Excel Class Library For Automation

Whenever our script fails in middle of our execution. we need to kill excel process from the task manager. So to get rid of this issue, we can use following code. The following code does all Excel operation with kill process errors.

Just copy this code and save into a vbs/qfl/dll File and add it your library Files.

Class ExcelDocument

Dim objExcel, objWorkbook, sheetcount
'--- Does necessary initialisation
Private Sub Class_Initialize
set objExcel= CreateObject("Excel.Application")
sheetcount=1
End Sub

'--- Destroys the excel object once the class is destroyed
Private Sub Class_Terminate
sheetcount=0
If Not IsEmpty(objWorkbook) Then
objExcel.Workbooks(1).Save
objWorkbook.Close
End If

objExcel.Quit
set objExcel= Nothing
End Sub


'--- Creates an excel object
Public Function CreateExcel(path_of_file)
Set objWorkbook = objExcel.Workbooks.Add()
objWorkbook.SaveAs(path_of_file)
End Function


'--- open an excel sheet
Public Function OpenExcel(path_of_file)
Set objWorkbook =objExcel.WorkBooks.Open(path_of_file)
End Function


'--- set visible status of an excel
Public Function VisibleState(state)
objExcel.Visible=state
End Function


'--- set visible status of an excel
Public Function AddSheet(sheetName)
objExcel.ActiveWorkbook.Worksheets.Add
objExcel.Sheets("Sheet"&sheetcount).Name=sheetName
objWorkbook.Save
sheetcount=sheetcount+1
End Function


'--- returns the excel object (better not to use until critical)
Public Function ReturnObject(sheetName)
ReturnObject=objWorkbook
End Function


'--- Inserts a "str" value at x,y pos
Public Function InsertAtCell(x,y,str)
objExcel.ActiveWorkbook.Activesheet.cells(x,y)=str
objWorkbook.Save
End Function


'--- insert "str" value in a sheet at x,y position in the current excel
Public Function InsertInSheet(sheetName,x,y,str)
objExcel.Sheets(sheetName).Activate
objExcel.ActiveWorkbook.Activesheet.cells(x,y)=str
objWorkbook.Save
End Function


'--- get value at cell x,y
Public Function ValueAtCell(x,y)
'       Msgbox x &"  "& y
' msgbox objExcel.ActiveWorkbook.Activesheet.cells(x,y)
ValueAtCell=objExcel.ActiveWorkbook.Activesheet.cells(x,y)
End Function


'--- Activate a sheet
Public Function ActivateSheet(sheetName)
objExcel.Sheets(sheetName).Activate
End Function


'--- Set the colour of a cell
Public Function SetCellColour(x,y,color)
objExcel.ActiveWorkbook.Activesheet.cells(x,y).Interior.ColorIndex=color
objWorkbook.Save
End Function


'--- set colour of a column
Public Function SetColumnColour(y,color)
objExcel.ActiveWorkbook.ActiveSheet.Range(y&"1",y&"65536").Interior.ColorIndex=color
objWorkbook.Save
End Function

'--- set colour for a row
Public Function SetRowcolour(y,color)
objExcel.ActiveWorkbook.ActiveSheet.Range("A"&y,"IV"&y).Interior.ColorIndex=color
objWorkbook.Save
End Function

'--- get the used rows count
Public Function UsedRows(sheetName)
UsedRows=objExcel.ActiveWorkbook.Activesheet.UsedRange.Rows.Count
End Function

'--- get used columns count
Public Function UsedColumns(sheetName)
UsedColumns=objExcel.ActiveWorkbook.Activesheet.UsedRange.Columns.Count
End Function

'--- set cell text colour
Public Function CellTextColour(x,y,color)
objExcel.ActiveWorkbook.Activesheet.cells(x,y).Font.ColorIndex=color
objWorkbook.Save
End Function

'--- set column text colour
Public Function ColumnTextColour(y,color)
objExcel.ActiveWorkbook.ActiveSheet.Range(y&"1",y&"65536").Font.ColorIndex=color
objWorkbook.Save
End Function

'--- set text colour of a row
Public Function RowTextColour(y,color)
objExcel.ActiveWorkbook.ActiveSheet.Range("A"&y,"IV"&y).Font.ColorIndex=color
objWorkbook.Save
End Function


End Class