Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
ms query - from statement
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
ms query - from statement
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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
ms query - from statement
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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
ms query - from statement
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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
ms query - from statement
what is wrong with using standard Excel msquery SQL ODBC parameters ?
for these standard queries they should suffice. No coding required for use with QueryTables at all? Dm Unseen |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
ms query - from statement
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? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
ms query - from statement
Using MSQuery, and type in/change your SQL in the option VIEW-SQL. For
parameter placseholder type in "?" (questionmark). Refresh this query and type in your parameter. Now exit to Excel and refresh again and when the parameter prompt comes select a cell adn enter a value in the cell. Now you've created aparameterized query in excel Using VBA AS400ID = Sheets("Colors").Range("A1").value |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
ms query - from statement
It will not let me do that. It gives a SQL0113 - Name "?" not allowed error
and will not save it. "DM Unseen" wrote: Using MSQuery, and type in/change your SQL in the option VIEW-SQL. For parameter placseholder type in "?" (questionmark). Refresh this query and type in your parameter. Now exit to Excel and refresh again and when the parameter prompt comes select a cell adn enter a value in the cell. Now you've created aparameterized query in excel Using VBA AS400ID = Sheets("Colors").Range("A1").value |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
ms query - from statement
Hm, MSQUERY is defnitly very picky. You could either try to recreate
the general query with MSQuery, add a filter, and change the filter value with "[Parameter 1]" (no quotes). The problem is that MSQuery prevents you from doing serious SQL queries and only works when you follow it's own format which your query probably does not, thus hand coded SQL can be rejected for parameters. It is not very user friendly (what am I saying? It is an SQL editor nightmare!). For the advanced option I would suggest you go to http://homepages.paradise.net.nz/~ro...eryeditor.html This tool works wonders on unwilling SQL reports. First be shure you can edit the SQL with this tool and you can see the SQL (and edit the query). For good database reports this tool is indispensible (I use a similar tool that I developed myself). First run it without a parameter( just hardcode the value). After that replace the value with a questionmark and refresh again. A parameter prompt should show up, and you can let that reference a cell. Once you know SQL, and use this editor you'll find that creating Excel database reports *can* be fun. Hope this quick primer on Excel SQL reports helps DM Unseen |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
IF Statement Query | Excel Worksheet Functions | |||
If statement query | Excel Worksheet Functions | |||
IF statement query | Excel Discussion (Misc queries) | |||
IF statement query | Excel Worksheet Functions | |||
Use MS Query in VBA to only get a SQL statement | Excel Programming |