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



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()

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.



Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question