Hi All - I currently have a vba macro setup to import data from MSACCESS into Excel using the following command which works great
Set ShDest = Sheets("Excel Sheet Name")
Set cnn = New ADODB.Connection
MyConn = TARGET_DB
With cnn
.Provider = "Microsoft.ACE.OLEDB.12.0"
.Open MyConn
End With
Set rst = New ADODB.Recordset
rst.CursorLocation = adUseServer
rst.Open Source:="[Database Table]", ActiveConnection:=cnn, _
CursorType:=adOpenDynamic, LockType:=adLockOptimistic, _
Options:=adCmdTable
'clear existing data on the sheet
ShDest.Activate
Range("A1").CurrentRegion.Offset(1, 0).Clear
'create field headers
i = 0
With Range("A1")
For Each fld In rst.Fields
.Offset(0, i).Value = fld.Name
i = i + 1
Next fld
End With
'transfer data to Excel
Range("A2").CopyFromRecordset rst
' Close the connection
rst.Close
cnn.Close
Set rst = Nothing
Set cnn = Nothing
The database contains all of our customer information and this query pulls all the data out of the table into Excel - What I can't figure out is how to use this to only pull the data for specific customers only.
Can anyone assist?
Set ShDest = Sheets("Excel Sheet Name")
Set cnn = New ADODB.Connection
MyConn = TARGET_DB
With cnn
.Provider = "Microsoft.ACE.OLEDB.12.0"
.Open MyConn
End With
Set rst = New ADODB.Recordset
rst.CursorLocation = adUseServer
rst.Open Source:="[Database Table]", ActiveConnection:=cnn, _
CursorType:=adOpenDynamic, LockType:=adLockOptimistic, _
Options:=adCmdTable
'clear existing data on the sheet
ShDest.Activate
Range("A1").CurrentRegion.Offset(1, 0).Clear
'create field headers
i = 0
With Range("A1")
For Each fld In rst.Fields
.Offset(0, i).Value = fld.Name
i = i + 1
Next fld
End With
'transfer data to Excel
Range("A2").CopyFromRecordset rst
' Close the connection
rst.Close
cnn.Close
Set rst = Nothing
Set cnn = Nothing
The database contains all of our customer information and this query pulls all the data out of the table into Excel - What I can't figure out is how to use this to only pull the data for specific customers only.
Can anyone assist?