Running Queries across several worksheets automatically

[t16turbo]

Hi there,

I have several worksheets in my spreadsheet i'm using.
Each one contains data returned from a query.

each query uses a resource name as the criteria, although they return different kinds of information.

I need this running for several resources each week.
What I need to be able to do is maintain a list of resource names, and then have the ability to make Excel go through the list of names, and
1. Refresh each worksheet using the current name
2. Save the spreadsheet as <current_name>.xls

is this possible?
I may be able to use a dynamic list of names, so a query will return the latest list of resources.

Pease can someone suggest how this might be done?

thanks,
Matt

 

[t16turbo]

I get a run time error (run time error 424: Object required) when I try to run the code:

Sub AutoFOB()
Dim sSQL As String
Dim sConn As String


Application.ScreenUpdating = False

'********* Refresh List of Project Managers *********'
Sheets("Managers").Select
Range("A1").Select
Selection.QueryTable.Refresh BackgroundQuery:=False

'********* Run through list of names and use in Query in next sheet *********'
Sheets("Sheet4").Select
Range("A1").Select

For Each rn In Managers.Range(Managers.[A2], Managers.[A2].End(xlDown))
    sConn = "ODBC;DSN=NIKULIVE;UID=arsuser;PWD=arsrecon1;SERVER=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



End Sub

it highlights the "For Each" line

 

[SkipVought]

Sub AutoFOB()
    Dim sSQL As String
    Dim sConn As String
    
    
    Application.ScreenUpdating = False
    
    '********* Refresh List of Project Managers *********'
    With Sheets("Managers")
        .QueryTables(1).Refresh BackgroundQuery:=False
    
    '********* Run through list of names and use in Query in next sheet *********'
        
        For Each rn In .Range(.[A2], .[A2].End(xlDown))
            sConn = "ODBC;DSN=NIKULIVE;UID=arsuser;PWD=arsrecon1;SERVER=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
    End With


End Sub

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}

 

[t16turbo]

run time error 1004.
Syntax Error

highlighting the line .Refresh BackgroundQuery:=False


Sheet4 is a blank sheet with no current query behind it at the moment, as I wanted to build up the entire VBA bit by, bit testing and understanding as I go (ir at least trying to)

I see and understand what you changed in my VBA, thanks.
can't work out why there is a syntax error.
I've commented out that line and it runs, but nothing happens!
Tried changing it to True, but I get the same errors.


sorry for taking up so much of your time.
I just need to get one small part of this working, and then the rest will be very straight forward to 'plug-in'!!

 

[SkipVought]

You have to ADD a query table in sheet4 BEFORE this code will run!

Also you need SPACES in your SQL string at end of each line

            sSQL = "SELECT DSTI_PROJ_REF, DSTI_PDS_PROJMANAGER "
            sSQL = sSQL & "FROM niku.ODF_CA_PROJECT "
            sSQL = sSQL & "WHERE DSTI_PDS_PROJMANAGER = '" & rn.Value & "'"

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}

 

[t16turbo]

to add Query tables, do you have to create a query, or can you just tell excel, "I want to put a query in there at some point"?

 

[t16turbo]

if I use this sort of thing

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

on a sheet with an existing query in it, it does not run the query I have specified in the sSQL variable.
it does nothing..

the example is in Skip's thread where he edited my code.
to start with this didn;t work, and skip suggested I need to ADD a query table to Sheet4 first.
not knowing how to do this, I used Data>Get External Data etc in EXCEL to creata query in the Sheet4.

the code then ran ok, but did nothing! It looked like it was going through every record in the "Managers" sheet, but not actually doing anything with it!!

 

[SkipVought]

If you have more than one query table on the sheet (look for multiple query table range names in the Name Box) then the QT that you want to refresh may NOT be QueryTables(1). It may be QTs(2) or QTs(3).......



Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}