Solved

Appending a Excel sheet to an Access Database Via Visual Basic

Posted on 2002-07-13
3
168 Views
Last Modified: 2013-11-25
Hi all,

I am having a problem while trying to import an Excel sheet into an Access database Via Visual Basic.  I have tried this two ways, first by connecting a data control to the excel worksheet and stepping through each record in the dataset and utilizing ado to connect to the access database.  

Second I used the following code that I found on the net.  

Private Sub Command1_Click()
    On Error GoTo errHandler
    Dim mExcelFile As String
    Dim mAccessFile As String
    Dim mWorkSheet As String
    Dim mTableName As String
    Dim mDataBase As Database
    mExcelFile = App.Path & "\Book1.xls"
    mAccessFile = App.Path & "\Db1.mdb"
    mWorkSheet = "Sheet1"
    mTableName = "Table1"
      ' Below you may use "Excel 7.0" or 8.0 depending on your installable ISAM.
    Set mDataBase = OpenDatabase(mExcelFile, True, False, "Excel 5.0")
    mDataBase.Execute "Select * into [;database=" & mAccessFile & "]." & mTableName & _
        " FROM [" & mWorkSheet & "$]"
    MsgBox "Done.  Use Access to view " & mTableName
    Exit Sub
errHandler:
    If Err.Number = 3010 Then
         MsgBox mTableName & " already exist." & vbCrLf & _
             "Delete " & mTableName & " first or use another table name."
    Else
         MsgBox Err.Number & "  " & Err.Description
    End If
End Sub

Both of these kind of half work.  The problem is that one of the fields in my excel table is alpha-numeric and it seems to be excluding the cells in the column that are truly numeric.

EX:  It will pick up the cell 11B but not the cell with just 11 in it.  Weird huh?

Has anyone got a solution to this?
0
Comment
Question by:mitai
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 7151685
The answer is that Excel is not a true DBMS.  Take a look at this article from MSDN (yes, I realize you are using DAO, but the same applies):

HOWTO: Use ADO with Excel Data from Visual Basic or VBA (Q257819)
http://support.microsoft.com/default.aspx?scid=kb;EN-US;q257819

And in particular this paragraph:
<quote>
Rows to Scan: Excel does not provide ADO with detailed schema information about the data it contains, as a relational database would. Therefore, the driver must scan through at least a few rows of the existing data in order to make an educated guess at the data type of each column. The default for "Rows to Scan" is eight (8) rows. You can specify an integer value from one (1) to sixteen (16) rows, or you can specify zero (0) to scan all existing rows. This is done by adding the optional MaxScanRows= setting to the connection string, or by changing the Rows to Scan setting in the DSN configuration dialog box.
</quote>

Also, please maintin this open question:
Drill down question for Crystal Date: 05/01/2002 07:38AM PST
http://www.experts-exchange.com/crystal/Q_20295856.html

Thanks,
Anthony
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 100 total points
ID: 7151686
Here are a couple of articles from MSDN, using DAO:
HOWTO: Extract Information from Excel Sheet with DAO (Q190195)
http://support.microsoft.com/default.aspx?scid=kb;EN-US;q190195
PRB: Excel Values Returned as NULL Using DAO OpenRecordset (Q194124)
http://support.microsoft.com/default.aspx?scid=kb;EN-US;q194124

Anthony
0
 

Author Comment

by:mitai
ID: 7152499
Thank you for such a quick response!
0

Featured Post

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

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

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

707 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