Importing data from multiple Excel Worksheets

[MrYoda1]

I'm given an Excel file, which is an export out of our main student data system. The file has five tabs that I need to pull data from into five temporary Access tables.

Originally I had hard-coded the location and name of the Excel file. However, I cannot guarantee that the users will honour my file location and filename requirement. I found a function that lets them browse to the Excel file in order to import data from a Worksheet.

It all works very well, but since there are multiple Worksheets in the Excel file that need to be imported the current function and code I'm using causes the user to re-browse(?) each time they are ready to import the data from the next Worksheet. I'd rather not force them to go through the unnecessary step of browsing four more times for the same file.

I have a feeling that the answer is somewhere in the code, meaning it does retain the file and name location. I'm just unclear if that's true, and if so, how to use this information.

Here is the function:

Function selectFile()
    Dim fd As FileDialog
    Set fd = Application.FileDialog(msoFileDialogFilePicker)
     With fd
        If .Show Then
            selectFile = .SelectedItems(1)
        Else
            End
        End If
    End With
     Set fd = Nothing
End Function
 

Here is one of the lines of code I'm using to import data from one of the tabs:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "tblMathTempData", selectFile, True, "Math!"
 

There are four more temp tables and corresponding subject Worksheet tabs that are very much the same as what is shown in one above and the code for those tabs is similar.

Any help would be very much appreciated.

[jforbes]

FileDialog.InitialFileName can be set to before calling FileDialog.Show to have the Dialog open on a particular FileName Or Directory. Here is a link to the rest of the information Microsoft has on Office.FileDialog

[PhilOfWalton]

Two alternatives.
I hold all my external file details in a table. Before running your TransferSpreadsheet you can do a Dir to check the file exists, if not, load it into the table and use a DLookup to retrieve the table name. That way unless the file moves, you only have to set up the location once.
Method 2. I am assuming you have a form with a Command Button to run the TransferSpreadsheet routine. (If you are only ever going to use 1 external file this will work) Define SelectFile in the header of the Module. Change the name of Function SelectFile() to something else say Function SelectExcelFile()
That way by running that function, SelectFile is loaded and available to all Functions and Subs while the form is open.
So on opening the form, the first thing you do is run the SelectExcelFile() to load a file name into SelectFile.

Phil

[NeoPa]

You may find Select a File or Folder using the FileDialog Object helpful.