| re: Exporting from Access to specific cell in Excel
Hi, I use this code below and have had no issues with it.
Basically I create a query in Access and copy and paste into the SQL area of this macro.
Option Explicit
Dim cnnDW As ADODB.Connection
Dim rsDW As ADODB.Recordset
Dim sQRY As String
Dim strDWFilePath As String
Sub GetData()
On Error GoTo Err:
strDWFilePath = "Location of the Database.mdb"
Set cnnDW = New ADODB.Connection
'Import all Data I want into Sheet4
Set rsDW = New ADODB.Recordset
'ASV N Week by Contract
Sheet4.Range("B5:BB23").ClearContents
cnnDW.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strDWFilePath & ";"
sQRY = "SELECT * FROM tblTableName " & _
"WHERE tblTableName.Name = 'Jez' "
rsDW.CursorLocation = adUseClient
rsDW.Open sQRY, cnnDW, adOpenStatic, adLockReadOnly
Application.ScreenUpdating = False
Sheet4.Range("B5").CopyFromRecordset rsDW
rsDW.Close
Set rsDW = Nothing
cnnDW.Close
Set cnnDW = Nothing
Exit Sub
Err:
MsgBox VBA.Err
End
You will need to change Sheet4 & the Range to what ever you need it to be and replace the SQL Statement too.
Jez
| 
