Thank you for your help so far...
I get a "Run time error 9 - subscript out of range error" for the following
code:

Const OriginalID = "DASSMY"
AS400ID = InputBox("Enter the AS400 name:")
With Sheets("Colors").QueryTables("DASPVIEWS.COLOR_CODE ").CommandText = _

Replace(Sheets("Colors").QueryTables("DASPVIEWS.CO LOR_CODE").CommandText,
OriginalID, AS400ID)
.Refresh BackgroundQuery:=False
End With
Cells.Select
Selection.Columns.AutoFit
ActiveWorkbook.Save

Also, can the AS400ID be typed into a cell on the worksheet and passed into
the SQL?

"K Dales" wrote:

Clarification: I used "MyQueryTable" as a 'placeholder' since I did not know
the name of your table - but from the code I see it is
Sheets("Colors").QueryTables("DASPVIEWS.COLOR_CODE "), so that line should
read:
Sheets("Colors").QueryTables("DASPVIEWS.COLOR_CODE ").CommandText =
Replace(Sheets("Colors").QueryTables("DASPVIEWS.CO LOR_CODE").CommandText ,
OriginalID, AS400ID)

To simplify your code you could do this:

Const OriginalID = "DASSMY"
AS400ID = InputBox("Enter the AS400 name:")
With Sheets("Colors").QueryTables("DASPVIEWS.COLOR_CODE ")
.CommandText = Replace(.CommandText, OriginalID, AS400ID)
.Refresh BackgroundQuery:=False
End With
Cells.Select
Selection.Columns.AutoFit
ActiveWorkbook.Save

I hope this clears it up and gets you going!

"mtate" wrote:

I am fairly new to this type of thing, so bear with me. I get the attached
error: 424 Object Required after inputting the AS400 ID. My code is as
follows (I'm sure I did something wrong...)

Const OriginalID = "DASSMY"
AS400ID = InputBox("Enter the AS400 name:")
Sheets("Colors").QueryTables("DASPVIEWS.COLOR_CODE ").CommandText =
Replace(MyQueryTable.CommandText, OriginalID, AS400ID)

Range("A2").Select
Selection.QueryTable.Refresh BackgroundQuery:=False
Cells.Select
Selection.Columns.AutoFit
ActiveWorkbook.Save

I'm running this from a button on the spreadsheet. My original AS400
machine id is DASSMY. The table is located at DASPVIEWS.COLOR_CODE. This is
the library and table name.
Any way to enter the machine id into a cell on the Worksheet? Any help
would be appreciated.



"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?