I have a code that runs daily and formats the report into Excel.
I now need to format the exported report as a table.
Please can someone shoe me what bit of Code below needs to be changed in order to do this.
I now need to format the exported report as a table.
Please can someone shoe me what bit of Code below needs to be changed in order to do this.
Code:
Sub FormatExcel(ExcelFile As String, LastCell As Integer, FreezeColumn As Integer, Optional Quit As Boolean)
On Error GoTo Handler
Dim xlApp As Object
Dim wb As Object
Dim ws As Object
Dim i As Integer
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True
xlApp.DisplayAlerts = False
Set wb = xlApp.Workbooks.Open(ExcelFile)
Set ws = wb.Sheets(1)
ws.cells.Font.Name = "Calibri"
ws.cells.Font.Size = 9
ws.EnableAutoFilter = True
ws.cells.AutoFilter (1)
ws.Rows(1).Font.Bold = True
ws.Columns.AutoFit
wb.windows(1).SplitColumn = FreezeColumn
wb.windows(1).SplitRow = 1
wb.windows(1).FreezePanes = True '' Disable due tot he issue mark was havingerror 1004
For i = 1 To LastCell
ws.cells(1, i).Value = StrConv(ws.cells(1, i).Value, vbProperCase)
ws.cells(1, i).Interior.Color = HexColour("#7EC0EE")
ws.cells(1, i).WrapText = True
Next i
DoEvents
wb.Save
If Quit = True Then
xlApp.Quit
End If
Exit Sub
Handler:
Call LogError(Err.Number, Err.Description, "FormatExcelError2", "")
'Forms!frmMain.Visible = True
Exit Sub
End Sub