VBS to change query in CommandText Property 1

[RODEOSOLSTICE]

I use XLSX files to pull data from SQL, and format for PDFs.

What I need is to update the query that is in the CommandText
The query is like this:
SELECT ID, STATE, COUNTS
FROM Database.dbo.Table1
where ID = 4332 and STATE = 'TX' and COUNTS > 0

I need VBS to change this query on attributes like ID and STATE, for example, to change it to ID = 4340 and STATE = LA:
The query is like this:
SELECT ID, STATE, COUNTS
FROM Database.dbo.Table1
where ID = 4340 and STATE = 'LA' and COUNTS > 0

I posted example code below, and if I could get VBS to update the CommandText, I could do this all dynamically, rather than having to setup a new XLSX for each state we are working on, where ID is always changing over the years.

Any help would be very much appreciated.

I found similar code in VBS, but can't find it in VBS, and I am not good enough at VBS to get there:

https://msdn.microsoft.com/en-us/vba/excel-vba/articles/querytable-commandtext-property-excel

Here is some code I use to update the XLSX with new data from SQL, and then export it to PDF
Set xlObj = CreateObject("Excel.Application")
Set fso = CreateObject("Scripting.FileSystemObject")
Set f = fso.GetFolder("..\SummaryTables")
For Each file In f.Files
set xlWB = xlObj.Workbooks.Open(file)
thisFileName =Left(xlWB.FullName , InStrRev(xlWB.FullName , ".") - 1)
xlWB.Sheets.Select
xlWB.RefreshAll
WScript.Sleep 9000 'Sleeps for 9 seconds
xlwb.save
xlWB.close True
counter = counter + 1
'WScript.Echo "File " & counter & " of " & f.Files.count & " Done"
Next
xlObj.quit

Set xlObj = CreateObject("Excel.Application")
Set fso = CreateObject("Scripting.FileSystemObject")
Set f = fso.GetFolder("..\SummaryTables")
For Each file In f.Files
set xlWB = xlObj.Workbooks.Open(file)
thisFileName =Left(xlWB.FullName , InStrRev(xlWB.FullName , ".") - 1)
xlWB.Sheets.Select
xlWB.ActiveSheet.ExportAsFixedFormat 0, thisFileName & ".pdf", 0, 1, 0,,,0
xlwb.save
xlWB.close True
counter = counter + 1
'WScript.Echo "File " & counter & " of " & f.Files.count & " Done"
Next
xlObj.quit

 

[SkipVought]

hi,

You do a RefreshAll.

How many QueryTables of this form do you have in each workbook?

What version Excel?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[RODEOSOLSTICE]

Excel 2013.

Maybe I should try another way...

I have a XLSX file named 4332_TX.xlsx with this query:
SELECT ID, STATE, COUNTS
FROM Database.dbo.Table1
where ID = 4332 and STATE = 'TX' and COUNTS > 0

I want to make a copy of that file (batch or python script to copy file), name that copy 4340_LA.xlsx, and then change the query programmatically to :
SELECT ID, STATE, COUNTS
FROM Database.dbo.Table1
where ID = 4340 and STATE = 'LA' and COUNTS > 0

Any way I could do that?

 

[SkipVought]

So your workbook has one querytable. On what sheet?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[RODEOSOLSTICE]

worksheet name = Sheet1
And just the one querytable per workbook.

Thanks much for the help.

 

[SkipVought]

I suppose you could loop thru all the sheets looking for...

Dim ws As Excel.Worksheet, lo As Excel.ListObject
Dim sSQL as String

For Each ws in wb.Worksheets
   For Each lo in ws.ListObjects
      sSQL = "SELECT ID, STATE, COUNTS "
      sSQL = sSQL & "FROM Database.dbo.Table1 "
      sSQL = sSQL & "where ID = " & ThisID & "and STATE = '" & ThisST & "' and COUNTS > 0

      With lo.QueryTable
         .CommandText = sSQL
         .Refresh False
      End With
   Next
Next

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[RODEOSOLSTICE]

I'm going to give that a try.

I was hoping to alter the querytable query from an external script, much like I do to refresh and export the PDF.

But, I could rethink to perhaps use the concept you proposed above.

Thanks very much, this one is a requirement for our projects.

 

[SkipVought]

I was suggesting that be incorporated into your csript.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[RODEOSOLSTICE]

That'd be great. Not sure I could pull it off, any chance you combine it as an example? I should add my script is a .vbs script

 

[SkipVought]

'
    Set xlObj = CreateObject("Excel.Application")
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set f = fso.GetFolder("..\SummaryTables")
    For Each file In f.Files
        Set xlWB = xlObj.Workbooks.Open(file)
        thisFileName = Left(xlWB.FullName, InStrRev(xlWB.FullName, ".") - 1)
        
        For Each ws In xlWB.Worksheets
           For Each lo In ws.ListObjects
              sSQL = "SELECT ID, STATE, COUNTS "
              sSQL = sSQL & "FROM Database.dbo.Table1 "
'[b]somewhere above you must assign ThisID and ThisST [/b] because they get assigned to a string here
              sSQL = sSQL & "where ID = " & [b]ThisID[/b] & "and STATE = '" & [b]ThisST[/b] & "' and COUNTS > 0"
        
              With lo.QueryTable
                 .CommandText = sSQL
                 .Refresh False
              End With
           Next
        Next
        
        xlWB.Save
        xlWB.Close True
        counter = counter + 1
        'WScript.Echo "File " & counter & " of " & f.Files.count & " Done"
    Next
    xlObj.Quit

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[RODEOSOLSTICE]

Much obliged Skip, very much. I couldn't find any way to send a personal message, so wanted to be sure to say thanks. I'll toy with that, it looks very promising!

 

[RODEOSOLSTICE]

Skip, YOU DA' MAN.. that's it, got it to work!!! Need to modify some for my needs, but 100% absolutely what I need!!!

I owe ya a cold one, I've been chasing down this need for a long time, and now our projects will be that much easier!!!!


Thanks you very much,

Greg

 

[SkipVought]

Glad it fit the bill.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[strongm]

In future you might be better off asking Vbscript questions in forum329

 

[RODEOSOLSTICE]

Thanks strongm, I will bookmark, and if a moderator wants to move this thread, have at it. I will enjoy knowing the vbs section is there, I couldn't find it when I joined up