Variable not defined


A

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
 
Ad

Advertisements

P

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.
 
A

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?
 
P

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

Stuart McCall

Apprentice said:
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\"
 
A

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.
 
Ad

Advertisements


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

Top