Export to Excel from Access and Format Excel

[gatorcc]

I have this little VBA module that formats a spreadsheet in Exce that is exported from Access. I need to add the font being Arial, the size of the font being 8 and the top row being frozen. Below is the code I have that does most of the formatting except the items I need to add. Any help is appreciated.
If this should be posted in the Excel Help forum please let me know and I will repost there.

Formatting:
Function FormatExcel(strFilename As String, strTabNames As String)
Dim objXL
Set objXL = CreateObject("Excel.Application")
objXL.DisplayAlerts = False
With objXL
.Workbooks.Open (strFilename)

' Rename Tabs - optional
Dim i As Integer
i = 1
If Len(strTabNames) Then
arrNames = Split(strTabNames, "|")
For Each strTabName In arrNames
.Worksheets(i).Name = strTabName
.Worksheets(i).AutoFilterMode = False
.Worksheets(i).Range("A1").AutoFilter
.Worksheets(i).Rows("1").Font.Bold = True
.Worksheets(i).Rows("1").Interior.Color = RGB(191, 191, 191)
.Worksheets(i).Columns("A:AQ").Columns.AutoFit
i = i + 1
Next
End If

.ActiveWorkBook.Save
.ActiveWorkBook.Close

End With
objXL.DisplayAlerts = True
Set objXL = Nothing
End Function

[austenr]

try this for the freeze panes:

.Worksheets(i).Rows("1").FreezePanes = True

[gatorcc]

try this for the freeze panes:

.Worksheets(i).Rows("1").FreezePanes = True

Thanks. I tried this but it blew up. I did get the size and font style parts to work. Any other ideas work be great. I am running Office 2007 if that matters.

Function FormatExcel(strFilename As String, strTabNames As String)
Dim objXL
Set objXL = CreateObject("Excel.Application")
objXL.DisplayAlerts = False
With objXL
.Workbooks.Open (strFilename)

' Rename Tabs - optional
Dim i As Integer
i = 1
If Len(strTabNames) Then
arrNames = Split(strTabNames, "|")
For Each strTabName In arrNames
.Worksheets(i).Name = strTabName
.Worksheets(i).AutoFilterMode = False
.Worksheets(i).Range("A1").AutoFilter
.Worksheets(i).Rows("1").Font.Bold = True
.Worksheets(i).Rows("1").Interior.Color = RGB(191, 191, 191)
.Worksheets(i).Cells.Font.Name = "Arial"
.Worksheets(i).Cells.Font.Size = 8
.Worksheets(i).Columns("A:AQ").Columns.AutoFit
' .Worksheets(i).Rows("1").FreezePanes = True
i = i + 1
Next
End If

.ActiveWorkBook.Save
.ActiveWorkBook.Close

End With
objXL.DisplayAlerts = True
Set objXL = Nothing
End Function

[austenr]

Try putting this outside your for loop.

.Row(1).FreezePanes = True

Right after this line:

.Workbooks.Open (strFilename)

[gcomyn]

I got it to work after I used .activate to show the file and sheets. here is the code that I use:

Function FormatExcel(strFilename As String, strTabNames As String)
Dim objXL
Dim arrNames() As String
Dim strTabName As Variant


Set objXL = CreateObject("Excel.Application")
objXL.DisplayAlerts = False
With objXL
.Workbooks.Open (strFilename)
.Visible = True
' Rename Tabs - optional
Dim i As Integer
i = 1
If Len(strTabNames) Then
arrNames = Split(strTabNames, "|")
For Each strTabName In arrNames
.Worksheets(i).Activate
.Worksheets(i).Name = strTabName
.Worksheets(i).AutoFilterMode = False
.Worksheets(i).Range("A1").AutoFilter
.Worksheets(i).Rows("1").Font.Bold = True
.Worksheets(i).Rows("1").Interior.Color = RGB(191, 191, 191)
.Worksheets(i).Cells.Font.Name = "Arial"
.Worksheets(i).Cells.Font.Size = 8
.Worksheets(i).Columns("A:AQ").Columns.AutoFit
.Worksheets(i).Range("a2").Select
.ActiveWindow.FreezePanes = True
i = i + 1
Next
End If

.ActiveWorkBook.Save
.ActiveWorkBook.Close

End With
objXL.DisplayAlerts = True
Set objXL = Nothing
End Function

I had to add some dim'd variables (since I always use Option Explicit). and had to activate the workbook and each sheet as it showed up... but it works. You may not have to have the workbook visible for this to work, but I put it in so I could see what was going on.

GComyn
:sleuth: