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.

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

No comments:

Post a Comment