VBA to run queries in 5 different worksheets
 

hi there,

I've got a bit of a complex problem I need help with.
I have to write some VBA that will do the following in one go:

1. Refresh a query containing a list of names
2. On 5 worskheets I have queries that incoporate a name in the where
clause.
3 For each name returned in the refreshed query in 1. refrsh all 5
worksheets using that name in the whereclause
4. once all 5 worksheets have been refreshed, save the entire
spreadsheet as <name.xls


right, thats it in a nutshell.
I need to know where to type this VBA.
I'm not sure when modules are used etc.

I need to know how to refresh the query on the first sheet (producing
the names)
and then I need to know how to use each name to refresh the 5
subsequwnt worksheets.

any help you can offer is appreciated.
I've got a vague idea about it. but haven't a clue where to start
typing the code, and how to organise the code....

thanks in advance,
Matt


--
matpj
------------------------------------------------------------------------
matpj's Profile: http://www.excelforum.com/member.php...o&userid=21076
View this thread: http://www.excelforum.com/showthread...hreadid=472083

VBA to run queries in 5 different worksheets
 

You can put the code in various places depending on how it is to be executed,
e.g: - will it occur automatically when a user opens a workbook? Then it
would go in the module for ThisWorkbook. Or when they press a commandbutton?
Then it would go in a standard module (Insert Module in VBA). Or (perhaps
best) you can write it as a Public Sub and then call it from whichever of
these other modules you need it in. For example, create a new module and
enter it as:
Public Sub Requery()
....
End Sub

Then, to run it when a commandbutton is pressed, make the button and in its
code module you would have something like
Sub Button1_Click()
Requery
End Sub

I am assuming you created your queries with MSQuery: in VBA the result range
(however big it might be) is called a QueryTable; it's name is the name you
see when you right-click on the range and look at the properties. So to
refresh, you would use this in VBA:
ThisWorkbook.Worksheets("SheetName").QueryTables(" QueryTableName").Refresh

Then you could step through the results like this (QueryRow is a Range
variable):
For Each QueryRow in
ThisWorkbook.Worksheets("SheetName").QueryTables(" QueryTableName").ResultRange.Rows
MyName = QueryRow.Cells(1,4) ' if name is in column D
...
Next QueryRow

Then, to use the name as a parameter in the other queries, first manually
set up the parameter using MSQuery and the range properties (see MSQuery
helpo if you need to know how to do this). Enter a constant parameter value
for now, it doesn't matter what it is. Then this code bit would go inside
the For Each QueryRow loop in the code above (and you would need to either
dupllicate it for the other 4 sheets or work it into a loop of its own):
Set NameQuery =
Workbooks("QueryBook").Worksheets("QuerySheetName1 ").QueryTables("QueryTableName1")
NameQuery.Parameters("ParameterName").Value = MyName 'from above
NameQuery.Refresh

Finally, to save the book using the name this would be the code when you are
done refreshing all the queries:
Workbooks("QueryBook").SaveAs MyName & ".xls"

Those are the building blocks; with a bit of coding hopefully you can put
them together as you need.
--
- K Dales


"matpj" wrote:


hi there,

I've got a bit of a complex problem I need help with.
I have to write some VBA that will do the following in one go:

1. Refresh a query containing a list of names
2. On 5 worskheets I have queries that incoporate a name in the where
clause.
3 For each name returned in the refreshed query in 1. refrsh all 5
worksheets using that name in the whereclause
4. once all 5 worksheets have been refreshed, save the entire
spreadsheet as <name.xls


right, thats it in a nutshell.
I need to know where to type this VBA.
I'm not sure when modules are used etc.

I need to know how to refresh the query on the first sheet (producing
the names)
and then I need to know how to use each name to refresh the 5
subsequwnt worksheets.

any help you can offer is appreciated.
I've got a vague idea about it. but haven't a clue where to start
typing the code, and how to organise the code....

thanks in advance,
Matt


--
matpj
------------------------------------------------------------------------
matpj's Profile: http://www.excelforum.com/member.php...o&userid=21076
View this thread: http://www.excelforum.com/showthread...hreadid=472083

VBA to run queries in 5 different worksheets
 

thanks so much for the reply.

I have a couple of questions:

1) each sheet has a query written in MSQuery.
They are not graphical, as it could not display them graphically.
the WHERE clause on all queries has this key line:

AND x.DSTI_PDS_PROJMANAGER = <manager name goes here

I can't use a parameter ( ? symbol) as it says they cant be used on
queries that cant be displayed graphically.

previoulsy I tried building the SQL using strings:

For Each rn In .Range(.[A2], .[A2].End(xlDown))
sConn =
"ODBC;DSN=NIKULIVE;UID=arsuser;PWD=arsrecon1;SERVE R=nikulive.dstintl.com;"
sSQL = "SELECT DSTI_PROJ_REF, DSTI_PDS_PROJMANAGER "
sSQL = sSQL & "FROM niku.ODF_CA_PROJECT "
sSQL = sSQL & "WHERE DSTI_PDS_PROJMANAGER = '" & rn.Value &
"'"

With Sheet4.QueryTables(1)
..Connection = sConn
..CommandText = sSQL
..Refresh BackgroundQuery:=False
End With
Next


I had problems getting this working as Sheet4 that I was testing on did
not have a query in there previoulsy. Someone suggested that I have to
ADD a Qurty table to it first, but I'm not sure how to do this.
I prefer your method - but this seems to be a problem without being
able to create parameters..

any ideas?


--
matpj
------------------------------------------------------------------------
matpj's Profile: http://www.excelforum.com/member.php...o&userid=21076
View this thread: http://www.excelforum.com/showthread...hreadid=472083