Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Jul 2002
    Location
    England
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Uploading excel tables - Access 97 (SR2)

    I have over 500 Excel tables to upload into an Access database. Each excel table is exactly the same in structure, but the numbers of records differ in each one. I have been using an APPEND QUERY to load the tables into Access but it's taking forever!
    Is there a way of uploading all of the tables without having to do them one by one?

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

    Re: Uploading excel tables - Access 97 (SR2)

    It might be possible to write a short VBA routine to do it for you. Importing will still take time, but it'll be automatic. But if you want help, you'll have to provide some more detail:

    Is each table in a different Excel file, or are they all in the same file?
    If in different files, are these files in the same directory?
    If in the same file, are the tables all on the same sheet, or not?
    If not, how are they distributes over the sheets?

  3. #3
    New Lounger
    Join Date
    Jul 2002
    Location
    England
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Uploading excel tables - Access 97 (SR2)

    Hi Hans,

    Each excel table is a separate file. Each file has a different name. The data in each file is different but each file has the exact same fields. Each file is on sheet1 of the workbook.
    All of the files are in the same directory. If you can possibly help, that would be great.
    In the meantime, I am slowly appending each file to a table in access.

    Thanks
    Tok

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

    Re: Uploading excel tables - Access 97 (SR2)

    Hi Tok,

    Take the code below as starting point. Of course, you'll have to substitute the appropriate names.

    Sub ImportExcel()
    Dim strFolder As String
    Dim strFile As String
    strFolder = "C:ExcelImport"
    strFile = Dir(strFolder & "*.xls")
    Do While strFile <> ""
    DoCmd.TransferSpreadsheet _
    TransferType:=acImport, _
    SpreadsheetType:=acSpreadsheetTypeExcel97, _
    TableName:="MyTable", _
    FileName:=strFolder & strFile, _
    HasFieldNames:=True
    strFile = Dir()
    Loop
    End Sub

    Notes:
    <UL><LI>You can copy the code into a standard module.
    <LI>The underscore characters _ are continuation characters. They indicate that the instruction is continued on the next line. There *must* be a space before the underscore!
    <LI>Replace "C:ExcelImport" by the name of the folder with the worksheets. There must be a trailing backslash at the end.
    <LI>Replace "MyTable" by the name of the table you want to append the data to.
    <LI>Replace HasFieldNames:=True by ...:=False if the spreadsheets have no field names in the first row.
    <LI>Run the code by placing the insertion point anywhere inside the procedure and clicking the Run button or hitting function key F5.
    <LI>The code assumes you want to import *all* spreadsheets in the folder.[/list]HTH,
    Hans

  5. #5
    New Lounger
    Join Date
    Jul 2002
    Location
    England
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Uploading excel tables - Access 97 (SR2)

    Hans,

    I am going to nominate you for President / King / God or whatever other title you require! You have cracked it!

    I really, really appreciate your help with this one. Many thanks indeed.

    That was a fantastic bit of problem solving you just did.

    Have a great weekend.

    Cheers

    Tok

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

    Re: Uploading excel tables - Access 97 (SR2)

    <img src=/S/blush.gif border=0 alt=blush width=15 height=15> <img src=/S/thankyou.gif border=0 alt=thankyou width=36 height=24>

    Have a great weekend too,
    Hans

  7. #7
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Uploading excel tables - Access 97 (SR2)

    WOW! I can't wait to get to the office and try this out. Yet again, it opens a whole world of opportunities for me.
    Thanks! I appreciate it,
    Leesha

Posting Permissions

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