Excel 'freeze panes' from Access VBA code

[jasperz01]

Hi,

Is it possible using Office Automation to freeze panes in Excel from Access VBA code? I've been trying some things but can't get it right...

Jasper

[jasperz01]

Noone just yet? Maybe I should clarify myself:

All I want is the upper few (5) rows of my Excel sheet not to scroll when scrolling the sheet. And that with some VBA code from Access, I already got the parts that put my querydata on a sheet...

I didn't think this should be hard to do but now it seems nearly impossible... Please anyone? anywhere? ;)

[Killer42]

ActiveWindow.FreezePanes = True

[jasperz01]

ActiveWindow.FreezePanes = True

Yes, tried that, didn't work at first. Got it working now however:

 
Dim oApp As New Excel.Application
Dim oBook As Excel.Workbook
Dim oSheet As Excel.Worksheet
 
Set oBook = oApp.Workbooks.Add 'Open a new workbook
Set oSheet = oBook.Worksheets(1) 'with a new sheet
oSheet.PageSetup.Orientation = xlLandscape 'In Landscape for printing purposes
oSheet.PageSetup.Zoom = fnZoomLevel(Name) ' Zoom level for printing
oSheet.Name = fnName(Name) ' Give the sheet the right name (name of grade)
 
oSheet.Range("A4").Select
oApp.ActiveWindow.FreezePanes = True
 

I tried 'ActiveWindow' as a property of oSheet, that didn't work of course ;) You also need to select the right cell before setting the FreezePanes property.
Works like a dream now. Thanks!

Jasper

[Killer42]

Glad to see you got it working. :)

Sorry, I was at work and in a rush when I posted my response, but thought that would help push you in the right direction. (I just did "record new macro", did a "freeze", stopped and copied the macro.)

[jasperz01]

Glad to see you got it working. :)

Sorry, I was at work and in a rush when I posted my response, but thought that would help push you in the right direction. (I just did "record new macro", did a "freeze", stopped and copied the macro.)

No Prob!

That's what I tried :) Thanks for the effort,

Jasper