auto-naming the excel files I'm importing (use Dir()???)

[Boulder_girl]

Hi, I posted a very similar message to what I'm about to post now, but I've
refined my question since then!

So, I've got this (seemingly common) script that automatically imports excel
files within a given folder (See below)... the problem lies in the name
assigned to each newly-imported file. As it is now, I've got the name to be
given to the new file (strTable) = Dir(strPath). I was hoping that each new
file would therefore have the same name as the excel file it was imported
from.

But no, what happens is that each imported file has the same name as the
first excel file in the folder containing all the excel files! The only
difference is that with each iteration of the loop, the name gets a number
tagged on to it (e.g. File, File_1, File_2. etc.)

Can someone help me? Thanks in advance!!!


Code:


Sub sImportExcel()


Dim strPathFile As String, strFile As String, strPath As String
Dim strTable As String
Dim blnHasFieldNames As Boolean

' Change this next line to True if the first row in EXCEL worksheet
' has field names
blnHasFieldNames = True

' Replace C:\Documents\ with the real path to the folder that
' contains the EXCEL files
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 = Dir(strPath)

strFile = Dir(strPath & "*.xls")
Do While Len(strFile) > 0
strPathFile = strPath & strFile
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
strTable, strPathFile, blnHasFieldNames

' Uncomment out the next code step if you want to delete the
' EXCEL file after it's been imported
' Kill strPathFile

strFile = Dir()
Loop

End Sub

 

[Stuart McCall]

Hi, I posted a very similar message to what I'm about to post now, but
I've
refined my question since then!

So, I've got this (seemingly common) script that automatically imports
excel
files within a given folder (See below)... the problem lies in the name
assigned to each newly-imported file. As it is now, I've got the name to
be
given to the new file (strTable) = Dir(strPath). I was hoping that each
new
file would therefore have the same name as the excel file it was imported
from.

But no, what happens is that each imported file has the same name as the
first excel file in the folder containing all the excel files! The only
difference is that with each iteration of the loop, the name gets a number
tagged on to it (e.g. File, File_1, File_2. etc.)

Can someone help me? Thanks in advance!!!


Code:


Sub sImportExcel()


Dim strPathFile As String, strFile As String, strPath As String
Dim strTable As String
Dim blnHasFieldNames As Boolean

' Change this next line to True if the first row in EXCEL worksheet
' has field names
blnHasFieldNames = True

' Replace C:\Documents\ with the real path to the folder that
' contains the EXCEL files
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 = Dir(strPath)

strFile = Dir(strPath & "*.xls")
Do While Len(strFile) > 0
strPathFile = strPath & strFile
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
strTable, strPathFile, blnHasFieldNames

' Uncomment out the next code step if you want to delete the
' EXCEL file after it's been imported
' Kill strPathFile

strFile = Dir()
Loop

End Sub

The problem is that, inside the loop you're setting strFile but not
strTable.

After the line:

strFile = Dir()

add the following:

strTable = strFile