C
cecil23
Hello -- One of the Excel Tips (re-printed below) discusses how t
export Excel worksheet into Access. I tried to adapt it, but I get a
error ("Compile error: User-defined variable not defined") at "cn A
ADODB.Connection" in the code.
I've loaded Tools>References "ActiveX Data Object (Multidimensional
2.5 Library" (the Excel Tip below suggests loading "ActiveX Dat
Objects x.x Object Library", but nothing with exactly that nam
appears.
Ideas? Thanks in advance.
Cecil23
******************************************
If you want to export data to an Access table from an Excel worksheet
the macro example below shows how this can be done:
Sub ADOFromExcelToAccess()
' exports data from the active worksheet to a table in an Acces
database
' this procedure must be edited before use
Dim cn As ADODB.Connection, rs As ADODB.Recordset, r As Long
' connect to the Access database
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=C:\FolderName\DataBaseName.mdb;"
' open a recordset
Set rs = New ADODB.Recordset
rs.Open "TableName", cn, adOpenKeyset, adLockOptimistic, adCmdTabl
' all records in a table
r = 3 ' the start row in the worksheet
Do While Len(Range("A" & r).Formula) > 0
' repeat until first empty cell in column A
With rs
.AddNew ' create a new record
' add values to each field in the record
.Fields("FieldName1") = Range("A" & r).Value
.Fields("FieldName2") = Range("B" & r).Value
.Fields("FieldNameN") = Range("C" & r).Value
' add more fields if necessary...
.Update ' stores the new record
End With
r = r + 1 ' next row
Loop
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
End Sub
The macro example assumes that your VBA project has added a referenc
to the ADO object library.
You can do this from within the VBE by selecting the menu Tools
References and selecting Microsoft
ActiveX Data Objects x.x Object Library.
Use ADO if you can choose between ADO and DAO for data import o
export
export Excel worksheet into Access. I tried to adapt it, but I get a
error ("Compile error: User-defined variable not defined") at "cn A
ADODB.Connection" in the code.
I've loaded Tools>References "ActiveX Data Object (Multidimensional
2.5 Library" (the Excel Tip below suggests loading "ActiveX Dat
Objects x.x Object Library", but nothing with exactly that nam
appears.
Ideas? Thanks in advance.
Cecil23
******************************************
If you want to export data to an Access table from an Excel worksheet
the macro example below shows how this can be done:
Sub ADOFromExcelToAccess()
' exports data from the active worksheet to a table in an Acces
database
' this procedure must be edited before use
Dim cn As ADODB.Connection, rs As ADODB.Recordset, r As Long
' connect to the Access database
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=C:\FolderName\DataBaseName.mdb;"
' open a recordset
Set rs = New ADODB.Recordset
rs.Open "TableName", cn, adOpenKeyset, adLockOptimistic, adCmdTabl
' all records in a table
r = 3 ' the start row in the worksheet
Do While Len(Range("A" & r).Formula) > 0
' repeat until first empty cell in column A
With rs
.AddNew ' create a new record
' add values to each field in the record
.Fields("FieldName1") = Range("A" & r).Value
.Fields("FieldName2") = Range("B" & r).Value
.Fields("FieldNameN") = Range("C" & r).Value
' add more fields if necessary...
.Update ' stores the new record
End With
r = r + 1 ' next row
Loop
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
End Sub
The macro example assumes that your VBA project has added a referenc
to the ADO object library.
You can do this from within the VBE by selecting the menu Tools
References and selecting Microsoft
ActiveX Data Objects x.x Object Library.
Use ADO if you can choose between ADO and DAO for data import o
export