Insert data from Excel to Access using VBA

Question

I made a code to insert data from an excel table into an access database - my code looks like this:

    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 that when I run this error, I get the error message "Microsoft Jet Engine could not find the path to the object." Datasheet is just the name of the sheet in which the data is in my book ..

Answer 1

What happens if you put a $ sign after a sheet name like this [data table]

Answer 2

, . , , , . "", .

• , Access ( )

Answer 3

, , , - :

Data Source=sample.xls;

, :

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

SELECT * FROM [datasheet$]

Answer 4

SELECT , EXCEL.

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

/ ..

,

... , CHAR ;

' ....
' .... "...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

2 , EXCEL , , ...

, source_range, 1 , INSERT . .Rows . INSERT .Cells(1, 1), .Cells(1,2).... ..

:

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