Reply to thread

Message: [QUOTE="ruffnro, post: 6424321"] 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 [/QUOTE]