'================================================================ '************* Format Excel Sheet *************************** '================================================================ Sub FormatExcelSheet(ByVal myFilePath As String) ' Dim objXL As Excel.Application Dim objWkb As Excel.Workbook Dim objSht As Excel.Worksheet Dim WKB_NAME As String 'Dim SHT_NAME As String Dim intLastCol As Integer Dim Current As Worksheet ' Declare Current as a worksheet object variable. WKB_NAME = myFilePath Set objXL = Excel.Application With objXL .Visible = False Set objWkb = .Workbooks.Open(WKB_NAME) On Error Resume Next Debug.Print objWkb.Name 'Loop through all of the worksheets in the active workbook. For Each Current In Worksheets Set objSht = objWkb.Worksheets(Current.Name) objSht.Activate intLastCol = objSht.UsedRange.Columns.Count With objSht ' Paint first row yellow .Cells(1, 1).EntireRow.Interior.ColorIndex = 6 ' Use bold font in first row .Cells(1, 1).EntireRow.Font.Bold = True 'Auto fit cell width .Columns("A:CZ").AutoFit End With objSht.Cells(2, 1).Select objXL.ActiveWindow.FreezePanes = False 'Freeze the first row with column titles. objXL.ActiveWindow.FreezePanes = True Set objSht = Nothing Next End With objWkb.Save objWkb.Close objXL.Quit Set objWkb = Nothing Set objXL = Nothing WKB_NAME = "" End Sub '==========================================================================
Samples of VBA code I'm using in my daily tasks.
Friday, March 12, 2010
Format Excel Sheet From Access
I have this function in my Access modules. When a table or a query is transfered to a speadsheet I use the function to format all sheets in the workbook.
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment