B
Boulder_girl
Hi, I've found a script on-line (it appears in several places) that automates
importing all excel files in a given folder into access. The problem is, I
want each table to be imported AS A SEPARATE TABLE in access, and have each
table be named after the excel file it was imported from.
Here's the script:
Sub sImportExcel()
Dim strPathFile As String, strFile As String, strPath As String
Dim strTable As String
Dim blnHasFieldNames As Boolean
blnHasFieldNames = True
strPath = "C:\WS\Scratch\MAPSS_temp\CUR\"
' Replace tablename with the real name of the table into which
' the data are to be imported
strTable = strFile
strFile = Dir(strPath & "*.xls")
Do While Len(strFile) > 0
strPathFile = strPath & strFile
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
strTable, strPathFile, blnHasFieldNames
strFile = Dir()
Loop
End Sub
As you can see, I've attempted to cause the program to name the new tables
after their excel counterparts by using the strTable = strFile
statement... but that causes a Run-time error (code = 2495, "The action or
method requires a Table Name argument)... so I'm at a loss... I did try
just doing strTable = "test" and it seems like every excel table in my folder
was copied into just one acess table called "test". I have to do this on
many, many files, so can someone help me??? Thanks!
importing all excel files in a given folder into access. The problem is, I
want each table to be imported AS A SEPARATE TABLE in access, and have each
table be named after the excel file it was imported from.
Here's the script:
Sub sImportExcel()
Dim strPathFile As String, strFile As String, strPath As String
Dim strTable As String
Dim blnHasFieldNames As Boolean
blnHasFieldNames = True
strPath = "C:\WS\Scratch\MAPSS_temp\CUR\"
' Replace tablename with the real name of the table into which
' the data are to be imported
strTable = strFile
strFile = Dir(strPath & "*.xls")
Do While Len(strFile) > 0
strPathFile = strPath & strFile
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
strTable, strPathFile, blnHasFieldNames
strFile = Dir()
Loop
End Sub
As you can see, I've attempted to cause the program to name the new tables
after their excel counterparts by using the strTable = strFile
statement... but that causes a Run-time error (code = 2495, "The action or
method requires a Table Name argument)... so I'm at a loss... I did try
just doing strTable = "test" and it seems like every excel table in my folder
was copied into just one acess table called "test". I have to do this on
many, many files, so can someone help me??? Thanks!