Macro to import external data from Access query to Excel

[ruffnro]

I have tried to post this a couple times, but don't see it after I post it...
so if it out here three times, I am sorry!

I am trying to write code that will allow me to pull in information for
different areas depending on the file that I am creating. For example, I
will create a file for US006 and want the external information for US006 and
then create a file for US007 and want the information for US007.

I created the code below. The fourth line from the bottom references US006.
I would like that to be a variable dependent on the file I am creating. I
cannot seem to get it to work.

Is this doable?

Thanks for your help.

Sub Macro7()
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='US006')")
.Refresh BackgroundQuery:=False
End With
End Sub

 

[Mike]

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

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