Solved

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

Posted on 2004-08-23
10
710 Views
Last Modified: 2012-08-14
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




0
Comment
Question by:jc50967w
  • 4
  • 3
10 Comments
 
LVL 27

Expert Comment

by:jjafferr
Comment Utility
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
Loop

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


jaffer
0
 
LVL 16

Expert Comment

by:Nestorio
Comment Utility
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.
0
 

Author Comment

by:jc50967w
Comment Utility
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.

Vivian
0
 

Author Comment

by:jc50967w
Comment Utility
Nestorio,

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!

Vivian
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 27

Accepted Solution

by:
jjafferr earned 500 total points
Comment Utility
>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.

jaffer
0
 

Author Comment

by:jc50967w
Comment Utility
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,

Vivian
0
 

Author Comment

by:jc50967w
Comment Utility
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?

Vivian
0
 
LVL 27

Expert Comment

by:jjafferr
Comment Utility
Hey Vivian

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

jaffer
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

771 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

11 Experts available now in Live!

Get 1:1 Help Now