Import external data from Access query with Variable

R

ruffnro

Hello - I have been able to record a macro where I can change the
"inventory.Area" from US006 to US007. I would like to be able to make the
area a variable and pick up the area code from within a spreadsheet.

For example, I am doing reporting for 10 areas and would like to have one
template that pulls in the data from Access for one area, saves the file,
goes to the next area, pulls in that data from Access and so on. But I
cannot figure out how to make the area in the VBcode below a variable.

Any help would be appreciated.

Sub Macro3()
With Selection.QueryTable
.Connection = Array(Array( _
"ODBC;DSN=MS Access Database;DBQ=C:\Documents and
Settings\ruffnro\My Documents\__Reporting\Monthly Reporting\_ Financial
Reporting.m" _
), Array( _
"db;DefaultDir=C:\Documents and Settings\ruffnro\My
Documents\__Reporting\Monthly Reporting;DriverId=25;FIL=MS Access;MaxBufferS"
_
), Array("ize=2048;PageTimeout=5;"))
.CommandText = Array( _
"SELECT inventory.Area, inventory.`Client No`, inventory.`Client
Name`, inventory.SEC, inventory.`CP Name`, inventory.`Net Unbilled`,
inventory.`Net Billed`, inventory.`net Invty`" & Chr(13) & "" & Chr(10) &
"FROM inventory inven" _
, "tory" & Chr(13) & "" & Chr(10) & "WHERE (inventory.Area='US007')")
.Refresh BackgroundQuery:=False
End With
End Sub
 
M

Mike

Dim sTheArea As String
sTheArea = ActiveSheet.Range("A1").Value

"WHERE (inventory.Area='" & sTheArea & "')")
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top