Multiple MS-Queries in the same Workbook

[JWGoldfinch]

I have a workbook with multiple MS-Queries built into it. These are ODBC access to data on an IBM iSeries 400. I need to know if there is a way to have the queries process in a given order. Example Query 1 is dependent on the results of query 2. Currently when I press "Refresh All" it runs the in order 1 then 2, I then have to refresh query 1 separately again to gain the desired results. This would not be bad if the workbook only had 2 queries, but I have workbooks with as much as 8 queries (Or More) and if there were 8 I might need them processed (4,1,2,5,3,7,6,8) when query 4 depends on results from query 8, Etc. Is there a way to give Excel an order of processing? Any Assistance would be greatly Appreciated.

 

[Fazza]

Yes there is a way - refresh the queries individually in the order wanted. This is the default in Excel I work in - there is no 'Refresh All' for me (except in VBA).

I don't know your Excel version but I expect this will work - try selecting a cell in the resultset/table/output then ALT-D-R to refresh that query, And so on for other queries.

You may find it easier with VBA. Again, without specifics of your Excel version I can't advise specifics. Maybe best you use the macro recorder & modify the generated code. Basic idea is something like,

worksheetTwo.HoweverQueriesAreReferencedOnYourVersion(m).refresh
worksheetOne.HoweverQueriesAreReferencedOnYourVersion.refresh
worksheetThree.HoweverQueriesAreReferencedOnYourVersion(p).refresh

Check too that the queries have backgroundquery set to false - so that the query refresh won't be done in the "background" and allow other calculations to be done before it finishes.

PS. Different versions of Excel VBA refer differently to query objects. Hence I can't advise specifics. The macro recorder should show what you need, or otherwise google.