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.

'================================================================
'*************   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
'==========================================================================