trying to import worksheets

[token_remedie]

Hi all,

Basically I'm trying to import each worksheet, but each one needs something different done to it on import so I'm using a for loop:

Private Sub Command32_Click()

'import master data click
     Dim MyXLApp As Excel.Application
    Dim MyXLWorkBook As Excel.Workbook
 Dim MyRange As String

       
    'import the data
Call import

     Dim WrksheetName As String
     Dim i As Integer
     Dim xl As Object
     
     Set xl = CreateObject("Excel.Application")
     xl.Visible = True
     xl.Workbooks.Open strPathAndFile
     With xl
     .Visible = False
     With .Workbooks(.Workbooks.Count)
     For i = 1 To .Worksheets.Count
     
     WrksheetName = .Worksheets(i).Name
     If WrksheetName = "Master" Then
 
       MyRange = WrksheetName & "!A:ZZ"
      
      'Create the import table

    Dim tdf As DAO.TableDef
    Dim fld As DAO.Field
    Dim fld2 As DAO.Field
    Dim fld3 As DAO.Field
    Dim fld4 As DAO.Field
    Dim fld5 As DAO.Field
    Dim fld6 As DAO.Field
    Dim fld7 As DAO.Field
    Dim fld8 As DAO.Field
    Dim fld9 As DAO.Field
    Dim fld10 As DAO.Field
    Dim fld11 As DAO.Field
    Dim fld12 As DAO.Field
    Dim fld13 As DAO.Field
    Dim fld14 As DAO.Field
    Dim fld15 As DAO.Field
    Dim fld16 As DAO.Field
    Dim fld17 As DAO.Field
    Dim fld18 As DAO.Field
    Dim fld19 As DAO.Field
    Dim fld20 As DAO.Field
    Dim fld21 As DAO.Field
    Dim fld22 As DAO.Field
    Dim fld23 As DAO.Field
    Dim fld24 As DAO.Field
    Dim fld25 As DAO.Field
    Dim fld26 As DAO.Field
    Dim fld27 As DAO.Field
    Dim fld28 As DAO.Field
    Dim fld29 As DAO.Field
    Dim fld30 As DAO.Field
    Dim fld31 As DAO.Field
    Dim fld32 As DAO.Field
    Dim fld33 As DAO.Field
    Dim fld34 As DAO.Field
    Dim fld35 As DAO.Field
    Dim fld36 As DAO.Field
 
    
    Set tdf = CurrentDb.CreateTableDef("importtable")
    Set fld = tdf.CreateField("Asset Number", dbText)
    Set fld2 = tdf.CreateField("CoCd", dbDouble)
    Set fld3 = tdf.CreateField("Class", dbText)
    Set fld4 = tdf.CreateField("Asset Description", dbText)
    Set fld5 = tdf.CreateField("Serial No#", dbText)
    Set fld6 = tdf.CreateField("Invent No", dbText)
    Set fld7 = tdf.CreateField("CostCentre", dbDouble)
    Set fld8 = tdf.CreateField("Plnt", dbDouble)
    Set fld9 = tdf.CreateField("LOCATION", dbText)
    Set fld10 = tdf.CreateField("F10", dbText)
    Set fld11 = tdf.CreateField("F11", dbDouble)
    Set fld12 = tdf.CreateField("FundTyp", dbText)
    Set fld13 = tdf.CreateField("ProgSrc", dbText)
    Set fld14 = tdf.CreateField("SubClass", dbDouble)
    Set fld15 = tdf.CreateField("Vendor", dbDouble)
    Set fld16 = tdf.CreateField("Manufacturer", dbText)
    Set fld17 = tdf.CreateField("COST", dbCurrency)
    Set fld18 = tdf.CreateField("W Start", dbDate)
    Set fld19 = tdf.CreateField("Lenovo W Start", dbDate)
    Set fld20 = tdf.CreateField("Remarks", dbText)
    Set fld21 = tdf.CreateField("Remarks 2", dbText)
    Set fld22 = tdf.CreateField("Formula", dbDouble)
    Set fld23 = tdf.CreateField("F23", dbText)
    Set fld24 = tdf.CreateField("F24", dbText)
    Set fld25 = tdf.CreateField("F25", dbText)
    Set fld26 = tdf.CreateField("F26", dbText)
    Set fld27 = tdf.CreateField("F27", dbText)
    Set fld28 = tdf.CreateField("F28", dbText)
    Set fld29 = tdf.CreateField("Remarks1", dbText)
    Set fld30 = tdf.CreateField("F22", dbText)
    Set fld31 = tdf.CreateField("sub class", dbText)
    Set fld32 = tdf.CreateField("HP W Start", dbDate)
    Set fld33 = tdf.CreateField("F21", dbText)
    Set fld34 = tdf.CreateField("F20", dbText)
    Set fld35 = tdf.CreateField("F19", dbText)
    Set fld36 = tdf.CreateField("F1", dbText)
   
    
    
    tdf.Fields.Append fld
    tdf.Fields.Append fld2
    tdf.Fields.Append fld3
    tdf.Fields.Append fld4
    tdf.Fields.Append fld5
    tdf.Fields.Append fld6
    tdf.Fields.Append fld7
    tdf.Fields.Append fld8
    tdf.Fields.Append fld9
    tdf.Fields.Append fld10
    tdf.Fields.Append fld11
    tdf.Fields.Append fld12
    tdf.Fields.Append fld13
    tdf.Fields.Append fld14
    tdf.Fields.Append fld15
    tdf.Fields.Append fld16
    tdf.Fields.Append fld17
    tdf.Fields.Append fld18
    tdf.Fields.Append fld19
    tdf.Fields.Append fld20
    tdf.Fields.Append fld21
    tdf.Fields.Append fld22
    tdf.Fields.Append fld23
    tdf.Fields.Append fld24
    tdf.Fields.Append fld25
    tdf.Fields.Append fld26
    tdf.Fields.Append fld27
    tdf.Fields.Append fld28
    tdf.Fields.Append fld29
    tdf.Fields.Append fld30
    tdf.Fields.Append fld31
    tdf.Fields.Append fld32
    tdf.Fields.Append fld33
    tdf.Fields.Append fld34
    tdf.Fields.Append fld35
    tdf.Fields.Append fld36
    
    CurrentDb.TableDefs.Append tdf
    
    'turn warnings off ready for import and transfer into newly created table
    MsgBox (strPathAndFile & " " & MyRange)
    Debug.Print
DoCmd.TransferSpreadsheet acImport, , "importtable", strPathAndFile, True, MyRange


DoCmd.SetWarnings (warningsoff)
   ' delete blank rows
    strSQL = "Delete FROM importtable Where Len(Trim([asset number] & ' '))=0 "
    'runSQL for strsql (deleting the blank rows)
    DoCmd.RunSQL (strSQL)
    'append from import table to master table
DoCmd.RunSQL "INSERT INTO Master ([Asset Number], CoCd, Class, [Serial No#], [Invent No], CostCentre, Plnt, LOCATION, COST, [W Start], [HP W Start], [Lenovo W Start], Remarks ) SELECT importtable.[Asset Number], importtable.CoCd, importtable.Class, importtable.[Serial No#], importtable.[Invent No], importtable.CostCentre, importtable.Plnt, importtable.LOCATION, importtable.COST, importtable.[W Start], importtable.[HP W Start], importtable.[Lenovo W Start], importtable.Remarks FROM importtable;"

'update based on serial number remarks1 and remarks 2 all to remarks
DoCmd.RunSQL "UPDATE importtable INNER JOIN Master ON importtable.[Asset Number] = Master.[Asset Number] SET importtable.[Remarks 2] = [master].[remarks];"
DoCmd.RunSQL "UPDATE importtable INNER JOIN Master ON importtable.[Asset Number] = Master.[Asset Number] SET importtable.Remarks1 = [master].[remarks];"

'delete any error tables
   Dim tblDef As TableDef
     
    On Error Resume Next
   For Each tblDef In CurrentDb.TableDefs
If InStr(1, tblDef.Name, "$A:ZZ_ImportErrors") > 0 Then
   DoCmd.SelectObject acTable, tblDef.Name, True
 DoCmd.DeleteObject acTable, tblDef.Name
   Else
      
   End If
   Next tblDef
   
   On Error GoTo Errhandler
   
' clear import table ready for next import
strSQL = "DROP TABLE importtable"
DoCmd.RunSQL (strSQL)

'clear the edit list combobox
Combo4.ListItemsEditForm = ""
DoCmd.SetWarnings (warningson)

Errhandler:
Debug.Print Err.Number & Err.Description & Err.HelpContext
'MsgBox Err.Number & Err.Description & Err.HelpContext

'if import table exists drop it

For Each tblDef In CurrentDb.TableDefs
If InStr(1, tblDef.Name, "importtable") > 0 Then
   DoCmd.SelectObject acTable, tblDef.Name, True
 DoCmd.DeleteObject acTable, tblDef.Name
   Else
      
   End If
   Next tblDef
 

Else: End If

    Next i
    End With
    End With
    Set xl = Nothing
    xl.Quit
    MsgBox ("nexti")
    


 End Sub

the error I'm currently getting is

Run-time error 3011
the Microsoft Access database engine could not find the object 'Master$A:ZZ'. Makre sure the object exists etc.

Then when I click on help it says:

Cannot open the file: ,lc@MSITStore:C:\Program Filer(x86)\Common Files\Microsoft Shared\OFDFICE12\1033jeterr40.chm

even though the last message box before that error correctly shows the correct file path and range.

Any ideas?