In a previous post I created a VBScript in response to a discussion in the forums where somebody wanted to automatically run a report once per month and was looking for ideas. I wrote a first draft of the script and it was OK. Go check out that post first if you haven't already.
I've since updated it to handle extended characters like ® and ™ by using ADODB.Stream...but as long as I had the script open, I decided now was a good time to add XLS output (not just CSV) so we can have better formatting of the results, and add a few command-line parameters for setting the header font/size/boldness and the body font/size.
The resulting script can be found here --> SMSReport2XL.txt (save it as a .VBS)
I've added a bunch of parameters to the script so you can change the following:
- The report URL
- The output file (now an XLS file, not CSV)
- The font name used in the body of the spreadsheet (the data cells)
- The font SIZE used in the body
- The font name used in the HEADER (first row)
- The font SIZE used in the header
- Whether the header row should be bold or not
CSCRIPT SMSReport2XL.vbs "reportURL" "OutputFile.xls" "HeaderFont" "HeaderFontSize" "BodyFont" "BodyFontSize" "HeaderBoldTrueFalse"
CSCRIPT SMSReport2XL.vbs "http://reportURL" "c:\Reports\ARP.xls" "Arial"
"10" "Courier New" "8" "True"
CSCRIPT SMSReport2XL.vbs "http://reportURL" "c:\Reports\ARP.xls" "Small Fonts"
"6" "Small Fonts" "6" "False"
THE UPDATED BITS
There are essentially two major portions that I've updated to facilitate the new updates. This first part uses the ResponseBody instead of the ResponseText from the http POST operation coming from the SMS server. That has the full binary results of the operation, complete with extended characters. From there we use the ADODB.Stream object to save the response to an intermediary .CSV file
'save binary response using ADODB.Stream
Set oStream = createobject("ADODB.Stream")
oStream.type = 1 'adTypeBinary
oStream.write http.responseBody 'write the binary response from our POST above to the stream
oStream.savetofile strTempFile, 2 '2=adSaveCreateOverwrite...save http stream to a file
Now we've got the raw .CSV output, but we need to format it using Excel so we open the Excel.Application and automate the format/save process.
'open intermediary CSV file in Excel, format it and save as XLS
Set objExcel = CreateObject("Excel.Application")
Const xlWorkbookNormal = -4143
objExcel.DisplayAlerts = False 'don't prompt if file exists
objExcel.Visible = False
objExcel.Columns.Font.Name = strBodyFont
objExcel.Columns.Font.Size = strBodyFontSize
objExcel.ActiveCell.EntireRow.Font.Name = strHdrFont
objExcel.ActiveCell.EntireRow.Font.Size = strHdrFontSize
objExcel.ActiveCell.EntireRow.Font.Bold = boolHdrBoldTrueFalse
objExcel.ActiveWorkbook.SaveAs strOutputFile, xlWorkbookNormal
Set objExcel = Nothing
There are some other minor changes elsewhere but they're pretty minor and if you have any VBScript experience, pretty self-explanatory when you look at the code. These two sections above are the important changes however.
Again, the resulting script can be found here if you'd like to see it --> SMSReport2XL.txt (save it as a .VBS)
Number2 (John Nelson)
MyITForum - Forum Posts
MyITForum - Blog