Variable not defined

[Apprentice]

StrFile =

Having trouble with this code. It hangs on:

strFile = Dir(strPath & "*.xls")

Private Sub ImportCI_Click()

Dim blnHasFieldNames As Boolean
Dim strWorksheet As String
Dim strTable As String
Dim strPath As String
Dim strPathFile As String

' 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:\Documents and Settings\eberger\My Documents\ES2009"

' Replace worksheetname with the real name of the worksheet that is to be
' imported from each file
strWorksheet = "CoreItemTrends"

' Import the data from each workbook file in the folder
strFile = Dir(strPath & "*.xls")

Do While Len(strFile) > 0
strPathFile = strPath & strFile
strTable = "tbl_" & Left(strFile, InStrRev(strFile, ".xls") - 1)

DoCmd.TransferSpreadsheet acImport, _
acSpreadsheetTypeExcel9, strTable, strPathFile, _
blnHasFieldNames, strWorksheet & "$"

' 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

 

[Piet Linden]

Did you notice that all the other variables are declared?

Dim <variablename> As <type>?

strFile is not declared anywhere, so the interpreter doesn't know it
exists... Use

Dim strFile As String

to "tell" it that you're using that variable... then you should be off
to the races... just put the declaration in the block with all the
others and you should be good to go.

 

[Apprentice]

Thanks, I missed that. But now nothing happens. Not sure if I should be
using this code in a module or as a coded macro.

Any thoughts?

 

[Piet Linden]

Thanks, I missed that.  But now nothing happens.  Not sure if I should be
using this code in a module or as a coded macro.

It should be in a module. Create a button and paste the code behind
it (between the Sub ... End Sub)

 

[Stuart McCall]

StrFile =

Having trouble with this code. It hangs on:

strFile = Dir(strPath & "*.xls")

Private Sub ImportCI_Click()

Dim blnHasFieldNames As Boolean
Dim strWorksheet As String
Dim strTable As String
Dim strPath As String
Dim strPathFile As String

' 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:\Documents and Settings\eberger\My Documents\ES2009"

' Replace worksheetname with the real name of the worksheet that is to be
' imported from each file
strWorksheet = "CoreItemTrends"

' Import the data from each workbook file in the folder
strFile = Dir(strPath & "*.xls")

Do While Len(strFile) > 0
strPathFile = strPath & strFile
strTable = "tbl_" & Left(strFile, InStrRev(strFile, ".xls") - 1)

DoCmd.TransferSpreadsheet acImport, _
acSpreadsheetTypeExcel9, strTable, strPathFile, _
blnHasFieldNames, strWorksheet & "$"

' 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

In addition to Piet's advice, strPath needs a trailing backslash. From your
code:

strPath = "C:\Documents and Settings\eberger\My Documents\ES2009"

then, later on inside the loop you have:

strPathFile = strPath & strFile

That will append the contents of strFile onto the end of strPath, but
without a backslash separating them. Invalid path.

So make the line read:

strPath = "C:\Documents and Settings\eberger\My Documents\ES2009\"

 

[Apprentice]

No luck. For some reason nothing happens. I have verified the path and the
3 files in the Dir. I have verified all parts of the line to make sure that
everything is aligned and pointed to the right place.

No error is coming up, its just doesn't do anything.

 

[Apprentice]

Thanks Stuart and Piet, both fixs worked.