Choosing Excel Spreadsheet for Import Using Code

[ScottXJ]

Hi,

I have a requirement to import Excel spreadsheets into a table on a daily basis and I wanted to know if anyone could provide information on how to do this. I looked at the TransferSpreadsheet option but this requires the path to be hard-coded and doesn't allow for different files to be selected. What I am looking to do is to have a window come up to ask the user to locate the file they would like to import. Any assistance would be appreciated.

Thanks,

Scott.

 

[fletch518]

Are these spreadsheets constant? If so, try building a form with a list box and place the paths and names of all your files in the box. Then code it like this:

Dim stFile As String
Dim stTable As String
stTable = "TableName"
stFile = [Forms]![Formname]![ListBoxName]

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, stTable, stFile

Dave

 

[jebry]

Hi Scott!

If you can make sure that the Excel files are all in one folder then the following code will put the files available in a list box:

Dim f, fc
Set fc = CreateObject("Scripting.FileSystemObject&quot.GetFolder(Application.CurrentProject.Path).Files
For Each f In fc
If Right(f.Name, 4) = ".xls" Then
lstFilename.RowSource = lstFilename.RowSource & """" & f.Name & """;"
End If
Next f

Note that this code assumes the Excel files are in the same folder as the database. If you do not want that then you need to replace Application.CurrentProject.Path with the path to the folder where the files reside. You can use the double click event to capture the name selected and then append the path to the name and use that in your transfer spreadsheet method.

hth
Jeff Bridgham
bridgham@purdue.edu

 

[ScottXJ]

Fletch518 and Jebry:

Thanks for your help! I will try these methods out and let you know how they work. I really appreciate your assistance.

Scott.