Import external data from Access query with Variable

[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

 

[Mike]

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

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