'=================================================================================================================
' SMSReport2XL.vbs  (rename to .VBS if it's saved as .TXT)
'
'      Author:    John W. Nelson (Number2) http://www.number2blog.com
' Description:    This script takes 7 arguments (SMS report URL, an output fileName, the font & size for the header cells,
'                 the font and size for the body cells and a TRUE/FALSE flag to set the header bold or not)
'                 It will execute the said SMS report (and act as if you clicked the EXPORT  button on the top of 
'                 the report) and dump a the report data into a mildly formatted excel spreadsheet.  
'                 It is intended to facilitate the automatic running/exporting of reports using the Windows task 
'                 scheduler.
'              
' Requirements:   Ability to run vbscript using CSCRIPT
'                 Access to "Microsoft.XmlHttp" which I believe is part of MSXML and thus IE.
'                 Access to "ADODB.Stream" (for saving the http responseBody properly)
'                 Excel installed (for formatting and saving as actual spreadsheet, not just CSV)
'                 An account with rights to run SMS Web reports
'                 Ability to write the output file to the machine running this script.
'               
'   Disclaimer:   As always, this script is released as-is with no warranty for any particular purpose.
'                 Test the script before attempting to run on any production systems.  It has only been tested
'                 on my personal workstation (Server 2003 x64) using the web reports on an SMS 2003 SP3 server.
'                 I'm sure there are some kinds of limitations related to report response size.  I've only ever
'                 tried with reports that return up to 1.6MB of data.  Any limitation would be in the recieve
'                 buffer limit of "Microsoft.XmlHttp" (whos limitations I can't find documented anywhere)
'=================================================================================================================



'=================================================================================================================
' getTempFileName(strPrefix, strSuffix)
'
' DESCRIPTION: generates a random temporary filename with passed-in prefix and suffix added on
'
' Syntax: getTempFileName("~NO2",".csv")
Function getTempFileName(strPrefix, strSuffix)
   Set objFSO = CreateObject("Scripting.FileSystemObject")
   Set tmpFolder = objFSO.GetSpecialFolder(2)
   getTempFileName = tmpFolder & "\" & strPrefix & objFSO.GetTempName & strSuffix
   Set tmpFolder = Nothing
   Set objFSO = Nothing
End Function
'=================================================================================================================


'=================================================================================================================
' fileDelete(strFileName)
'
' DESCRIPTION: Deletes specified file, ignores any errors
'
' Syntax: fileDelete("c:\file.txt")
Function fileDelete(strFileName)
   On Error Resume Next 
   Set objFSO = CreateObject("Scripting.FileSystemObject")
   objFSO.DeleteFile strFileName
   Set objFSO = Nothing
End Function
'=================================================================================================================


'=================================================================================================================
' exportSMSReport(strReportURL,strOutputFile,strHdrFont,strHdrFontSize,strBodyFont,strBodyFontSize,boolHdrBoldTrueFalse)
'
' DESCRIPTION: exports an SMS report to an Excel spreadsheet
'    REQUIRES: getTempFileName() function
'              fileDelete() function
'
' Syntax: exportSMSReport("http://reporturl?param=value","c:\OutFile.xls","Arial",12,"Arial",8,True)
Function exportSMSReport(strReportURL,strOutputFile,strHdrFont,strHdrFontSize,strBodyFont,strBodyFontSize,boolHdrBoldTrueFalse)
   'On Error Resume Next
   
   Set WshShell = WScript.CreateObject("WScript.Shell")

   'setup an HTTP POST to the report URL
   Set http = CreateObject("Microsoft.XmlHttp")
   http.open "Post", strReportURL, False   
   http.setRequestHeader "Content-Type", "application/x-www-form-urlencoded" 'required header

   'fake SMS into thinking EXPORT has been clicked. The following line initiates the POST
   http.send "export=yes"    

   'generate temp filename for holding CSV output from report
   strTempFile = getTempFileName("~",".csv")
   
   '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
   
   '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   
   
   'delete intermediary CSV file
   fileDelete(strTempFile)
   
   Set WshShell = Nothing   
End Function
'=================================================================================================================

If (WScript.Arguments.Count = 7) Then
   strReportURL= WScript.Arguments(0)           'example  "http://SMSSERVER/SMSReporting_XXX/Report.asp?ReportID=382&param1=abc" 
   strOutFile = WScript.Arguments(1)            'example: "c:\output.xls"
   strHdrFont = WScript.Arguments(2)            'example: "Arial"
   strHdrFontSize = WScript.Arguments(3)        'example: 12
   strBodyFont = WScript.Arguments(4)           'example: "Courier New"
   strBodyFontSize = WScript.Arguments(5)       'example: 8
   strHdrBoldTrueFalse = WScript.Arguments(6)   'example: True
   exportSMSReport strReportURL,strOutFile,strHdrFont,strHdrFontSize,strBodyFont,strBodyFontSize,strHdrBoldTrueFalse
Else
    WScript.Echo "SYNTAX:  CSCRIPT ExportSMSReport.vbs ""reportURL"" ""OutputFile.xls"" ""HeaderFont"" ""HeaderFontSize"" ""BodyFont"" ""BodyFontSize"" ""HeaderBoldTrueFalse"" "
End If