Help!!! Import Multiple Spreadsheet in an Excel file into Access through VB6.0

Hi,

I am currently working on a VB + Access program which imports each of spreadsheet of each excel file into an existing table(TempTable). I design a form with a command button, I want to get excel sheets and put them into access table by click the commend button.

I have received the run-time Error that " The Microsoft jet engine can't find the input table or query 'strSheetName', make sure it exists or its name is spelled correctly." The Error points to the line : CurrentDb.Execute "SELECT*INTO TempTable FROM strSheetName"
                       
Program Discription:
1. each excel file includs 31 spreadsheet, I only need first 30.. I have many excel files in the same folder.
2. Each spreadsheet is multiple headings, data/values which I need starts 7th row.
3. each sheet has 20 columns with data. I have created 20 fields in TempTable.

I have used TransferSpreadsheet method importing fixed range, but received errors for entire sheet (Type convension failed). The sheet is read-only, not way to reformat.

Part of syntax ( let's me know if you need entire code):

'***********************************************************************************
'**
'**   IMPORTING MULTIPLE SPREADSHEETS AT A TIME                                   **
'**   ADDING SPREADSHEET NAME IN THE FIRST FIELD AFTER IMPORTING EACH SPREADSHEET **
'***********************************************************************************

Private Sub Import_OneFile(ByVal strFilename As String)
   
    Dim stDocName As String
    Dim xlWorkbook As Excel.Workbook
    Dim xlSheet As Excel.Sheets
    Dim IntCounter As Integer
   
    Dim eachSheetName As String
    Dim myRange As String
   
    Dim i As Integer
    Dim strSheetName As String
    Dim xdate As Date
   
    Set xlWorkbook = Excel.Workbooks.Open("c:\DS\" & strFilename)
    IntCounter = xlSheet.Count
       
        'for some files which contain 32 spreadsheet
        If (IntCounter > 31) Then
        IntCounter = 31
        End If
             i = 1 'initialize the counter and start the loop for each spreadsheet.
             Do Until i = IntCounter + 1
             
                  'get sheet name and define range
                  strSheetName = xlWorkbook.Sheets(i).Name
                  xdate = strSheetName
                  eachSheetName = strSheetName + "$"
                  'myRange = eachSheetName + "A7:T150"
                 
                  '** method for importing spreadsheet with fixed range,
                  '** ignore TransferSpreadsheet method for now
                  'DoCmd.TransferSpreadsheet acImport, 8, "TempTable", "C:\DS\" & strFilename, False, myRange
                 
                  '** import entire sheet from spreadsheet by sql statement
                  '** the method I am working on
                    Dim cnn As New ADODB.Connection
                    Dim rst As New ADODB.Recordset
                    Dim rstCounter As Integer
                   
                    Set cnn = CurrentProject.Connection
                        CurrentDb.Execute "SELECT*INTO TempTable FROM strSheetName"
                       
                       
                        'delete first fix rows which are multiple heading in spreasheet
                        'CurrentDb.Execute "DELETE FROM TempTable WHERE rst.Index IN(1,2,3,4,5,6)"
                       
                        rstCounter = rst.RecordCount
                        With rst
                            .CursorLocation = adUseClient
                            .Open "[TempTable]", cnn, adOpenStatic, adLockBatchOptimistic, adCmdTable
                            rst.MoveFirst
                            While rstCounter < 7
                                rst.Delete
                                rst.MoveNext
                            Wend
                        End With
                        rst.Close
                        Set rst = Nothing
                       
                 
                  '***** try to insert the spreadsheet name in the first field ********
                                           
               
                        CurrentDb.Execute "UPDATE TempTable Set F1 = '" & Str(xdate) & "' WHERE F1 Is Null"
                    cnn.Close
                    Set cnn = Nothing
                     
            'control back to the spreadsheet loop after add the due date in the first field
            i = i + 1
            Loop
    'xlWorkbook.Save
    xlWorkbook.Close
    Set xlWorkbook = Nothing
    'DoCmd.RunMacro "M_Import1File"
End Sub