Number2 Blog - myITforum

BACKGROUND 

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.

DETAILS 

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:

1. The report URL
2. The output file (now an XLS file, not CSV)
3. The font name used in the body of the spreadsheet (the data cells)
4. The font SIZE used in the body
5. The font name used in the HEADER (first row)
6. The font SIZE used in the header
7. Whether the header row should be bold or not

SYNTAX:

CSCRIPT SMSReport2XL.vbs "reportURL" "OutputFile.xls" "HeaderFont" "HeaderFontSize" "BodyFont" "BodyFontSize" "HeaderBoldTrueFalse"

Examples:

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.open
   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
   oStream.Close

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.Workbooks.Open(strTempFile)
   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.Columns.AutoFit()
   objExcel.Rows.AutoFit()
   objExcel.ActiveWorkbook.SaveAs strOutputFile, xlWorkbookNormal
   objExcel.ActiveWorkbook.Saved=True
   objExcel.Quit
   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