Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2002
    Location
    Houston, Texas
    Posts
    74

    Question Unanswered: import excel files...

    HI,

    I have few hundreds of excel files (with and without headers) but the same type of data (column a to m) that I need to import into a table (tblMaster). I was trying to use this method to import excel files. but nothng happens when i use following code on a button and click on the button. Please advise.

    Thanks


    Private Sub cmdImport_Click()

    Dim MyFile, MyPath, MyName, fs
    MyFile = Dir("L:\Conversions\Master Trans\1\*.xls")
    Set fs = CreateObject("Scripting.FileSystemObject")
    Do While MyName <> ""
    DoCmd.DeleteObject acTable, "tblMaster"
    'DoCmd.Transfertext acImportdelim, , "NewData", myfile
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel97, "tblMaster"
    MsgBox "import complete"
    'DoCmd.OpenQuery "Append NewData to your final output"
    fs.CopyFile MyFile, "L:\Conversions\Master Trans\1\Done\"
    MsgBox "file have been copied to done directory"
    'fs.deletfile myfile
    MyName = Dir
    Loop

    End Sub

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    we ignore this one, right?
    your other post is much closer to the truth.

    izy
    currently using SS 2008R2

  3. #3
    Join Date
    Dec 2002
    Location
    Houston, Texas
    Posts
    74
    I got it all figured out. The other post uses getfilelist function but i am not using that at all with this new code. here is the working version of the new code.

    Private Sub cmdImport_Click()
    Dim strFile As Variant
    Dim strPath As String
    Dim strFilter As String
    Dim strSaveFileName As String

    strPath = "H:\SourceFolder\"
    strFilter = ahtAddFilterItem(strFilter, "Text File (*.txt)", "*.txt")
    strSaveFileName = ahtCommonFileOpenSave( _
    OpenFile:=False, _
    Filter:=strFilter, _
    Flags:=ahtOFN_OVERWRITEPROMPT Or ahtOFN_READONLY)

    CurrentProject.Connection.Execute "DELETE * FROM master"
    strFile = Dir(strPath & "FileName*.xls")
    'MsgBox "file name is " & strFile
    Do While Len(strFile) > 0
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "master", strPath & strFile, False
    strFile = Dir
    Loop
    'MsgBox "Import Completed. System will now export the data into a tab delimited text file."
    DoCmd.Hourglass 1
    'DoCmd.TransferText acExportDelim, "TranslationSpecification", "master", strSaveFileName, 0
    DoCmd.TransferText acExportDelim, "ExportSpecification", "qryExport", strSaveFileName, 0

    MsgBox "The data has been sucessfully exported", vbDefaultButton1, "Data Export"
    DoCmd.Hourglass 0
    End Sub

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •