CODEWITHSUNDEEP
excelvbams-accessado
user319940
user319940

Reputation: 3317

Insert Data from Excel into Access

I've made some code to insert data from an excel table in to an access database - my code is as follow:

    Sub AddData()

Dim Cn As ADODB.Connection

Set Cn = New ADODB.Connection

'lets connect to the workbook first, I tested this, it works for me
Cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=sample.xls;Extended Properties=Excel 8.0;" _
& "Persist Security Info=False"

' Append data from Sheet1 of workbook to Table1 of mydb.mdb:
Cn.Execute "INSERT INTO tblSales IN 'C:\Users\User\Documents\access.mdb' SELECT * FROM [datasheet]"

Cn.Close
Set Cn = Nothing

End Sub

My problem is when executing this I get the error "Microsoft Jet Engine could not find the path to object "datasheet" . Datasheet is just the name of the sheet where the data is located in my workbook. Any help is much appreciated.

Upvotes: 4

Views: 38312

Answers (5)

WIL
WIL

Reputation: 127

xlFilepath = Application.ThisWorkbook.FullName
Sql = "INSERT INTO tblSales " & _

"SELECT * FROM [Excel 12.0 Macro;HDR=YES;DATABASE=" & xlFilepath & "].[datasheet$A1:AK10011] "

cnn.Execute Sql

Warning in your Datasheet Table The first 8 rows. Assuming theres a Heading (HDR=YES) the next 6 rows should contain a dummy data to define your columns equivalent to your access table column definition.

Upvotes: 2

Fionnuala
Fionnuala

Reputation: 91316

All that is missing, as far as I can see, is the path to your data source and a string on datasheet:

Data Source=sample.xls;

Should read, for example:

Data Source=c:\docs\sample.xls;

And:

SELECT * FROM [datasheet$]

Upvotes: 0

MikeD
MikeD

Reputation: 8941

The SELECT statement runs on the database itself, but you want to send values from EXCEL. So you must use

cn.Execute "INSERT .... VALUES (" & excelcell_or_variable & ");"

eventually in a loop to proces all rows/columns etc.

Hope that helps

good luck

EDIT ... don't forget quotation marks surrounding CHAR's and interpunctations; I use

' ....
' .... "...VALUES (" & T(Q(MyStringCell)) & T(MyNumCell) & Q(MyLastTextCell) & ");"
' ....

' surrounds a string by single quotes
Private Function Q(Arg as String) As String
    Q = "'" & Arg & "'"
Return

' appens a comma to a string
Private Function T(Arg as String) As String
    T = Arg & ","
Return

EDIT 2 I asume that in EXCEL the values you want to insert into the DB are all in 1 row ...

Suppose you have a source_range which contains more than 1 row, you must fire the INSERT statement for each row in that range. You use the .Rows property to return a single row from a range. And you send multiple columns to the INSERT statement within the same row by using .Cells(1, 1), .Cells(1,2) .... and so on

example:

Sub Test()
Dim MyRange As Range, MyRow As Range

    Set MyRange = Range([B4], [C8])   ' source range

    For Each MyRow In MyRange.Rows    ' get one row at the time from source range 
        Debug.Print MyRow.Cells(1, 1), MyRow.Cells(1, 2)
        ' replace the above by your INSERT statement
    Next MyRow

End Sub

Upvotes: -1

iDevlop
iDevlop

Reputation: 25252

I think you cannot execute a query on just any open workbook. It MUST be run against a file, that means you have to provide a full path to you sheet, including the filename. If your workbook is "dirty", you need to save it first. I would rather

  • loop the worksheet lines and add the records one by one
  • or use the code you just wrote from Access (if it is suitable)

Upvotes: 0

dimitarie
dimitarie

Reputation: 437

What happens if you put a $ sign after the sheet name like this [datasheet$] ?

Upvotes: 1

Related Questions