In addition to updating the Connection property to update the DB command
string, in order to change the FROM clause in the SQL you will need to modify
the CommandText property, which should contain the SQL query. You could use
the Replace function to modify it, e.g.
Replace(Sheets("MySheet").QueryTables(1).CommandTe xt, "FROM AAA", "FROM BBB")

"Robert Bruce" wrote:

mtate wrote:
Question...
We have quite a few queries developed in Excel/MS Query that query an
AS400. We need to share these with users at other locations that use
an AS400 with a different name. Is there any way around changing the
SQL FROM statement for the AS400 machine name to another AS400
machine name without editing the query in MS Query and doing it in
View, SQL? Looking for the quick, easy way to do this, because there
are too many to change this way.

If you used MSQuery to return the results then you will have a QueryTable
object on your worksheet. As long as you chose to save the query definition
with the sheet, the QueryTable object will have a Connection property that
corresponds to the connectionstring used by the query. Your mission, I
suppose, is to ensure that this property is updated with the appropriate
value depending on who opens the workbook. You could put a lookup table of
usernames and connectionstrings on a hidden sheet.

Rob