Public Sub FormatWorksheet(ByVal TargetPath As String, _
ByVal SheetName As String)
[COLOR="SeaGreen"]'Note that this procedure requires that the XLS file being modified not be in use
'Note that this procedure does not actually check to see if the XLS file is in use.[/COLOR]
[COLOR="seagreen"]'Variable declaration[/COLOR]
Dim wb As Excel.Workbook[COLOR="seagreen"] 'Workbook being modified[/COLOR]
Dim ws As Excel.Worksheet[COLOR="seagreen"] 'Worksheet being modified[/COLOR]
Dim LastColumn As Integer[COLOR="seagreen"] 'Last column used in the indicated worksheet[/COLOR]
Dim ProcName As String[COLOR="seagreen"] 'Name of the procedure being run[/COLOR]
On Error GoTo FormatWorksheet_Err
[COLOR="seagreen"] 'Assign default values.[/COLOR]
ProcName = "FormatWorksheet"
[COLOR="seagreen"] 'Assign to wb and open the indicated workbook.[/COLOR]
Set wb = Excel.Workbooks.Open(TargetPath)
[COLOR="seagreen"] 'Assign to ws the indicated sheet.[/COLOR]
Set ws = wb.Sheets(SheetName)
[COLOR="seagreen"] 'Determine the last used column.[/COLOR]
LastColumn = ws.UsedRange.Columns.Count
[COLOR="seagreen"] 'Set the first row in the assigned range to grey.[/COLOR]
ws.Range(Cells(1, 1), Cells(1, LastColumn)).Interior.ColorIndex = 15
[COLOR="seagreen"] 'Make the font in the first row bold.[/COLOR]
ws.Range(Cells(1, 1), Cells(1, LastColumn)).Font.Bold = True
[COLOR="seagreen"] 'Outline every cell in the used area.[/COLOR]
ws.UsedRange.Cells.Borders.LineStyle = xlContinuous
ws.UsedRange.Cells.Borders.Weight = xlThin
ws.UsedRange.Cells.Borders.Color = 1
[COLOR="seagreen"] 'Resize all columns in TargetRange.[/COLOR]
ws.UsedRange.Columns.AutoFit
FormatWorksheet_Exit:
If Not ws Is Nothing Then Set ws = Nothing
If Not wb Is Nothing Then
wb.Save
wb.Close
Set wb = Nothing
End If
Exit Sub
FormatWorksheet_Err:
MsgBox "An error was encountered during execution of procedure '" & ProcName & "'" & vbCrLf & vbCrLf & _
"Error number:" & vbTab & Err.Number & vbCrLf & _
"Error desc: " & vbTab & Err.Description, vbCritical
Resume FormatWorksheet_Exit
End Sub