XLS->MDB

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
 
B

Brendan Reynolds

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
 
P

Paul Clement

¤ 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 ~~~ [email protected]
Microsoft MVP (Visual Basic)
 
Top