Imports Microsoft.Office.Interop
Imports Microsoft.Office.Interop.Excel

‘Create the Excel object declaration

' create a excel application object
Dim objExcel As Excel.Application = Nothing
' create a excel workbooks object
Dim objBooks As Excel.Workbooks = Nothing
' create a workbook object
Dim objBook As Excel.Workbook = Nothing
' create a excel sheets object
Dim objSheets As Excel.Sheets = Nothing
' create a excel sheet object
Dim objSheet As Excel.Worksheet = Nothing
' create a excel range object
Dim objRange As Excel.Range = Nothing

' Create a new object of the Excel application object
objExcel = New Excel.Application
objExcel.Visible = False
objExcel.DisplayAlerts = False
' Adding a collection of Workbooks to the Excel object
objBook = CType(objExcel.Workbooks.Add(), Excel.Workbook)

objBooks = objExcel.Workbooks
objSheet = CType(objBooks(1).Sheets.Item(1), Excel.Worksheet)
objSheets = objBook.Worksheets
' Adding multiple worksheets to workbook
objSheets.Add(Count:=6)

            ' Summary log file sheet
            ' adding first sheet as summary log file
 objBook = objBooks.Item(1)
 objSheet = CType(objSheets.Item(1), Excel.Worksheet)
 ' Assigning the worksheet name and summary log file values
 objSheet.Name = “test”
objExcel.Cells(2, 2).Value = “Title”
objExcel.Cells(3, 1).value = “Heading”
objExcel.Cells(4, 1).value = “Content”

objSheet.Range("A2", "Z2").Font.Bold = True
objSheet.Range("A2", "Z2").Font.ColorIndex = 5
objRange = objSheet.CellsWriteDataToExcel(objRange)
objSheet.Range("A2", "Z2").EntireColumn.AutoFit()



' Focus on summary log file sheet
objSheet = objBook.ActiveSheet()
Dim objFirstSheet As Excel.Worksheet
objSheets = objBook.Worksheets
objFirstSheet = CType(objSheets.Item(1), Excel.Worksheet)
objFirstSheet.Activate()

Marshal.ReleaseComObject(objSheet)
Marshal.ReleaseComObject(objSheets)

'Saving the Workbook as a normal workbook format under log location
objBook.SaveAs(“Path of the file”, Excel.XlFileFormat.xlWorkbookNormal, System.Reflection.Missing.Value, System.Reflection.Missing.Value, True, False, Excel.XlSaveAsAccessMode.xlNoChange, False, False, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value)




Private Sub WriteDataToExcel(ByVal objCells As Excel.Range)
        ' get the minutes value
        Dim intMin As Integer = Now.Minute
        ' get the current date value
        Dim currentDate As Date = Now.Date
        ' get the hource
        Dim inthr As Integer = Now.Hour
        ' get the cuirrent time
        Dim currentTime As String = inthr.ToString() + ":" + intMin.ToString()

        Try

            '  Assigning values to the excel cells

            objCells(3, 2) = strUsername

            objCells(4, 2) = currentDate

            objCells(5, 2) = currentTime

        Catch ex As Exception
            objExceptionHandler.ToWrite_ErrorinDB(ex)
            objUtilityFunctions.To_WriteAudit("WriteDataToExcel() - CVX_GIL3_DCT_BLLogProcess  ", ex.Message)
        End Try
End Sub