XLS->MDB

  • Thread starter Thread starter Guy Cohen
  • Start date Start date
G

Guy Cohen

Hi all,

I am using VB6 + ADO.

Is there a quick way to convert an Excel worksheet to a recordset ?

I have an access mdb file and an excel file.
I want to read the entire content of the excel file and add it as a new
table to an existing databse.

TIA
Guy
 
Public Sub GetExcelData()

Dim strConnectionString As String
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset

'Change the data source, obviously.
strConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Documents and Settings\Brendan Reynolds\My
Documents\" & _
"Test.xls;Extended Properties='Excel 8.0;HDR=Yes';Persist Security "
& _
"Info=False"

With cnn
.ConnectionString = strConnectionString
.Open
End With

With rst
.ActiveConnection = cnn

'"TestNumber" is a named range in the Excel worksheet.
.Open ("SELECT * FROM TestNumber")

Do Until .EOF
Debug.Print .Fields(0).Value, .Fields(1).Value
.MoveNext
Loop
.Close
End With

cnn.Close

End Sub
 
¤ Hi all,
¤
¤ I am using VB6 + ADO.
¤
¤ Is there a quick way to convert an Excel worksheet to a recordset ?
¤
¤ I have an access mdb file and an excel file.
¤ I want to read the entire content of the excel file and add it as a new
¤ table to an existing databse.

Yes, you can use ADO and SQL to perform the import:

Sub ImportExcelToAccess()

Dim cnn As New ADODB.Connection
Dim sqlString As String

cnn.Open _
"Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=E:\My Documents\db1.mdb;" & _
"Jet OLEDB:Engine Type=4"

'Assumes Access table does not already exist
sqlString = "SELECT * INTO [tblExcelNew] FROM [Excel 8.0;DATABASE=E:\My
Documents\Test.xls;HDR=No;IMEX=1].[Sheet1$]"
'Assumes Access table exists
'sqlString = "INSERT INTO [tblExcelNew] (Column1, Column2, Column3, Column4) SELECT Column1,
Column2, Column3, Column4 from [Excel 8.0;DATABASE=E:\My
Documents\Test.xls;HDR=Yes;IMEX=1].[Sheet1$];"

cnn.Execute sqlString
cnn.Close
Set cnn = Nothing

End Sub


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)
 
Back
Top