INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

VBS to change query in CommandText Property

VBS to change query in CommandText Property

(OP)
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/art...



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

RE: VBS to change query in CommandText Property

hi,

You do a RefreshAll.

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

What version Excel?

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: VBS to change query in CommandText Property

(OP)
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?

RE: VBS to change query in CommandText Property

So your workbook has one querytable. On what sheet?

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: VBS to change query in CommandText Property

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

Thanks much for the help.

RE: VBS to change query in CommandText Property

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

CODE

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: VBS to change query in CommandText Property

(OP)
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.

RE: VBS to change query in CommandText Property

I was suggesting that be incorporated into your csript.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: VBS to change query in CommandText Property

(OP)
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

RE: VBS to change query in CommandText Property

CODE

'
    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 "
'somewhere above you must assign ThisID and ThisST  because they get assigned to a string here
              sSQL = sSQL & "where ID = " & ThisID & "and STATE = '" & ThisST & "' 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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: VBS to change query in CommandText Property

(OP)
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!

RE: VBS to change query in CommandText Property

(OP)
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 :)

RE: VBS to change query in CommandText Property

Glad it fit the bill.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: VBS to change query in CommandText Property

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

RE: VBS to change query in CommandText Property

(OP)
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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close