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

 

[SkipVought]

Hi,

Loop thru your list and substitute the resource names in your SQL.

for each rn in [YourResourceList]
  ...
  sSQL = ...
  sSQL = sSQL & "Where ... AND [Resource]='" & rn.Value & "'..."
  ...
next

Skip,

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

 

[t16turbo]

in the VBA, i'm assuming I need to specify the ODBC Connection details etc?
I've not done this before.

also, to use the other method I mentioned, whereby I would have an additional query to return the names that I want to plug into the existing queries, how might I acheive that?

thank you for your help

 

[SkipVought]

Turn on your macro recorder

edit the query

check out the recorded code for connection string et al.

Skip,

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

 

[t16turbo]

right, I have to run before I walk.
I used the query editor.
copied the results and pasted them into a blank spreadhseet.

there was a comile error and the entire SQL was highlighted red.

then I tried using a VERY basic query that was only on one line in the VBA editor.

this worked ok - although each time it ran, it planted the results NEXT to the original results.

this is the first problem
I need it to replace/refresh the data each time it is run.

secondly, I now need to know how to automatically save the worksheet in a given location using a given name.

thirdly I will then need to understand how I can write a query to return a list of Resources, store them, then go through each one in turn and use it as a variable in my SQL to refresh the data and save the spreasheet... etc

sorry for asking a lot of questions. I use so many tools in my job, I never get the chance to focus on (and remember) one.

 

[SkipVought]

What does your recorded code look like???

"copied the results and pasted them into a blank spreadhseet."

Can't read your mind.

Exactly WHAT are we talkin about?

Skip,

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

 

[t16turbo]

my macro looks like this if I copy and paste:

Sub Macro3()
'
' Macro3 Macro
' Macro recorded 29/09/2005 by Matthew Johnson
'

'
    With Selection.QueryTable
        .Connection = _
        "ODBC;DSN=NIKULIVE;UID=arsuser;PWD=arsrecon1;SERVER=nikulive.dstintl.com;"
        .CommandText = Array( _
        "select "&chr(13)&""&chr(10)&"x.dsti_proj_ref,"&chr(13)&""&chr(10)&"p.name,"&chr(13)&""&chr(10)&"x.dsti_true_client3, "&chr(13)&""&chr(10)&"x.dsti_prod_grp, "&chr(13)&""&chr(10)&"x.dsti_product, "&chr(13)&""&chr(10)&"d.proj_type_name, "&chr(13)&""&chr(10)&"x.dsti_unapproved_cat, "&chr(13)&""&chr(10)&"x.dsti_version, "&chr(13)&""&chr(10)&"x.dsti_proj_likelihood, "&chr(13)&""&chr(10)&"x.dsti_rad_indi" _
        , _
        "cator,"&chr(13)&""&chr(10)&"sum(a.practsum/27000),"&chr(13)&""&chr(10)&"sum(a.prestsum/27000)"&chr(13)&""&chr(10)&""&chr(13)&""&chr(10)&"FROM "&chr(13)&""&chr(10)&"niku.odf_ca_project x,"&chr(13)&""&chr(10)&"niku.nbi_project_current_facts nbi,"&chr(13)&""&chr(10)&"niku.srm_projects p,"&chr(13)&""&chr(10)&"niku.dsti_project_analysis d,"&chr(13)&""&chr(10)&"niku.prtask t,"&chr(13)&""&chr(10)&"niku.p" _
        , _
        "rassignment a,"&chr(13)&""&chr(10)&"niku.prchargecode c"&chr(13)&""&chr(10)&""&chr(13)&""&chr(10)&"WHERE"&chr(13)&""&chr(10)&"d.PROJ_TYPE(+) = x.dsti_proj_type"&chr(13)&""&chr(10)&"and t.prprojectid=p.id"&chr(13)&""&chr(10)&"AND a.prtaskid = t.prid "&chr(13)&""&chr(10)&"AND c.prid = t.prchargecodeid"&chr(13)&""&chr(10)&"AND x.DSTI_PDS_PROJMANAGER = 'Stuart P" _
        , _
        "hillips'"&chr(13)&""&chr(10)&"AND x.id = nbi.project_id"&chr(13)&""&chr(10)&"AND x.id = p.id"&chr(13)&""&chr(10)&"AND p.is_active = 1"&chr(13)&""&chr(10)&"AND p.is_template = 0"&chr(13)&""&chr(10)&"AND c.prexternalid NOT IN ('LDEV5150', 'LDEV5200', 'LDEV5205', 'LDEV5215', 'LDEV5100','LDEV5320', 'LDE" _
        , _
        "V5340')"&chr(13)&""&chr(10)&"AND ETC_HOURS > 0"&chr(13)&""&chr(10)&""&chr(13)&""&chr(10)&"GROUP BY"&chr(13)&""&chr(10)&"x.dsti_proj_ref,"&chr(13)&""&chr(10)&"p.name,"&chr(13)&""&chr(10)&"x.dsti_true_client3, "&chr(13)&""&chr(10)&"x.dsti_prod_grp, "&chr(13)&""&chr(10)&"x.dsti_product, "&chr(13)&""&chr(10)&"d.proj_type_name, "&chr(13)&""&chr(10)&"x.dsti_unapproved_cat, "&chr(13)&""&chr(10)&"x.dsti_version, "&chr(13)&""&chr(10)&"x.dsti_pro" _
        ,)
        .Refresh BackgroundQuery:=False
    End With
    ActiveWindow.SmallScroll Down:=-15
End Sub

this highlights the qeury part of the code in red and displays a syntax error message.

If I create a simple query on one line, it works, and looks liek this:

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 29/09/2005 by Matthew Johnson
'

'
    With ActiveSheet.QueryTables.Add(Connection:= _
        "ODBC;DSN=NIKULIVE;UID=arsuser;PWD=arsrecon1;SERVER=nikulive.dstintl.com;", _
        Destination:=Range("A1"))
        .CommandText = Array( _
        "SELECT ODF_CA_PROJECT.DSTI_PROJ_REF, ODF_CA_PROJECT.DSTI_VERSION" & Chr(13) & "" & Chr(10) & "FROM NIKU.ODF_CA_PROJECT ODF_CA_PROJECT")
        .Name = "Query from NIKULIVE"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = True
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .Refresh BackgroundQuery:=False
    End With
    Rows("2:2").Select
    ActiveWindow.FreezePanes = True
    Range("A1").Select
    With Selection.Interior
        .ColorIndex = 6
        .Pattern = xlSolid
    End With
End Sub

alghouth each time it runs it retrieves data in the column next to the original data!

 

[arides]

i don't know if i can help but i'll try...

1. to "refresh" queries, u should only use the REFRESH method

2. if u want to change the criteria of the query, u can do this by changing the .connection property

3. if u want to change the fields that the query projects, then u should want to delete the query and recreate it, because EXCEL will put the new field as the las column, regardless where u put it.

4. if excel puts another query to the right of the original, then u might be creating a new query instead of editing the original.

5. for saving spreadsheets, i could help u with this code.

code:

Sub guardalista()

Dim nombreHoja As String, strArch As String, strdirDiario As String
Dim strDir As String, strNew As String, strNombreLista As String

nombreHoja = ActiveWorkbook.Name

strDir = the directory where u'll put it
If Right(strDir, 1) <> "\" Then
strDir = strDir + "\"
End If

strNew = strDir + Trim(cond.Marca)

strArch = name of the file. remember to put the .XLS at the end

On Error GoTo panic_strdir (very important)
ChDir strdirDiario
Sheets("Temp_Hoja").Select ' this is the sheet i'll copy
Sheets("Temp_Hoja").Copy

ActiveSheet.Name = 'name the sheet!

ActiveWorkbook.SaveAs Filename:= _
strArch, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False


ActiveWorkbook.Close
On Error GoTo 0
Workbooks(nombreHoja).Activate
Exit Sub
panic_strdir:
MsgBox ("La carpeta de salida es incorrecta o inexistente" & Chr(13) & "Por favor verifique en la hoja de Configuración")
End Sub


ps, this is an edit from the original code, so i don't know if it 'll work right away, but it's close enough

 

[SkipVought]

Each tinme that you ADD a qt it inserts.

Change you code...

Sub Macro1()
    sConn As String, sSQL As String
    
    sConn = "ODBC;DSN=NIKULIVE;UID=arsuser;PWD=arsrecon1;SERVER=nikulive.dstintl.com;"
    
    sSQL = "SELECT ODF_CA_PROJECT.DSTI_PROJ_REF, ODF_CA_PROJECT.DSTI_VERSION "
    sSQL = sSQL & "FROM NIKU.ODF_CA_PROJECT ODF_CA_PROJECT "
        
    With [b]YourSheetNameHERE[/b].QueryTables(1)
        .Connection = sConn
        .CommandText = sSQL
        .Refresh BackgroundQuery:=False
    End With
End Sub

Skip,

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

 

[SkipVought]

I mean to state

With [b]YourSheetOBJECThere[/b].QueryTables(1)

Skip,

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

 

[t16turbo]

thanks Arides,
as currently my queries are on each sheet - and not using VBA,
I guess I can just use VBA to refresh those easy enough.
thats straight forward.

If I want to change one part of the WHERE clause in each sheet's query, I will have to include the whole query in VBA? Which would then mean that before I runt he query I qould have to blank the sheet and focus on A1 before running the query..

 

[t16turbo]

thanks Skip, took so long writing my reply I hadn't read your replies.

 

[t16turbo]

Skip, when I run that it gives me an error saying:
"Compile error: Statement invlid outsiode type block"

it highlights the "Sub Macro1()" in yellow.

 

[SkipVought]

sorry

    [b]Dim[/b] sConn As String, sSQL As String

Skip,

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

 

[arides]

np.

If I want to change one part of the WHERE clause in each sheet's query, I will have to include the whole query in VBA? Which would then mean that before I runt he query I qould have to blank the sheet and focus on A1 before running the query..

u should rewrite the whole query.

basically, u could put the query into a string; and even use the ARRAY() if it's too large. (although frankly i cannot fully understand how that one works)

BUT, as Skip says, u don't need to recreate the query, u can use shtobject.querytables(1) or, if it fits ur needs better,

with Sht.Range("a1").querytable
.commandtext = smtg smtg
.refresh
end with

this is a little easier to identify, although if u want to run a process through a series of QT in the worksheet Skip's method is better.



the only time u should need to blank and recreate the query is if u make a change in the fields that the query will show (aka columns); quick example:

original Query: select customer_id, customer_tel from clients

shows: (lol)

customer_id customer_tel


new query: select customer_id, customer_name, customer,tel from clientes

if u just change the existing query, it will show:

customer_id customer_tel customer_name

now, if u don't care about that, there is no problem. BUT if u want it to be shown like

customer_id customer_name customer_tel


THEN u ll need to recreate the query

t - and not using VBA,

btw, it's very likely the REFRESH method is also available using automation, but i couldn't tell for sure

 

[t16turbo]

thanks np.
thats very helpful.

that should help me with the majority of the work I need to do.

the only bit I'm not unsure of is the 'loop' that I need to create that will go through the query for each project manager ni the list returned by a seperate query.

e.g.
QUERY to retrieve a list of project managers

SELECT PROJECT_MANAGER FROM PROJECTS

then for each PROJECT_MANAGER, set a variable with the name
and run through each of the SQL queries using that variable in the WHERE clause.

how do I get Excel to store the results of the first query in order to go through it?
Or is it easier to use a hidden sheet with the manager query on it, and then go through each row in turn and set the variable?

 

[SkipVought]

You return the data to a sheet (you are using MS Query aren't you?) and then loop thru that range.

for each rn in ThatSheet.Range(ThatSheet.[A2], ThatSheet.[A2].End(xldown))
  ...
  sSQL = ...
  sSQL = sSQL & "Where ... AND [Resource]='" & rn.Value & "'..."
  ...
next

Skip,

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

 

[t16turbo]

Perfect!

here we go!!

thanks guys

 

[t16turbo]

Skip,

what is the 'rn' in your FOR loop?
is it the column name (contents of cell A1)?
or is it the SQL field name?

for some reason I keep writing this reply but it never registers!

 

[SkipVought]

rn is a RANGE OBJECT of ONE CELL. rn has all the properties of a range.

Hence rn.Value to return the VALUE of that particular cell.

Skip,

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