Export into Excel and Format as Table (1 Viewer)

tucker61

Registered User.
Local time
Today, 16:52
Joined
Jan 13, 2008
Messages
321
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.


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
 

Minty

AWF VIP
Local time
Today, 23:52
Joined
Jul 26, 2013
Messages
10,355
The second function XLFormatTable takes the full file path and name, the sheet name and formats the contents as a table.

It makes the assumption that the data starts at cell A1 and that it has headings. This would be how a standard transfer from the spreadsheet would work.
 

Users who are viewing this thread

Top Bottom