PDA

View Full Version : Export Excel Range to Access



ndendrinos
04-10-2007, 03:32 PM
Sub Auto_close()
' exports data from the active worksheet to a table in an Access database

Dim cn As ADODB.Connection, rs As ADODB.Recordset
' connect to the Access database

Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=C:\Documents and Settings\nick dendrinos\Desktop\vault.mdb;"
' open a recordset
Set rs = New ADODB.Recordset
rs.Open "invoices", cn, adOpenKeyset, adLockOptimistic, adCmdTable

With rs
.AddNew ' create a new record
' add values to each field in the record
.Fields("name") = Range("A2").Value
.Fields("total") = Range("B2").Value
.Fields("date") = Range("C2").Value
.Update ' stores the new record
End With

rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing



End Sub


I use the above to transfer data from an Excel invoice to an Access Database.
The section where I type the part # , Description, Qty, Unit Price is ten rows long and needs to be added to this code.
Is there an easier way to do this than :
.Fields("name") = Range("A2").Value
.Fields("total") = Range("B2").Value
.Fields("date") = Range("C2").Value Where I would replace the Fields names to match each of the 40 cells and input the individual Cell Range to match the Field name.

Thank you

XLGibbs
04-10-2007, 06:00 PM
You could do an INSERT INTO statement via SQL instead...

Sub Auto_close()
' exports data from the active worksheet to a table in an Access database

Dim cn As ADODB.Connection, rs As ADODB.Recordset
' connect to the Access database

Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=C:\Documents and Settings\nick dendrinos\Desktop\vault.mdb;"
' open a recordset
Set rs = New ADODB.Recordset
rs.Open "invoices", cn, adOpenKeyset, adLockOptimistic, adCmdTable

Dim StrSQL as string
'build you SQL Statement here
strSQL = "Insert Into Invoices (Name, Total,[Date]) "
strSQL = StrSQL & " Select " & Range("A2").Value & "," & Range("B2").Value & "," & Range("C2").Value

'execute the SQL Statement here
rs.Execute strSQL

rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing



End Sub

ndendrinos
04-10-2007, 07:20 PM
Hello Pete and thanks for your reply.
SQL ? Did a Google on that and noticed the "How to buy" ... to me it means that SQL is not part of excel (did find Microsoft SQL Parser OBJ Library 1.0) Activated same , run your code got an error:
Compile Error / Method or Data Member not found
With the section ".Execute" in your code highlighted in blue.

XLGibbs
04-10-2007, 07:33 PM
It isn't anything you have to add special. SQL is Structured Query Language and is standard database language.

The .execute method should be part of ADODB

But I should have been more precise.

You can also just do

rs.Open strSQL

ndendrinos
04-12-2007, 02:48 PM
Pete,Sorry was away all day yesterday . None of the two solution work ... with this edit I get : Operation not allowed when the object is open ... Any ideas ?
rs.Open strSQL

Charlize
04-12-2007, 03:28 PM
Do we have to have a reference to "microsoft ado ext 2.8 ..." to use ADO things ? Or some other reference ?

Charlize

Charlize
04-12-2007, 03:38 PM
You could do an INSERT INTO statement via SQL instead...

Sub Auto_close()
' exports data from the active worksheet to a table in an Access database

Dim cn As ADODB.Connection, rs As ADODB.Recordset
' connect to the Access database

Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=C:\Documents and Settings\nick dendrinos\Desktop\vault.mdb;"
' open a recordset
Set rs = New ADODB.Recordset
rs.Open "invoices", cn, adOpenKeyset, adLockOptimistic, adCmdTable

Dim StrSQL as string
'build you SQL Statement here
strSQL = "Insert Into Invoices (Name, Total,[Date]) "
strSQL = StrSQL & "VALUES (' " & Range("A2").Value & " ', "
strSQL = StrSQL & " ' " & Range("B2").Value & " ', "
strSQL = StrSQL & " ' " & Range("C2").Value & " ');"

'execute the SQL Statement here
rs.Execute strSQL, dbFailOnError

rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing

End SubJust a try out. Charlize

ndendrinos
04-13-2007, 09:13 AM
Thank you Charlize . Your solution gave me an error also " Compile error/ Method or data member not found" . and yes I did activate microsoft ado ext 2.8.
Looks impossible to significantly shorten this code so I guess I will use the initial code in my sample and add to it.
I thank you all for your kind input.
Please consider this thread now closed

Charlize
04-15-2007, 11:59 AM
It depends on the layout of your sheet. If everything is on 1 row A2,B2,C2 ... you could use a loop with cells(1,x).value and store the field names in an array. Then you say For Each item in array. Inside the For Each-loop you add 1 to x. That would be a line or 5 instead of 40 ? Is this a possible way or not ?

Charlize

ndendrinos
04-15-2007, 12:18 PM
You are correct Charlize, And I now have the code that does it this way.
I was hoping for a simple "copy" of a row in Excel with a "paste" in Access but learned that this is not possible.
I thank you for your kind help.
I will be back to post the entire code I'm working on when all done with proper credit to its author.
Kind regards.
Nick