Copy data from excel to access

[aluksnietis2]

Hello, i need to copy some fields from excel to access database.
Im new to databases, so i dont know much. i found this macro, but it shows me error at range. could someone help me please?


here is code:




Sub ADOFromExcelToAccess()
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:\Users\Lietotajs\Desktop\Database3.mdb;"
' open a recordset
Set rs = New ADODB.Recordset
rs.Open "Tabula", cn, adOpenKeyset, adLockOptimistic, adCmdTable
' all records in a table
r = 3 ' the start row in the worksheet
Do While Len(Range(I9).Formula) > 0 <---- error
' repeat until first empty cell in column A
With rs
.AddNew ' create a new record
' add values to each field in the record
.Fields("Nr") = Range("I" & 9).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

[jscriptor09]

Hi

Your code seems to be attempting to read an ACCESS DB.

In any case .... have your tried one of the docmd.Transfer methods? Here is what I do to import a PIPE separated csv file

If (mdr_verifyTableExists("aTable")) Then DoCmd.DeleteObject acTable, "aTable"
DoCmd.TransferText acImportDelim, "Land1ImportSpecification", "aTable", "C:\myExcel\MYCSV.txt", True

this will pick up the CSV data and put it in a table called aTable.

Theare are docmd transfer for excl sheets as well that you can google

[hertfordkc]

Hello, i need to copy some fields from excel to access database.
Im new to databases, so i dont know much. i found this macro, but it shows me error at range. could someone help me please?


here is code:


Sub ADOFromExcelToAccess()
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:\Users\Lietotajs\Desktop\Database3.mdb;"
' open a recordset
Set rs = New ADODB.Recordset
rs.Open "Tabula", cn, adOpenKeyset, adLockOptimistic, adCmdTable
' all records in a table
r = 3 ' the start row in the worksheet
Do While Len(Range(I9).Formula) > 0 <---- error Range(I9)
' repeat until first empty cell in column A
With rs
.AddNew ' create a new record
' add values to each field in the record
.Fields("Nr") = Range("I" & 9).Value Range("I"&9)
' 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

I'm not up to really analyzing this yet, but doing a test on a fixed location doesn't seem right.

[aluksnietis2]

Thanks for replys, but i made new macro, which is almost completed and is easyer to use (i think so).

here is code:


Sub macros()
'
' macros Macro
'

'
Range("I9").Select
Selection.Copy
Dim oApp As Object
Dim LPath As String
Dim LCategoryID As Long
'Path to Access database
LPath = "C:\Users\Lietotajs\Desktop\Database3.accdb"

'Open Access and make visible
Set oApp = CreateObject("Access.Application")
oApp.Visible = True

'Open Access database as defined by LPath variable
oApp.OpenCurrentDatabase LPath

'Open form called Categories filtering by CategoryID
'LCategoryID = Range("A2").Value
oApp.DoCmd.OpenForm "tabula" ', , , "CategoryID = " & LCategoryID
SendKeys "{ENTER}", True
SendKeys ("^{V}"), True
SendKeys "{ENTER}", True



End Sub


it copy one cell, then opens database, then form, but i dont know how to paste it to form, i cant find any command that would be usefull, i found that you can user this command(it should work as ctrl + v) ---->sendkeys {"^(V)"}<----- but when i write it and run macros, it opens database and close it after it opens, but wont copy data, which i copy from that cell.. could anyone please help me with that command or say better one please??

[shabbaranks]

Hi,

Not to jump on your thread, but I think I might be trying to do the exact same thing as you - please correct me if I am not.

I am trying to import a perticular column from a perticular sheet from within Excel. I have read up on this and found the DoCmd.TransferSpreadsheet seems to be the approach to go for as per http://msdn.microsoft.com/en-us/libr...ice.11%29.aspx

I am yet to experiment with this but thought I would add my imput - I too am not that experienced with Access/DB's so maybe we could help each other out - along with all the experts here too

[aluksnietis2]

Hello, looks like you are right. Looks very interesting, but i think it wont be helpfull for me, because i will need to copy many excel tables to my access database, but then i will need to change everytime macro on access database, because name will be different, thatwhy i am writing on excel that macro, so everytime i am done writing excel table it copy's itself to database..

i hope i wrote good enough to understand, because my english isnt very good.

[aluksnietis2]

update:

Sub macros()
'
' macros Macro
'

'
Range("I9").Select
Selection.Copy
Dim oApp As Object
Dim LPath As String
Dim LCategoryID As Long
'Path to Access database
LPath = "C:\Users\Lietotajs\Desktop\Database3.accdb"

'Open Access and make visible
Set oApp = CreateObject("Access.Application")
oApp.Visible = True

'Open Access database as defined by LPath variable
oApp.OpenCurrentDatabase LPath

'Open form called Categories filtering by CategoryID
'LCategoryID = Range("I9").Value
oApp.docmd.OpenForm "tabula" ', , , "CategoryID = " & LCategoryID
SendKeys "{ENTER}", True
SendKeys "{tab}", True
SendKeys "{tab}", True
SendKeys "^{v}", True
SendKeys "{tab}", True
SendKeys "^{v}", True
SendKeys "{tab}", True
SendKeys "^{v}", True
SendKeys "{ENTER}", True
SendKeys "{ENTER}", True
SendKeys "{ENTER}", True
SendKeys "{ENTER}", True
SendKeys "{ENTER}", True
SendKeys "{ENTER}", True
SendKeys "^{s}", True
SendKeys "%{F4}", True


End Sub

now it will copy cell, open database, open form, open new record and paste excel cell there, but it will copy only 1 cell to database, can someone help and make that it will copy more cells to database?