Minty
AWF VIP
- Local time
- Today, 05:09
- Joined
- Jul 26, 2013
- Messages
- 10,371
Have a look at this code - it takes a Excel Filename, I have fixed the sheet to sheet(1) but you could change it back to sSheet to take the worksheet name.
It formats the contents as a table. It uses late binding throughout.
Hopefully, this will get you where you need to be.
It formats the contents as a table. It uses late binding throughout.
Hopefully, this will get you where you need to be.
Code:
Public Sub XLFormatTable(sFile As String, sSheet As String, Optional bOpen As Boolean = True)
' On Error GoTo XLFormatTable_Error
' Late binding to avoid reference:
Dim xlApp As Object 'Excel.Application
Dim xlWB As Object 'Workbook
Dim xlWS As Object 'Worksheet
Dim tbl As Object
Dim rng As Object
Dim iSheet As Integer
Debug.Print sFile, sSheet
iSheet = 1
' Create the instance of Excel that we will use to open the temp book
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = bOpen
Set xlWB = xlApp.Workbooks.Open(sFile)
'Debug.Print xlWB.Name
Set xlWS = xlWB.worksheets(iSheet)
' Format our temp sheet
' ************************************************** *************************
xlApp.range("A1").Select
With xlWS
' With .UsedRange
' .borders.LineStyle = xlContinuous
' .borders.ColorIndex = 0
' .borders.TintAndShade = 0
' .borders.Weight = xlThin
' End With
'
' 'format header 90 degree
' With .Range("i1:y1")
' .HorizontalAlignment = xlCenter
' .VerticalAlignment = xlBottom
' .WrapText = False
' .Orientation = 90
' .AddIndent = False
' .IndentLevel = 0
' .ShrinkToFit = False
' .ReadingOrder = xlContext
' .MergeCells = False
' End With
' .UsedRange.Rows.RowHeight = 15
' .UsedRange.Columns.AutoFit
With xlWB.Sheets(iSheet)
Set rng = .Cells(1, 1).CurrentRegion
End With
Set tbl = xlWS.ListObjects.Add(xlSrcRange, rng, , xlYes)
tbl.TableStyle = "TableStyleMedium2"
tbl.ShowTotals = False
xlWS.Cells.EntireColumn.AutoFit
End With
xlWB.Save
If Not bOpen Then
xlApp.Workbooks.Close
Set xlApp = Nothing
Else
xlApp.ActiveWindow.WindowState = xlMaximized
End If
On Error GoTo 0
Exit Sub
XLFormatTable_Error:
MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure XLFormatTable, line " & Erl & "."
End Sub
