Results 1 to 6 of 6
  1. #1
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Import Macro (2003)

    I have to import tables into a database <post#=688,197>post 688,197</post#> and have all the same field names, so I can append each time. These spreadsheets are downloaded from by me, from a reading device. The problem is that the data is not given to me on a regular basis, so I could end up with 10 spreadsheets to import into the database. All are in the same folder, and the filenames are all Checklist followed by the date (Checklist 180108). I think I cancreate a Macro to pull a table in, but can I get it to prompt for the file name. This would speed up the task a little.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Import Macro (2003)

    You can loop through all files in a folder. See for example <post:=659,670>post 659,670</post:> (that's for dBase files, but the principle remains the same).

  3. #3
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Import Macro (2003)

    I've copied the code, and followed the instructions. What do I put instead of "dBase 5.0". I tried Excel 7, and it says the object doesn't exist. If I leave it at dbase 5.0, which I know is wrong, it seemed to work, then came up with the message, 'The Microsoft Jet Database Engine could not find the object 'Checks 261107.xls' which is one of the files in the directory.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Import Macro (2003)

    Sorry, my fault. For importing Excel workbooks, you don't need DoCmd.TransferDatabase but DoCmd.TransferSpreadsheet. You can look up the syntax in the VBA help.

    I apologize for the confusion.

  5. #5
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Import Macro (2003)

    Thanks Hans, but still getting errors, down to the fact that the guys using the equipment will insist on putting data in wrong. The Macro will never work because of this. I have to go in and amend each spreadsheet manually, so I can only import one at a time the usual way. Thanks anyway.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Import Macro (2003)

    You could prompt for a single workbook like this. You'll have to adapt the code to your needs.

    Sub ImportXL()
    ' Path - modify as needed
    Const strFolder = "H:Excel"
    Dim strFile As String
    With Application.FileDialog(msoFileDialogOpen)
    .Filters.Clear
    .Filters.Add "Excel workbooks", "*.xls"
    .InitialFileName = strFolder
    If .Show Then
    strFile = .SelectedItems(1)
    ' code to import workbook goes here - modify as needed
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tblImport", strFile, True
    End If
    End With
    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
  •