Appending a Excel sheet to an Access Database Via Visual Basic
Posted on 2002-07-13
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
If Err.Number = 3010 Then
MsgBox mTableName & " already exist." & vbCrLf & _
"Delete " & mTableName & " first or use another table name."
MsgBox Err.Number & " " & Err.Description
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?