Run-Time Error '91' and SQL Update

[moscgama]

Hi,

Happy New Year!! I am trying to loop through newly imported tables from excel and add the excel file name to a column in the new table. However, I'm getting an "Run-time Error '91': Object variable or With block variable not set" at the line "strrename= strsql1 & tdf.Name & strsql2 & strsql3 & ";"" I believe it has something to do with tdf.Name.

Here's my code so far:

 Option Compare Database
  
 Public Function Impt_Sero()
  
 Dim blnHasFieldNames As Boolean, blnEXCEL As Boolean, blnReadOnly As Boolean
  Dim intWorkbookCounter As Integer
  Dim lngCount As Long
  Dim objExcel As Object, objWorkbook As Object
  Dim colWorksheets As Collection
  Dim strPath As String, StrFile As String
  Dim strpassword As String
  Dim tdf As TableDef
  Dim strsql1 As String
  Dim strsql2 As String
  Dim strsql3 As String
  
 ' Establish an EXCEL application object
  On Error Resume Next
  Set objExcel = GetObject(, "Excel.Application")
  If Err.Number <> 0 Then
        Set objExcel = CreateObject("Excel.Application")
        blnEXCEL = True
  End If
  Err.Clear
  On Error GoTo 0
  
 ' Change this next line to True if the first row in EXCEL worksheet
  ' has field names
  blnHasFieldNames = False
  
 ' Replace C:\MyFolder\ with the actual path to the folder that holds the EXCEL files
  strPath = "C:\\users\user\Desktop\To_Import\"
  
 ' Replace passwordtext with the real password;
  ' if there is no password, replace it with vbNullString constant
  ' (e.g., strPassword = vbNullString)
  
  strpassword = vbNullString
  
  blnReadOnly = True ' open EXCEL file in read-only mode
  
  StrFile = Dir(strPath & "*.xls")
  
  intWorkbookCounter = 0
  
  
  Do While StrFile <> ""
  
        intWorkbookCounter = intWorkbookCounter + 1
  
        Set colWorksheets = New Collection
  
        Set objWorkbook = objExcel.Workbooks.Open(strPath & StrFile, , _
              blnReadOnly, , strpassword)
           
       For lngCount = 1 To objWorkbook.Worksheets.Count
              colWorksheets.Add objWorkbook.Worksheets(lngCount).Name
        Next lngCount
        
        ' Close the EXCEL file without saving the file, and clean up the EXCEL objects
        objWorkbook.Close False
        Set objWorkbook = Nothing
  
        ' Import the data from each worksheet into a separate table
        For lngCount = colWorksheets.Count To 1 Step -1
              DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tbl" & colWorksheets(lngCount) & intWorkbookCounter, _
                    strPath & StrFile, blnHasFieldNames, _
                    colWorksheets(lngCount) & "$" & "A1:O142"
         
         strsql1 = "UPDATE"
         strsql2 = "SET"
         strsql3 = ".[F14]=' " & StrFile & "'"
         strrename = strsql1 & tdf.Name & strsql2 & strsql3 & ";"
  
         CurrentDb.Execute strrename
  
        Next lngCount
  
        ' Delete the collection
        Set colWorksheets = Nothing
  
  'Uncomment out the next doe step if you want to delete the
  'Excel file after its been imported
  'Kill strPath & strFile
  
  StrFile = Dir()
  
  Loop
  
  If blnEXCEL = True Then objExcel.Quit
  Set objExcel = Nothing
  
 End Function

I believe I declared the tdf object, but maybe not? Any suggestions would be appreciated!

Thanks so much in advance!

 

[pbaldy]

You declared it, but never set it to anything.

 

[pbaldy]

Why not just use

"tbl" & colWorksheets(lngCount) & intWorkbookCounter

And I suspect you'll need this:

http://www.baldyweb.com/ImmediateWindow.htm