automatically naming the new tables I'm creating automatically...

  • Thread starter Thread starter Boulder_girl
  • Start date Start date
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!
 
You need to assign the value to strTable inside the do while loop. When it
is outside the loop it never changes.

Kevin
 
Back
Top