470,565 Members | 2,232 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,565 developers. It's quick & easy.

Excel 'freeze panes' from Access VBA code

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
Jun 25 '07 #1
5 15073
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? ;)
Jun 25 '07 #2
Killer42
8,435 Expert 8TB
ActiveWindow.FreezePanes = True
Jun 26 '07 #3
ActiveWindow.FreezePanes = True
Yes, tried that, didn't work at first. Got it working now however:

Expand|Select|Wrap|Line Numbers
  1.  
  2. Dim oApp As New Excel.Application
  3. Dim oBook As Excel.Workbook
  4. Dim oSheet As Excel.Worksheet
  5.  
  6. Set oBook = oApp.Workbooks.Add 'Open a new workbook
  7. Set oSheet = oBook.Worksheets(1) 'with a new sheet
  8. oSheet.PageSetup.Orientation = xlLandscape 'In Landscape for printing purposes
  9. oSheet.PageSetup.Zoom = fnZoomLevel(Name) ' Zoom level for printing
  10. oSheet.Name = fnName(Name) ' Give the sheet the right name (name of grade)
  11.  
  12. oSheet.Range("A4").Select
  13. oApp.ActiveWindow.FreezePanes = True
  14.  
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
Jun 26 '07 #4
Killer42
8,435 Expert 8TB
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.)
Jun 26 '07 #5
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
Jun 26 '07 #6

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by Dan | last post: by
3 posts views Thread by user_5701 | last post: by
2 posts views Thread by jcm | last post: by
1 post views Thread by livre | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.