| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Spreadsheet to access
Urkec,
You are great, it works fine. Thank you very much!! "urkec" wrote: "boris" wrote: Please,help When i run the code below i get message: "the connection can not be used to perform this operation. it is either closed or invalid in this context Private Sub ADOFromExcelToAccess() Dim cn As ADODB.Connection, rs As ADODB.Recordset, r As Long Set cn = New ADODB.Connection 'Replace with the path to your database cn = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=C:\Documents and Settings\Boris\My Documents\test/" _ & "Data.mdb;Persist Security Info=False" Set rs = New ADODB.Recordset rs.Open "test", cn, adOpenKeyset, adLockOptimistic, adCmdTable r = 2 ' the starting 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("C" & r).Value .Fields("FieldName3") = Range("D" & r).Value .Fields("FieldName4") = Range("F" & 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 Hi Boris, I think you need to use cn.Open before Set rs = New ADODB.Recordset rs.Open "test", cn, adOpenKeyset, adLockOptimistic, adCmdTable Hope that helps. -- urkec |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| limited access spreadsheet | Excel Discussion (Misc queries) | |||
| Import Spreadsheet into Access | Excel Worksheet Functions | |||
| Remote Access to a Spreadsheet | Excel Discussion (Misc queries) | |||
| need help with using VBA to access a second spreadsheet | Excel Discussion (Misc queries) | |||
| cannot access excel spreadsheet | Excel Discussion (Misc queries) | |||
Hybrid Mode
