'================================================================ '************* 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.
Showing posts with label Excel. Show all posts
Showing posts with label Excel. Show all posts
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:
Posts (Atom)