'(Declarations)
'    Available Objects:
'        BaseObjectEngine    -  PCS Base Object Engine
'        LookupObjectEngine  -  PCS Lookup Table Engine
'        WorkflowData        -  PCS Workflow Data Engine
'        MetaObjectEngine    -  PCS Def Object Engine
'        DB                  -  PCS Database Engine
'        DataItem            -  PCS Workflow DataItem
'        ScriptInfoObject    -  PCS Workflow Script Parameter Collection
'
'End of (Declarations)

Const vbGeneralDate = 0
Const vbLongDate = 1
Const vbShortDate = 2
Const vbLongTime = 3
Const vbShortTime = 4

Public Function WorkflowScript()

'==============================================================
' On Error Resume Next is the only form of error handling in
' VBScript.  If an error is raised out of a script while it
' is running, it may cause the workflow system to slow.
' Therefore, please use On Error Resume Next in conjunction
' with the Err Object to handle any errors
' that may occur.
'==============================================================
    
    Dim TodaysDate
    Dim tempSheetName
    
    On Error Resume Next
    
    'Get the date and time
    TodaysDate = FormatDateTime(Now, vbGeneralDate)
    'Replace the colons in the Date/Time with underscores
    TodaysDate = Replace(TodaysDate, Chr(58), "_")
    
    'Create the name of the Excel File, This assumes a path passed in as a parameter,
    'The company name pass in as a DataItem and using the Data and Time to ensure that
    'the resulting file has a unique filename.
    Set DataItem = WorkflowData.GetItemEx("[CompanyName]")
    tempSheetName = ScriptInfoObject("[FilePath]").ParameterValue & "\" & DataItem.Value & "_" & TodaysDate & ".xls"
    Set DataItem = Nothing
    
    'Save the name of the file into a DataItem so that it can be retrieved by other tasks
    'within the workflow
    Set DataItem = WorkflowData.GetItemEx("[SpreadSheetName]")
    DataItem.Value = tempSheetName
    Set DataItem = Nothing
    
    WorkflowData.SaveData
    
    WriteSpreadsheetNamed ScriptInfoObject.Item("[ExcelTemplate]").ParameterValue, tempSheetName
    
'===============================================
' The following values are valid return codes
'-----------------------------------------------
'  0     = Success - (Default)
' -1     = Failure - Mark task as failed
' -2     = Failure - Mark task as failed and Raise WithException Flag
' -3     = Failure - Mark task as failed and Rollback workflow
'===============================================

    WorkflowScript = 0    '(Default) Success
End Function

'Access an Excel Spreadsheet using named cells, load values into the
'named cells and then save the spreadsheet
Public Function WriteSpreadsheetNamed(ByVal Template, ByVal OutputFile)
    Dim objExcel
    
    Set objExcel = Nothing
    Set objExcel = CreateObject("Excel.Application")
    
    'Make Excel Visible, it should still work if this is set to false
    objExcel.Visible = True
    
    'Load the Excel Spreadsheet
    objExcel.Workbooks.Add Template
    
    'Activate the first Worksheet
    objExcel.Worksheets("Input").Activate
    
    'Load the relevant Dataitem values into the appropriate named cells
    'This assumes that you have Dataitems named Value1,2,3, etc. and
    'that there are named cells in the spreadsheet that are named after
    'the XML tag of the matching DataItems.
    Set DataItem = WorkflowData.GetItemEx("[Value1]")
    objExcel.Range(DataItem.AttribXMLTag).Cells(1, 1) = DataItem.Value
    Set DataItem = Nothing
    
    Set DataItem = WorkflowData.GetItemEx("[Value2]")
    objExcel.Range(DataItem.AttribXMLTag).Cells(1, 1) = DataItem.Value
    Set DataItem = Nothing
    
    Set DataItem = WorkflowData.GetItemEx("[Value3]")
    objExcel.Range(DataItem.AttribXMLTag).Cells(1, 1) = DataItem.Value
    Set DataItem = Nothing
    
    Set DataItem = WorkflowData.GetItemEx("[Value4]")
    objExcel.Range(DataItem.AttribXMLTag).Cells(1, 1) = DataItem.Value
    Set DataItem = Nothing

    'Set the formula you want to calculate
    Set DataItem = WorkflowData.GetItemEx("[Answer]")
    objExcel.Range(DataItem.AttribXMLTag).Cells(1, 1).Formula = "=Value1+Value2+Value3+Value4"
    Set DataItem = Nothing

    'Save the Spreadsheet
    objExcel.Workbooks(1).SaveAs OutputFile
    
    'Quit Excel
    objExcel.Quit

End Function

'Access an Excel Spreadsheet using cell references (row, column),
'load values into the named cells and then save the spreadsheet
Public Function WriteSpreadsheetCellRef(ByVal Template, ByVal OutputFile)
    Dim objExcel
    
    Set objExcel = Nothing
    Set objExcel = CreateObject("Excel.Application")
    
    'Make Excel Visible, it should still work if this is set to false
    objExcel.Visible = True
    
    'Load the Excel Spreadsheet
    objExcel.Workbooks.Add Template
    
    'Activate the first Worksheet, with the name of "Input"
    objExcel.Worksheets("Input").Activate
    
    'Load the relevant Dataitem values into the referenced cells
    Set DataItem = WorkflowData.GetItemEx("[Value1]")
    objExcel.Cells(1, 1) = DataItem.Value
    Set DataItem = Nothing
    
    Set DataItem = WorkflowData.GetItemEx("[Value2]")
    objExcel.Cells(2, 1) = DataItem.Value
    Set DataItem = Nothing
    
    Set DataItem = WorkflowData.GetItemEx("[Value3]")
    objExcel.Cells(3, 1) = DataItem.Value
    Set DataItem = Nothing
    
    'Switch to the Second Worksheet
    objExcel.Worksheets("Output").Activate
    Set DataItem = WorkflowData.GetItemEx("[Value4]")
    objExcel.Cells(1, 1) = DataItem.Value
    Set DataItem = Nothing

    'Set the formula you want to calculate
    'Since the first 3 values are on the 1st Worksheet-"Input", must reference the
    'Worksheet, Value 4 is in the first cell of the current Worksheet
    objExcel.Cells(2, 1).Formula = "=Input!A1+Input!A2+Input!A3+A1"


    'Save the Spreadsheet
    objExcel.Workbooks(1).SaveAs OutputFile
    
    'Quit Excel
    objExcel.Quit

End Function

Public Function ReadSpreadsheet(ByVal Template, ByVal OutputFile)
    Dim objExcel
    Dim TodaysDate
    Dim tempSheet
    Dim fName
    Dim SubCol
    Dim DataItem
    
    Set objExcel = Nothing
    Set objExcel = CreateObject("Excel.Application")
    
    'Make Excel Visible, it should still work if this is set to false
    objExcel.Visible = True
    
    'Load the Excel Spreadsheet
    objExcel.Workbooks.Add Template
    
    'Activate the first Worksheet
    objExcel.Worksheets(1).Activate
    
    'Load the relevant Dataitem values into the appropriate named cells
    Set DataItem = WorkflowData.GetItemEx("[CompanyName]")
    objExcel.Range(DataItem.AttribXMLTag).Cells(1, 1).Value = DataItem.Value
    Set DataItem = Nothing
    
    Set DataItem = WorkflowData.GetItemEx("[Value1]")
    objExcel.Range(DataItem.AttribXMLTag).Cells(1, 1).Value = DataItem.Value
    Set DataItem = Nothing
    
    Set DataItem = WorkflowData.GetItemEx("[Value2]")
    objExcel.Range(DataItem.AttribXMLTag).Cells(1, 1).Value = DataItem.Value
    Set DataItem = Nothing
    
    Set DataItem = WorkflowData.GetItemEx("[Value3]")
    objExcel.Range(DataItem.AttribXMLTag).Cells(1, 1).Value = DataItem.Value
    Set DataItem = Nothing
    
    Set DataItem = WorkflowData.GetItemEx("[Value4]")
    objExcel.Range(DataItem.AttribXMLTag).Cells(1, 1).Value = DataItem.Value
    Set DataItem = Nothing

    'Set the formula you want to calculate
    Set DataItem = WorkflowData.GetItemEx("[Answer]")
    objExcel.Range(DataItem.AttribXMLTag).Cells(1, 1).Formula = "=Value1+Value2+Value3+Value4"
    DataItem.Value = objExcel.Range(DataItem.AttribXMLTag).Cells(1, 1).Value
    Set DataItem = Nothing

    'Take the value calculated for "Answer" and save back into the FlowData Table
    WorkflowData.SaveData
    
    'Save the Spreadsheet
    objExcel.Workbooks(1).SaveAs OutputFile
    
    'Quit Excel
    objExcel.Quit

End Function

Public Function PrintWorksheet(ByVal File)
    Dim objExcel
    
    Set objExcel = Nothing
    Set objExcel = CreateObject("Excel.Application")
    
    'Make Excel Visible, it should still work if this is set to false
    objExcel.Visible = True
    
    'Load the Excel Spreadsheet
    objExcel.Workbooks.Add File
    
    'Activate the first Worksheet
    objExcel.Worksheets(1).Activate
    
    'Print the worksheet
    'Use the options below to print only certain pages or multiple copies
    'objExcel.ActiveWorkbook.PrintOut [From], [To], [Copies], [Preview], [ActivePrinter], [PrintToFile], [Collate], [PrToFileName]
    
    'This will print the entire current workbook
    objExcel.ActiveWorkbook.PrintOut
    
    objExcel.quit

End Function