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

Posted on 2004-08-23
Last Modified: 2012-08-14

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                                   **

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
                            While rstCounter < 7
                        End With
                        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"
                    Set cnn = Nothing
            'control back to the spreadsheet loop after add the due date in the first field
            i = i + 1
    Set xlWorkbook = Nothing
    'DoCmd.RunMacro "M_Import1File"
End Sub

Question by:jc50967w
  • 4
  • 3
LVL 27

Expert Comment

ID: 11870753
Try this code, I got it somewhere from EE, it was posted by WonHop

Copy and Paste this into a Module
The first two times it loops thru, it will not find anything.  It will find something on the 3rd try.

Function ShowFileInfo()

Dim fs, f, s
Dim ExcelFileName As String
Dim PathToExcelFiles As String

Set fs = CreateObject("Scripting.FileSystemObject")

PathToExcelFiles = "D:\data\Excel_Files\*.*"

ExcelFileName = Dir(PathToExcelFiles, vbDirectory)    ' Get first
Do While ExcelFileName <> "" ' Start loop.

   If ExcelFileName <> "." And ExcelFileName <> ".." Then
      Set f = fs.GetFile("D:\data\Excel_Files\" & ExcelFileName)
      s = f.DateCreated
      'MsgBox "File: " & ExcelFileName & " DateCreated: " & s
      DoCmd.TransferSpreadsheet acImport, 8, "tbl_Import_Table", "D:\data\Excel_Files\" & ExcelFileName, True
   End If
ExcelFileName = Dir  ' Next file

End Function

The "True" part of the DoCmd.TransferSpreadsheet means the Spreadsheet has Header in the First Row.
"tbl_Import_Table", is your Table Name
"D:\data\Excel_Files\" is the path to your Excel Folder
ExcelFileName is the name of the Excel File in the Folder

LVL 16

Expert Comment

ID: 11870757
The program expects an Access table (or a linked one) named as the content of strSheetName field.

You need to link your Excel sheets to an Access table.

Author Comment

ID: 11870947
Dear Jaffer,

I used the same method as in your threads, I received Type Conversion Failed Error Tables, and lost some records in two fields.


Author Comment

ID: 11870968

I have tried Transferspreadsheet aclink command, but don't work well, besides losing records, each spreadsheet became a link table, I have 30 spreadsheets in each of total 650 excels, then how many link tables I am going to get. I need to ran query after import!

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

LVL 27

Accepted Solution

jjafferr earned 500 total points
ID: 11871328
>Type Conversion Failed Error
means that you tried to Import a field to the wrong Data Type,
for example,
Import Text to Number Field.

check the fields in your Table and make sure they comply with the Field type in the Excel file.

The best way to avoid this error, is the make ALL the fields in the Table as text (try it as a test), but it is not remended.

If you want to do that, then do the Import to a Temp Table, after that,
Make an Append Query, to Append these Records to the Correct Table with the Correct Data Type.


Author Comment

ID: 11871521
Dear Jaffer,

I have tried all the ways you mentioned; I have confirmed corresponding data type between spreadsheets and table. Anyway, I go back to do it again in terms of Type Conversion Failed error, and keep you post.

Thank you very much,


Author Comment

ID: 11872173
Hi, Jaffer,

I realized that converting all data typies into text type is not key point to solve the problem. I can't  define the spreadsheet range, since each of spreadsheet has different rows. I can't do Transferspreadsheet  to import entire spreadsheet into an existing table, it will genereate run-time error  2319 "Field21 can't find".

Any idea for how to create a new table and how to read each spreadsheet?

LVL 27

Expert Comment

ID: 12195267
Hey Vivian

How did it go with this question, did you solve the problem?


Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

914 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now