Excel Functions

1. This function will return a new Excel Object with a default new oWorkBook



Function xlsCreateExcelObject() 'As Excel.Application

    Set oExcel = CreateObject("Excel.Application") 'Create a new excel Object

    oExcel.Workbooks.Add

    oExcel.Visible = True

    Set xlsCreateExcelObject = oExcel

End Function

2. The OpenWorkbook method opens a previously saved Excel oWorkBook and adds it to the Application

'ARG(s):

  'sPath - the path of the oWorkBook that will be opened

  'Note - returns Nothing on failure



Function xlsOpenExcel(oExcel, sPath)

    Set oBook = oExcel.Workbooks.Open(sPath)

    Set xlsOpenBook = oBook

End Function

3. This function deletes a specified range of rows

'ARG(s):

  'oExcel - Reference to the Excel object

  'sStartRow - Starting row to start deleting from

  'sEndRow - last row to delete.

  'Note - Deletes everything inbetween.



Public Function xlsDeleteRowRng(oExcel, sStartRow, sEndRow)

    'Delete rows above headers

    'oSheet.Rows("1:4").Delete

    oSheet.Rows(sStartRow +":"+ sEndRow).Delete

End Function

4. This function saves Excel with Save As

'ARG(s):

  'oExcel - Reference to the Excel object

  'sPath - Full path, including file name to save to

  'Note - Automatically overwrites files with matching names



Public Function xlsSaveAs(oExcel, sPath)

    'Save excel

    oExcel.DisplayAlerts = False

    oExcel.ActiveWorkbook.SaveAs (sPath)

    oExcel.DisplayAlerts = True

End Function

5. This function closes Excel and ignores any messages - does not save

'ARG(s):

  'oExcel - Reference to the Excel object

  'Note - closes app without saving



Public Function xlsCloseExcel(oExcel)

    'Close Excel

    oExcel.DisplayAlerts = False	

    oExcel.Quit

    oExcel.DisplayAlerts = True	

End Function

6. Inserts & names a new DataSheet to an Excel file

'ARG(s):

  'sFilePath - full path to xls file

  'sFileName - file name complete with extention

  'sSheetName - name to give the new worksheet



Public Function xlsAddSheet(sFilePath, sFileName, sSheetName)

    'Set the excel object

    Set oExcel = CreateObject("Excel.Application")

    ' Open Excel ...

    oExcel.Workbooks.Open(sFilePath)

    'Specify Excel book by name (Test2.xls)

    Set oThisExcel = oExcel.Workbooks(sFileName)

    Set oSheetCol = oThisExcel.Sheets.Add

    Set oSheetCol = oThisExcel.Sheets

    'Always addes to front of sheet list by default - Item(1)

    'Rename sheet

    oSheetCol.Item(1).Name = sSheetName

    oThisExcel.Save ' use this one to build the file sheet by sheet

    'oThisExcel.SaveAs (sFilePath2) ' generate backup file

    'Close the xls file

    oExcel.Workbooks.Close()

End Function

7. This function will close the given Excel Object

'ARG(s):

  'sSaveAsPath - Full UNC to the Dest Folder

  'sFileName - Name of the XLS file



Public Function xlsSaveAsClose(sSaveAsPath, sFileName)

    Set oExcel = xlsCreateExcelObject

    'Specify Excel book by name (Test2.xls)

    Set oThisExcel = oExcel.Workbooks(sFileName)

    'Save Excle file

    oThisExcel.SaveAs (sSaveAsPath)

    'Close the xls file

    oExcel.Workbooks.Close()

End Function

8. The SaveWorkbook method will save a oWorkBook according to the sBookName

'ARG(s):

  'sBookName - The name or number of the requested oWorkBook

  'sXLSPath - the location to which the oWorkBook should be saved

  'Return "PASS" on success and "Bad oWorkBook Identifier" on failure



Public Function xlsSaveBook(sBookName, sXLSPath)

    Set oExcel = xlsCreateExcelObject()

    Set oWorkBook = oExcel.Workbooks(sBookName)

    If oWorkBook <> Nothing Then

     If sXLSPath = "" OR sXLSPath = oWorkBook.FullName OR sXLSPath = oWorkBook.Name Then

      oWorkBook.Save

     Else

      Set oFileSys = CreateObject("Scripting.FileSystemObject")

      oFileSys.DeleteFile sXLSPath

      Set oFileSys = Nothing

      oWorkBook.SaveAs sXLSPath

     End If

    xlsSaveBook = "PASS"

    Else

     xlsSaveBook = "Invalid oWorkBook Name"

    End If

End Function

9. The SetCellValue method sets the given 'sValue' in the cell which is identified by 'its iRow iCol and parent Excel sheet

'ARG(s):

  'oDataSheet - the excel sheet that is the parent of the requested cell

  'iRow - the cell's iRow in the oDataSheet

  'iCol - the cell's iCol in the oDataSheet

  'sValue - the sValue to be set in the cell



Public Function xlsSetCellValue(oDataSheet, iRow, iCol, sValue)

    Set oDataSheet = oDataSheet

    oDataSheet.Cells(iRow, iCol) = sValue

End Function

10. The GetCellValue returns the cell's sValue according to its iRow iCol and sheet

'ARG(s):

  'oDataSheet - the Excel Sheet in which the cell exists

  'iRow - the cell's iRow

  'iCol - the cell's iCol

  'return 0 if the cell could not be found



Public Function xlsGetCellValue(oDataSheet, iRow, iCol)

    Set oDataSheet = oDataSheet

    xlsGetCellValue = oDataSheet.Cells(iRow, iCol)

End Function

11. The GetSheet method returns an Excel Sheet according to the sheetIdentifier

'ARG(s):

  'sheetIdentifier - the name or the number of the requested Excel sheet

  'return Nothing on failure



Public Function xlsGetSheet(sDataSheet) 'As Excel.worksheet

    Set oExcel = xlsCreateExcelObject()

    Set xlsGetSheet = oExcel.Worksheets.Item(sDataSheet)

End Function

12. The InsertNewWorksheet method inserts an new worksheet into the active oWorkBook or 'the oWorkBook identified by the sBookName,

       the new worksheet will get a default 'name if the sDataSheet parameter is empty, otherwise the sheet will have the sDataSheet 'as a name.

'ARG(s):

  'sBookName - an optional identifier of the worksheet into which the new worksheet should be added

  'sDataSheet - the optional name of the new worksheet.

  'Return - the new sheet as an Object



Public Function xlsAddSheet(sBookName, sDataSheet)

    Set oExcel = xlsCreateExcelObject()

    'In case that the sBookName is empty we will work on the active oWorkBook

    If sBookName = "" Then

      Set oWorkBook = oExcel.ActiveWorkbook

    Else

      Set oWorkBook = oExcel.Workbooks(sBookName)

    End If

    'Get sheet count

    iSheetCount = oWorkBook.Sheets.Count

    'Add new datasheet

    oWorkBook.Sheets.Add , iSheetCount

    'Get new datasheet object

    Set oDataSheet = oWorkBook.Sheets(iSheetCount + 1)

    'In case that the sDataSheet is not empty set the new sheet's name to sDataSheet

    If sDataSheet <> "" Then

      oDataSheet.Name = sDataSheet

    End If

    'Return new datasheet

    Set xlsAddSheet = oDataSheet

End Function

13. The RenameWorksheet method renames a worksheet's name

'ARG(s):

  'sBookName - the worksheet's parent oWorkBook identifier

  'worksheetIdentifier - the worksheet's identifier

  'sDataSheet - the new name for the worksheet



Public Function xlsRenameSheet(sBookName, sOldName, sNewName)

    Set oExcel = xlsCreateExcelObject()

    On Error Resume Next

    Err = 0

    Set oWorkBook = oExcel.Workbooks(sBookName)

    If Err <> 0 Then

      xlsRenameSheet = "Bad WorkBook Identifier"

      Err = 0

      Exit Function

    End If

    Set oDataSheet = oWorkBook.Sheets(sOldName)

    If Err <> 0 Then

      xlsRenameSheet = "Bad WorkSheet Identifier"

      Err = 0

      Exit Function

    End If

    oDataSheet.Name = sNewName

    RenameWorksheet = "OK"

End Function

14. The RemoveWorksheet method removes a worksheet from a oWorkBook

'ARG(s):

  'sBookName - the worksheet's parent oWorkBook identifier

  'worksheetIdentifier - the worksheet's identifier



Public Function xlsDeleteSheet(sBookName, sDataSheet)

    Set oExcel = xlsCreateExcelObject()

    On Error Resume Next

    Err = 0

    Set oWorkBook = oExcel.Workbooks(sBookName)

    If Err <> 0 Then

      xlsDeleteSheet = "Bad WorkBook Identifier"

      Exit Function

    End If

    Set oDataSheet = oWorkBook.Sheets(sDataSheet)

    If Err <> 0 Then

      xlsDeleteSheet = "Bad DataSheet Identifier"

      Exit Function

    End If

    oDataSheet.Delete

    xlsDeleteSheet = "OK"

End Function

15. The CreateNewWorkbook method creates a new WorkBook in the excel application



Public Function xlsCreateBook()

    Set oExcel = xlsCreateExcelObject()

    Set oBook = oExcel.Workbooks.Add()

    Set xlsCreateBook = oBook

End Function

16. The ActivateWorkbook method sets one of the workbooks in the application as Active oWorkBook

'ARG(s):

  'sBookName - the name or the number of the oWorkBook



Public Function xlsSetBookActive(sBookName)

    Set oExcel = xlsCreateExcelObject()

    oExcel.Workbooks(sBookName).Activate

End Function

17. The CloseWorkbook method closes an open oWorkBook

'ARG(s):

  'sBookName - the name or the number of the oWorkBook



Public Function xlsCloseBook(sBookName)

    Set oExcel = xlsCreateExcelObject()

    oExcel.Workbooks(sBookName).Close

End Function

18. The CompareSheets method compares between two sheets. 'if there is a difference between the two sheets then the sValue in the second sheet 'will be changed to red and contain the string: '"Compare conflict - sValue was 'Value2', Expected sValue is 'value2'"

'ARG(s):

  'sheet1, sheet2 - the excel sheets to be compared

  'startColumn - the iCol to start comparing in the two sheets

  'numberOfColumns - the number of columns to be compared

  'startRow - the iRow to start comparing in the two sheets

  'numberOfRows - the number of rows to be compared



Public Function xlsCompareSheets(sheet1, sheet2, startColumn, numberOfColumns, startRow, numberOfRows, trimed) 'As Boolean

    Dim returnVal 'As Boolean

    returnVal = True

    'In case that one of the sheets doesn't exists, don't continue the process

    If sheet1 Is Nothing Or sheet2 Is Nothing Then

     CompareSheets = False

     Exit Function

    End If

    'loop through the table and fill values into the two worksheets

    For r = startRow to (startRow + (numberOfRows - 1))

     For c = startColumn to (startColumn + (numberOfColumns - 1))

      Value1 = sheet1.Cells(r, c)

      Value2 = sheet2.Cells(r, c)

      'if 'trimed' equels True then used would like to ignore blank spaces

      If trimed Then

        Value1 = Trim(Value1)

        Value2 = Trim(Value2)

      End If

      'in case that the values of a cell are not equel in the two worksheets

      'create an indicator that the values are not equel and set return sValue

      'to False

      If Value1 <> Value2 Then

        Dim cell 'As Excel.Range

        sheet2.Cells(r, c) = "Compare conflict - sValue was '" & Value2 & "', Expected sValue is '" & Value1 & "'."

        Set cell = sheet2.Cells(r, c)

        cell.Font.Color = vbRed

        returnVal = False

      End If

     Next

    Next

xlsCompareSheets = returnVal

End Function

18. This function will return a new Excel Object with a default new oWorkBook

'ARG(s):

  'sSourcePath - file to open

  'sDestPath' - file to save to

  'sMergeType -

    '"all" will UnMerge all cells in book,

    'if blank, function takes a 'range of cells'

  'sRange -

    'ignored if "all" is selected for sMergeType arg

    'Else place a cell range l;ike: "B2,E2" or "B2,E4"



Public Function xlsUnMergeCells(sSourcePath, sDestPath, sMergeType, sRange)

    'Create Excel object

    Set oExcel = CreateObject("Excel.Application")

    'Open Book in Excel

    Set oBook = oExcel.Workbooks.Open(sSourcePath)

    'Set Activesheet

    Set oSheet = oExcel.Activesheet

    'Choose whether to ...

        'UnMerge all cells

        'UnMerge cell range

    Select Case LCase(sMergeType)

    Case "all"

        'Select a range of cells to UnMerge

        oSheet.Range(sRange).UnMerge

    Case Else

        'Select all cells in Sheet to UnMerge

        oSheet.Cells.UnMerge

    End Select

    'Save new book to Excel file

    oBook.SaveAs (sDestPath)

    'Close the xls file

    oExcel.Workbooks.Close()

End Function