You could fairly easily get the machine id through an inputbox just before
the query is refreshed and then replace the portion of the FROM clause in the
SQL (querytable's CommandText property), e.g:

Const OriginalID = "XXXX" ' change this to be the actual 'hard coded'
machine ID that you used when you created the query
MachineID = InputBox("Enter the machine ID for this location:")
Sheets("SheetName").QueryTables("QueryTableName"). CommandText =
Replace(MyQueryTable.CommandText, OriginalID, MachineID)

How you activate the code depends on how you are currently refreshing the
query. This could mean building the above into the Workbook_Open event
procedure, attaching it to a macro that runs when activated by a toolbar
button or command button, etc. The most general, but most difficult way, to
code it would be to build a class module "With Events" so you could put it
into the BeforeRefresh event procedure of the querytable.


"mtate" wrote:

We have numerous queries that we would like to run against AS400's at
different locations. The problem is each AS400 has a different machine id.
When a ms query is created in Excel it records this in the FROM statement in
the SQL. Is there any way to get around this? We know how to change the
SQL, but want to be able to prompt for the AS400 name or at least enter it
into a cell on the worksheet. Is this possible?