Code Does not work

[larabeelw]

I am importing all Excel Files in a folder - It works up to the point of updating the Field FileName with the file name of the Excel File.
Please Help. Here is my Code.

Private Sub Command2_Click()
Dim strPathFile As String, strFile As String, strPath As String
Dim strTable As String
Dim blnHasFieldNames As Boolean
Dim DB As DAO.Database

Dim STRSQL As String

' Change this next line to True if the first row in EXCEL worksheet
' has field names
blnHasFieldNames = False
' Replace C:\Documents\ with the real path to the folder that
' contains the EXCEL files
strPath = "C:\Users\larry.wolf\Desktop\davids\1\"
' Replace tablename with the real name of the table into which
' the data are to be imported
strTable = "tablename2"
strFile = Dir(strPath & "*.xls")
Do While Len(strFile) > 0
strPathFile = strPath & strFile
Dim temp As String
temp = strFile
On Error Resume Next
Set objExcel = GetObject(, "Excel.Application")
If Err.Number <> 0 Then
Set objExcel = CreateObject("Excel.Application")
blnEXCEL = True
End If
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
strTable, strPathFile, blnHasFieldNames




STRSQL = "UPDATE tablename2 SET tablename2.FileName = '" & temp & "' WHERE (((tablename2.FileName) Is Null));"
DoCmd.RunSQL (STRSQL)


strFile = Dir
' 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

[June7]

What is the error message?

Step debug. What is the value of temp?

[larabeelw]

I want the Value of Temp to be the Excel File Name.
There is not error, The Update Query does not update the field with anything. It does if I say Temp = "test"
But I want it to be the File Name.
I do not know what I am doing wrong, I am new to VB

[June7]

Step debug. Does the temp variable actually get properly populated?

Refer to link at bottom of my post for debugging guidelines.

[ssanfu]

Is the following two lines at the top of every module (standard/form/report)?

Option Compare Database
Option Explicit

Highly recommended........



Things I saw:
objExcel has not been declared (no Dim statement)
blnEXCEL has not been declared


There is a Dim statement inside of a loop.
Dim temp As String


Comment out or move the "On Error" statement. It is also inside of the loop. I would comment it out while debugging....


Two " strFile = Dir" lines??? Might be skipping an Excel file.......

[larabeelw]

Thanks guys. I do not know VB Too well, been wanting to learn. I tried to debug, but there is no errors. It does loop thought the folder and import the spreadsheets into the table. I just cannot get it to update the table with the file name of the file it just imported.

[June7]

Did you place a breakpoint in the code then step through the code one line at a time? Hover cursor over the temp variable after the 'temp=strFile' line executes. What does the popup tip show?

Or use: Debug.Print temp
to output the value of temp to the immediate window.

[larabeelw]

OK I have done the suggestions you have posted. There is still no change in operation.
The code runs with out errors, and appends to the Table tablename2 just fine. It does not update the field "Field name" with the file name of the Excel File.
Private Sub Command2_Click()
Dim strPathFile As String, strFile As String, strPath As String, temp As String
Dim strTable As String
Dim blnHasFieldNames As Boolean, blnEXCEL As Boolean
Dim DB As DAO.Database
Dim TDF As TableDef
Dim FLD As DAO.Field
Set DB = CurrentDb()
Dim STRSQL As String
Dim objExcel As Object

' Change this next line to True if the first row in EXCEL worksheet
' has field names
blnHasFieldNames = False
' Replace C:\Documents\ with the real path to the folder that
' contains the EXCEL files
strPath = "C:\Users\larry.wolf\Desktop\davids\1\"
' Replace tablename with the real name of the table into which
' the data are to be imported
strTable = "tablename2"
strFile = Dir(strPath & "*.xls")
Do While Len(strFile) > 0
strPathFile = strPath & strFile

temp = strFile
On Error Resume Next
Set objExcel = GetObject(, "Excel.Application")
If Err.Number <> 0 Then
Set objExcel = CreateObject("Excel.Application")
blnEXCEL = True
End If
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
strTable, strPathFile, blnHasFieldNames




STRSQL = "UPDATE tablename2 SET tablename2.FileName = '" & strPathFile & "' WHERE (((tablename2.FileName) Is Null));"
DoCmd.RunSQL (STRSQL)


strFile = Dir
' 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

[June7]

Still have not answered question about value of temp when the code runs.

[ssanfu]

And you will not get an error because you have not commented out/removed the "On Error" statement.


Having

      strFile = Dir    'delete/comment out this line
      ' Uncomment out the next code step if you want to delete the
      ' EXCEL file after it's been imported
      ' Kill strPathFile
      strFile = Dir()

is causing a file to be skipped (not imported). There should only be the last line "strFile = Dir()"


I can't figure out why you have the "temp" variable. It is not used for anything.


It looks like you have tried to modify the code Ken Snell has provided
(http://www.accessmvp.com/kdsnell/EXC...ImpFolderFiles)

[June7]

Okay, temp was used in the earlier version of code. Now need to know the value of strPathFile when the code runs.

You need to comment out the On Error GoTo and step debug.

[John_G]

Hi -

One possibility is that the field you are trying to update contains a zero-length string, and is not actually null, so your where clause in the SQL statement does not select any records.

Try this:

STRSQL = "UPDATE tablename2 SET tablename2.FileName = '" & strPathFile & "' WHERE len(nz(tablename2.FileName,'') = 0 "

The part in red is two single-quotes with no space between them.