Insert Data from Excel into Access
35,864
What happens if you put a $ sign after the sheet name like this [datasheet$] ?
Author by
user319940
Updated on July 06, 2020Comments
-
user319940 almost 4 years
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.
-
MikeD over 13 yearssee my edit ... pay attention to the data type receiving the insert; and of course your insert must fulfill all table constraints (i.e. the not null's, references etc.) and be in the format (dates!!) the DB wants to see it
-
user319940 over 13 yearsThanks for the help - how would this work if I wanted to select a range? for example A:E?
-
MikeD over 13 yearsahhh .... so I recon you got the quotations and interpunctations right now ... good so ... for the rest see my EDIT 2 ... hope it helps :-)
-
Fionnuala over 13 yearsThis is simply not true. It is quite possible to update from an Excel sheet to Access using ADO. Please reconsider your answer.
-
Fionnuala over 13 yearsLooping is slow, ADO is fast. You do not need an open workbook, but you can run ADO against the current workbook, if as you say, you have saved.
-
Fionnuala over 13 yearsIt will, if you afdd the full name of the file.