FreezePanes in Export to Excel Doesn't Work 1

[bvbowes]

I can't figure why my workbook won't freeze panes at A2 so that my column headers are always showing in a scroll. Everything else works.

Here's the whole code:

Private Sub cmdExport_Click()
On Error Resume Next
Dim xlApp        As Excel.Application
Dim xlSheet      As Excel.Worksheet
Dim xlWorkbook   As Excel.Workbook
Dim acQuery      As QueryDef
Dim objRST       As Recordset
Dim strQryName   As String
Dim strSheetName As String

' Set the form's mouse pointer to hourglass to show data is compiling.
Screen.MousePointer = 11

strQueryName = Me.List1
strSheetName = Left(strQueryName, 31)
strSheetName = Trim(strSheetName)
Set xlApp = New Excel.Application
xlApp.Visible = False
Set xlWorkbook = xlApp.Workbooks.Add
Set objRST = Application.CurrentDb.OpenRecordset(strQueryName)
'Add column headers to excel output
Set xlSheet = xlWorkbook.Sheets(1)
    For lvlColumn = 0 To objRST.Fields.count - 1
    xlSheet.Cells(1, lvlColumn + 1).Value = objRST.Fields(lvlColumn).Name
'Change the font to bold for the header row
xlSheet.Range(xlSheet.Cells(1, 1), _
xlSheet.Cells(1, objRST.Fields.count)).Font.Bold = True
'FREEZE HEADER CELLS - THIS ISN"T WORKING
With xlSheet.Range("A2").Select
     ActiveWindow.FreezePanes = True
End With
'Add Border to header row cells
With xlSheet.Range(xlSheet.Cells(1, 1), _
xlSheet.Cells(1, objRST.Fields.count)).Borders(xlEdgeLeft)
    .LineStyle = xlContinuous
    .Weight = xlThin
    .ColorIndex = xlAutomatic
End With
With xlSheet.Range(xlSheet.Cells(1, 1), _
xlSheet.Cells(1, objRST.Fields.count)).Borders(xlEdgeTop)
    .LineStyle = xlContinuous
    .Weight = xlThin
    .ColorIndex = xlAutomatic
End With
With xlSheet.Range(xlSheet.Cells(1, 1), _
xlSheet.Cells(1, objRST.Fields.count)).Borders(xlEdgeBottom)
    .LineStyle = xlContinuous
    .Weight = xlThin
    .ColorIndex = xlAutomatic
End With
With xlSheet.Range(xlSheet.Cells(1, 1), _
xlSheet.Cells(1, objRST.Fields.count)).Borders(xlEdgeRight)
    .LineStyle = xlContinuous
    .Weight = xlThin
    .ColorIndex = xlAutomatic
End With
'Autofit columns
Set xlSheet = xlBook.Sheets(strQueryName)
    xlSheet.Cells.EntireColumn.AutoFit
'Name worksheet
With xlSheet
    .Range("A2").CopyFromRecordset objRST
    .Name = strSheetName
End With
Next
xlApp.Visible = True
Set objRST = Nothing
Set xlSheet = Nothing
Set xlWorkbook = Nothing
Set xlApp = Nothing

' Set the form's mouse pointer back to the default mouse pointer.
Screen.MousePointer = 0

End Sub

 

[SkipVought]

Hi,

xlSheet.Activate
xlSheet.Range("A2").select
ActiveWindow.FreezePanes = True

Skip,
[sub]
[red][/red]
[/sub]

 

[bvbowes]

I've tried that one already and it did not work.

I did however compact and repair, and my same code worked!

So I tried it again, and it did NOT work. So I compacted and repaired again, and it did NOT work, so I tried a 3rd time, and it DID work.

What is going on?

 

[SkipVought]

This first line is an incorrect syntax

With xlSheet.Range("A2").Select
     ActiveWindow.FreezePanes = True
End With

There is no COMPACT & REPAIR in Excel.

There is Detect & Repair

Skip,
[sub]
[red][/red]
[/sub]

 

[bvbowes]

Well I'm doing all this code in Access, not in Excel, thats why I stated Compact and Repair. I'll change the code back to the way you suggest. I guess the problem lies with Excel and not Access, and I didn't know Excel had it's little repair thing like Access does. I might try that as well.
Thanks very much for your help.

 

[SkipVought]

I misspoke regarding the incorrect syntax. Your syntax was just fine.

Skip,
[sub]
[red][/red]
[/sub]

 

[SkipVought]

...as long as xlSheet is the ActiveSheet.

Skip,
[sub]
[red][/red]
[/sub]

 

[PHV]

[!]xlApp.[/!]ActiveWindow.FreezePanes = True

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[bvbowes]

Wow PHV - your xlApp. suggestion seems to be working - everytime you respond it's golden...thank you both.