Importinf multiple spreadsheets

[David]

I have a variable number of excel files in a particular folder- while each
file has a different name- they all contain a sheet of a similar name.
Is there a function I can run to import these tables into one access table

 

[ryguy7272]

You want to import everything into the same table?

Option Compare Database

Sub Import()
Dim strFile As String
Dim strPath As String
Dim blnHasFieldNames As Boolean
Dim strTable As String

strPath = "C:\Documents and Settings\ThinkPad\Desktop\Test\"
blnHasFieldNames = True ' Or set to False
strTablename = "Table1"
strFile = Dir(strPath & "*.xls")
Do While strFile <> ""
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
strTablename, strPath & strFile, blnHasFieldNames
strFile = Dir()
Loop

End Sub

Regards,
Ryan---

 

[John W. Vinson]

I have a variable number of excel files in a particular folder- while each
file has a different name- they all contain a sheet of a similar name.
Is there a function I can run to import these tables into one access table

You can use the Dir() function to crawl through the list of filenames, and the
TransferSpreadsheet() method to import. See the VBA help for these and/or post
back with more details for a more specific answer.

 

[David]

Ryan,
Thanks for that- wasn't sure how to run a sub- so changed it to a function-
the code below appears to work but only picks up the first sheet in the file-
any idea why the selected sheet isn't getting imported- also if sheet is
hidden can it still import it?

Function Import()
Dim strFile As String
Dim strPath As String
Dim blnHasFieldNames As Boolean
Dim strTable As String

strPath = "C:\temp\New folder II\" ' location of files
blnHasFieldNames = True ' Or set to False
strTablename = "Auto Upload" 'name of table i want to import
strFile = Dir(strPath & "*.xls")
Do While strFile <> ""
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, strTablename,
strPath & strFile, blnHasFieldNames
strFile = Dir()
Loop

End Function