Excel Function

' FUNCTION NAME: fnCreateExcel()
' PURPOSE:This function will return a new Excel Object with a default new Workbook
' INPUTS: None
' OUTPUTS: Newly created excel sheet

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
public Function fnCreateExcel()
On error resume next

Dim excelSheet 'Excel.worksheet

Set objExcelApp = CreateObject("Excel.Application") 'Create a new excel Object
objExcelApp.Workbooks.Add
objExcelApp.Visible = True
Set CreateExcel = objExcelApp

If err.number<>0 Then
strFuncProcName=" fnCreateExcel"
Call prErrorHandler(environment.Value("TestName"),strFuncProcName,err.number,err.description,err.Source,environment.Value("LocalHostName"))
err.clear
End If
End Function

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' FUNCTION NAME: prCloseExcel(objExcelApp)
' PURPOSE:This procedure will close the given Excel Object
' INPUTS: 1' Instance of Excel application.
' OUTPUTS: None

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

public Sub prCloseExcel(ExcelApp)
On error resume next

Set excelSheet = ExcelApp.ActiveSheet
Set excelBook = ExcelApp.ActiveWorkbook
Set objExcel= CreateObject("Scripting.FileSystemObject")

objExcel.CreateFolder strDataFilePath&"Temp"
objExcel.DeleteFile strDataFilePath&"ExcelExamples.xls"
excelBook.SaveAs strDataFilePath&"ExcelExamples.xls"
ExcelApp.Quit
Set ExcelApp = Nothing
Set objExcel = Nothing

If err.number<>0 Then
strFuncProcName="prCloseExcel"
Call prErrorHandler(environment.Value("TestName"),strFuncProcName,err.number,err.description,err.Source,environment.Value("LocalHostName"))
err.clear
End If
End Sub
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' FUNCTION NAME: fnSaveWorkbook(ExcelApp, workbookIdentifier, path)
' PURPOSE:The function will save a workbook according to the workbookIdentifier
' The function will overwrite the previously saved file under the given path
' Return "OK" on success and "Bad Workbook Identifier" on failure
' workbookIdentifier - The name or number of the requested workbook
' INPUTS:3'
' ExcelApp The excel object
' workbookIdentifier: the workbook/sheet name or Id
' path: The path to whihc the file has to be saved.
' OUTPUTS: The function will overwrite the previously saved file under the given path
' Return Ttrue after saving

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Function fnSaveWorkbook(ExcelApp, strworkbookIdentifier, path) 'As String
On error resume next

Dim strWorkBook 'Excel.workbook
Set strWorkBook = ExcelApp.strWorkBook(strworkbookIdentifier)

If Not strWorkBook Is Nothing Then
If path = "" Or path =strWorkBook.FullName Or path = strWorkBook.Name Then
strWorkBook.Save
Else
Set objExcel = CreateObject("Scripting.FileSystemObject")

'if the path has no file extension then add the 'xls' extension
If InStr(path, ".") = 0 Then
path = path & ".xls"
End If

objExcel.DeleteFile path
Set objExcel = Nothing

strWorkBook.SaveAs path
End If
SaveWorkbook = True
Else
SaveWorkbook = False
End If
If err.number<>0 Then
strFuncProcName=" fnSaveWorkbook"
Call prErrorHandler(environment.Value("TestName"),strFuncProcName,err.number,err.description,err.Source,environment.Value("LocalHostName"))
err.clear
End If
End Function
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' FUNCTION NAME: prSetCellValue(ExcelSheet, intRow, intColumn, strValue)

' PURPOSE:The procedure sets the given 'value' in the cell which is identified by
' its row column and parent Excel sheet
' excelSheet - the excel sheet that is the parent of the requested cell
' row - the cell's row in the excelSheet
' column - the cell's column in the excelSheet
' value - the value to be set in the cell
' INPUTS:4
' ExcelSheet,: The excel sheet to whihc the value needs to be set.
' intRow: The row to whihc the value has to be set
' intColumn : The column to whihc the value has to be set
' strValue: The value that needs to be set.
' OUTPUTS: None

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
public Sub prSetCellValue(ExcelSheet, intRow, intColumn, strValue)
On Error Resume Next

ExcelSheet.Cells(intRow, intColumn) = strValue

If err.number<>0 Then
strFuncProcName=" prSetCellValue"
Call prErrorHandler(environment.Value("TestName"),strFuncProcName,err.number,err.description,err.Source,environment.Value("LocalHostName"))
err.clear
End If
End Sub
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' FUNCTION NAME: fnGetCellValue(ExcelSheet, intRow, intColumn)
' PURPOSE:The function returns the cell's value according to its row column and sheet
' excelSheet - the Excel Sheet in which the cell exists
' row - the cell's row
' column - the cell's column
'' INPUTS:3' ExcelSheet,: The excel sheet / intRow : the row to whihc the value has to be set / intColumn: the column to which the value has to be set.
' OUTPUTS: return 0 if the cell could not be found and the value in the cell , if it can be found.

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Function fnGetCellValue(ExcelSheet, intRow, intColumn)


value = 0
Err = 0

inttempValue = ExcelSheet.Cells( intRow, intColumn)
If Err = 0 Then
value = inttempValue
Err = 0
End If
fnGetCellValue = value



End Function
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' FUNCTION NAME: fnGetSheet(ExcelApp, sheetIdentifier)
' PURPOSE:The GetSheet method returns an Excel Sheet according to the sheetIdentifier
' INPUTS:2'
' ExcelApp - the Excel application which is the parent of the requested sheet
' sheetIdentifier - the name or the number of the requested Excel sheet
' OUTPUTS: It will return name or the number of the requested Excel sheet or ' return Nothing on failure

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Function fnGetSheet(ExcelApp, sheetIdentifier) 'As Excel.worksheet


Set strGetSheet = ExcelApp.Worksheets.Item(sheetIdentifier)


End Function