Results 1 to 9 of 9
  1. #1
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Importing multiple tables at one time (2002)

    Hi,

    I have a user who will need to import multple excel spreadsheets that come from a variety of locations. Rather than have import one at a time I'm wondering if it is possible to import multiple spread sheets at one time. I'm envishioning that she would click on a command button labeled "import". The command would prompt her to to point to the area where the files are stored. She would then highlight the files she wants and they would all import into the table. Is this possible?

    Thanks,
    Leesha

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Importing multiple tables at one time (2002)

    Hi Leesha
    I have a form that imports many spreadsheets every Saturday morning. It does the following:
    1. Uses the Dir command to see what sheets are in a predetermined folder (the address of this folder is in a table)
    2. For each sheet import it into a temporary table using TransferSpreadSheet, then append it into a permanent table. Delete the sheet after they have been imported. Alternatively you could move the sheets to a backup folder.

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

    Re: Importing multiple tables at one time (2002)

    Hi Pat!!! Good to hear from you.

    I have a feeling that this will be as bad as trying to build those payment and payment allocations forms who you helped me a year back.

    DIR command huh. OK I'll start reading on that one. Haven't used it yet. As for storing the address of the folder in a table, never thought of that one. I had thought about using a temporary table on import but wasn't sure how to code it so it kept importing till all tables were in the database.

    I'll need to start experimenting.

    Thanks,
    Leesha

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

    Re: Importing multiple tables at one time (2002)

    Patt will no doubt react, but in the meantime, you can take a look at <post#=164791>post 164791</post#> to get an idea of how to process Excel files in a loop using Dir.

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

    Re: Importing multiple tables at one time (2002)

    Hi Hans,

    I've started playing with the code that you pointed me to. I've never used modules nor do I have a clue as to how to get them to work on my form. My "books" are at home so I'll have to bone up on those later as the help section in access only defined Modules and their purpose not how to incorporate them into a form. I tried putting this code into the click function of an command button labeled but it did nothing nor did I get any errors to point me in a direction to look.

    Private Sub cmdImport_Click()
    Dim strFolder As String
    Dim strFile As String
    strFolder = "Dynamic AllianceImport Files"
    strFile = Dir(strFolder & "*.xls")
    Do While strFile <> ""
    DoCmd.TransferSpreadsheet _
    TransferType:=acImport, _
    SpreadsheetType:=acSpreadsheetTypeExcel97, _
    TableName:="tblTempImport", _
    FileName:=strFolder & strFile, _
    HasFieldNames:=True
    strFile = Dir()
    Loop
    End Sub


    Am I simply missing the boat or is it because it can't be done on click?

    Leesha

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

    Re: Importing multiple tables at one time (2002)

    Never mind, I found my error. The path had one letter off in the spelling. I'm so delighted with how this works words can't even describe. My office thinks I'm nuts as I just let a hoot of laughing joy! I simply don't understand why they don't get how exciting this is!

    Thanks so much,
    Leesha

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

    Re: Importing multiple tables at one time (2002)

    Hi,

    I have successfully using the following code to import multiple spreadsheets:

    'Imports all excel files residing in designated folder

    Dim strFolder As String
    Dim strFile As String
    strFolder = "Dynamic AllianceeImport Files"
    strFile = Dir(strFolder & "*.xls")
    Do While strFile <> ""
    DoCmd.TransferSpreadsheet _
    TransferType:=acImport, _
    SpreadsheetType:=acSpreadsheetTypeExcel97, _
    TableName:="tblTempImport", _
    FileName:=strFolder & strFile, _
    HasFieldNames:=True
    strFile = Dir()
    Loop


    This has worked fine since there was only one tab on the excel file. The user will be adding 2 more tabs for other purposes, and the data doesn't need to be imported. The tab or sheet that needs to be imported is labeled "Import". Is it possible to define this in the code and if so where would I put it?

    Thanks,
    Leesha

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

    Re: Importing multiple tables at one time (2002)

    If you leave the code as is, Access will import the first worksheet, and ignore any other worksheets.
    If the worksheet you want to import won't be the first one in the workbook any more, you can specify it as follows:
    <code>
    DoCmd.TransferSpreadsheet _
    TransferType:=acImport, _
    SpreadsheetType:=acSpreadsheetTypeExcel97, _
    TableName:="tblTempImport", _
    FileName:=strFolder & strFile, _
    HasFieldNames:=True, _
    Range:="Import!"
    </code>
    You can even specify a specific range:
    <code>
    ... _
    Range:="Import!B2:E100"
    </code>
    It is also possible to import a named range. If your workbook contains a range that has been named "MyRange", you can use
    <code>
    ... _
    Range:="MyRange"
    </code>
    This range does not need to be on the first sheet, and it doesn't have to start in A1.

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

    Re: Importing multiple tables at one time (2002)

    Hi Hans!

    That is perfect. Thanks so much for including all the options that I can use down the line.

    Thanks again,
    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
  •