I have recorded this macro that modifies the information that I am pulling
into Excel from an Access query. About half way down, there is a line that
says, "WHERE (invexcption.Area='US006')". I know that I can modify the code
to bring back another areas information. for example,
invexcption.Area='US007'.

I would like to be able to make the Area a variable, so that I can create
and save a different file for each area... but cannot seem to get it to work.

Does anyone have any thoughts?

With ActiveWorkbook.Connections("Query from MS Access
Database").ODBCConnection
.BackgroundQuery = True
.CommandText = Array( _
"SELECT invexcption.Area, invexcption.`Client No`,
invexcption.`Client Name`, invexcption.SEC, invexcption.`CP Name`" _
, _
", invexcption.`Net Unbilled`, invexcption.`Net Billed`,
invexcption.`net Invty`" & Chr(13) & "" & Chr(10) & "FROM
`C:\MonthlyReportingPackage\F" _
, "inRpting.mdb`.invexcption invexcption" & Chr(13) & "" & Chr(10) &
"WHERE (invexcption.Area='US006')")
.CommandType = xlCmdSql
.Connection = Array(Array( _
"ODBC;DSN=MS Access
Database;DBQ=C:\MonthlyReportingPackage\FinRpting. mdb;DefaultDir=C:\MonthlyReportingPackage;DriverId =25;FIL=MS Ac" _
), Array("cess;MaxBufferSize=2048;PageTimeout=5;"))
.RefreshOnFileOpen = False
.SavePassword = False
.SourceConnectionFile = ""
.SourceDataFile = ""
.ServerCredentialsMethod = xlCredentialsMethodIntegrated
.AlwaysUseConnectionFile = False
End With

Declare a string and populate it from a variable:

dim strSql as string
strSql = "Select blah, blah2, blah3 from mytable where blah4 = ' " &
MyVariable & " ' "

..commandtext = strSql

etc.

Sam



"ruffnro" wrote:

I have recorded this macro that modifies the information that I am pulling
into Excel from an Access query. About half way down, there is a line that
says, "WHERE (invexcption.Area='US006')". I know that I can modify the code
to bring back another areas information. for example,
invexcption.Area='US007'.

I would like to be able to make the Area a variable, so that I can create
and save a different file for each area... but cannot seem to get it to work.

Does anyone have any thoughts?

With ActiveWorkbook.Connections("Query from MS Access
Database").ODBCConnection
.BackgroundQuery = True
.CommandText = Array( _
"SELECT invexcption.Area, invexcption.`Client No`,
invexcption.`Client Name`, invexcption.SEC, invexcption.`CP Name`" _
, _
", invexcption.`Net Unbilled`, invexcption.`Net Billed`,
invexcption.`net Invty`" & Chr(13) & "" & Chr(10) & "FROM
`C:\MonthlyReportingPackage\F" _
, "inRpting.mdb`.invexcption invexcption" & Chr(13) & "" & Chr(10) &
"WHERE (invexcption.Area='US006')")
.CommandType = xlCmdSql
.Connection = Array(Array( _
"ODBC;DSN=MS Access
Database;DBQ=C:\MonthlyReportingPackage\FinRpting. mdb;DefaultDir=C:\MonthlyReportingPackage;DriverId =25;FIL=MS Ac" _
), Array("cess;MaxBufferSize=2048;PageTimeout=5;"))
.RefreshOnFileOpen = False
.SavePassword = False
.SourceConnectionFile = ""
.SourceDataFile = ""
.ServerCredentialsMethod = xlCredentialsMethodIntegrated
.AlwaysUseConnectionFile = False
End With