Greating People,
I have a Excel VBA code that will import data from Access database to Excel sheet.
For example, It will import Customer_ID to Excel Range("D4").
There is nothing wrong with this code, take a look:
[VBA]Option Explicit
Sub Access_Data(Query As String)
'Requires reference to Microsoft ActiveX Data Objects xx Library
Dim Cn As ADODB.Connection, Rs As ADODB.Recordset
Dim MyConn, sSQL As String
Dim Rw As Long, Col As Long, C As Long
Dim MyField, Location As Range
Dim A As Long
'Set destination
Set Location = Worksheets("BackEnd").Range("D4")
MsgBox (Location)
'Set source
MyConn = "C:\Users\Desktop\Backup\Working\Bookings.mdb"
'Create query
sSQL = Query
'Create RecordSet
Set Cn = New ADODB.Connection
With Cn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Open MyConn
Set Rs = .Execute(sSQL)
End With
'Write RecordSet to results area
Rw = Location.Row
Col = Location.Column
C = Col
Do Until Rs.EOF
For Each MyField In Rs.Fields
Cells(Rw, C) = MyField
C = C + 1
Next MyField
Rs.MoveNext
Rw = Rw + 1
C = Col
Loop
Set Location = Nothing
Set Cn = Nothing
End Sub
[/VBA]
However, I am facing a problem as this code will Import data to the active sheet Range("D4") instead of BackEnd sheet range("D4").
I am confident that it is because of the bottm code which only write to column and row but never specific the sheet.
[VBA] 'Write RecordSet to results area
Rw = Location.Row
Col = Location.Column
C = Col
Do Until Rs.EOF
For Each MyField In Rs.Fields
Cells(Rw, C) = MyField
C = C + 1
Next MyField
Rs.MoveNext
Rw = Rw + 1
C = Col
Loop
Set Location = Nothing
Set Cn = Nothing[/VBA]
I would really appreciate if someone could help me to define the sheet to import the data. Thanks!