Excel VBA - MSQuery table - make dynamic

[arnie27]

Hi, I am trying to access some AS400 tables dynamically via MS Query. I have recorded a macro with one set of criteria, however I want the user to be able to amend this criteria without going directly into the query and just by entering the data in a cell in the spreadsheet. I cannot seem to be able to do it...Below is an extract from my code...any help greatly appreciated. I set up a named range for each of the bold criteria below and added them as a Dim String...but do not really know where to place in the code below as each time I try I get error messages in the code.

Many thanks

Range("Table_Query_2_from_xxxxx[AMAUCD]").Select
With Selection.ListObject.QueryTable
.Connection = Array(Array( _
"ODBC;DRIVER={Client Access ODBC Driver (32-bit)};SYSTEM=xx.xx.x.x;DBQ=xxxxx;DFTPKGLIB=QGPL;LANGUAGEID=ENU;PKG=QGPL/DEFAULT(IBM)," _
), Array("2,0,1,0,512;QRYSTGLMT=-1;"))
.CommandText = Array( _
"SELECT FTAMCPL3.AMAUCD, FTAMCPL3.AMCNCF, FTAMCPL3.AMBZNR, FTAMCPL3.AMB0NR, FTAMCPL3.AMBQNR, FTAMCPL3.AMAHST, FTAMCPL3.AMARCD, FTAMCPL3.AMD0CE, FTAMCPL3.AMAVCD, FTAMCPL3.AMAWCD, FTAMCPL3.AMABNB, FTAMCP" _
, _
"L3.AMA9CE, FTAMCPL3.AMI1NB, FTAMCPL3.AMAKCD, FTAMCPL3.AMAOCD, FTAMCPL3.AMG0DT, FTAMCPL3.AMBFTM, FTAMCPL3.AMG2CE, FTAMCPL3.AMUSR" & Chr(13) & "" & Chr(10) & "FROM SS1RESAV.xxxx.xxx FTAMCPL3" & Chr(13) & "" & Chr(10) & "WHERE (FTAMCPL3.AMD0CE='1268" _
, "635') AND (FTAMCPL3.AMG2CE='059110')" & Chr(13) & "" & Chr(10) & "ORDER BY FTAMCPL3.AMBZNR")
.Refresh BackgroundQuery:=False
End With
End Sub

 

[John_w]

I want the user to be able to amend this criteria without going directly into the query and just by entering the data in a cell in the spreadsheet

Have you tried using parameter cells? They are designed for the purpose you describe, without needing VBA code.

To add parameter cells, edit the query's SQL Command text and replace the WHERE values with question marks. The SQL command should then look like:

SELECT FTAMCPL3.AMAUCD, FTAMCPL3.AMCNCF, FTAMCPL3.AMBZNR, FTAMCPL3.AMB0NR, FTAMCPL3.AMBQNR, FTAMCPL3.AMAHST, FTAMCPL3.AMARCD, FTAMCPL3.AMD0CE, FTAMCPL3.AMAVCD, FTAMCPL3.AMAWCD, FTAMCPL3.AMABNB, FTAMCP
L3.AMA9CE, FTAMCPL3.AMI1NB, FTAMCPL3.AMAKCD, FTAMCPL3.AMAOCD, FTAMCPL3.AMG0DT, FTAMCPL3.AMBFTM, FTAMCPL3.AMG2CE, FTAMCPL3.AMUSR
FROM SS1RESAV.xxxx.xxx FTAMCPL3 WHERE (FTAMCPL3.AMD0CE=?) AND (FTAMCPL3.AMG2CE=?) ORDER BY FTAMCPL3.AMBZNR

Note - try the command as above, or with apostrophes surrounding each question mark: '?'.

When you click OK in the Connection Properties dialogue Excel should prompt you to select a cell for each parameter. You can't use named ranges for these parameters, they must be specific cells.