RODEOSOLSTICE
Programmer
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:
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
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:
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