Formatting Excel worksheet: Adding FreezePane

I’ve recently been working on a project where the user wants to be able to export many of his data tables/queries to Excel, with additional formatting added.  I ran into a problem that just drove me crazy for 3 days.  All I wanted to do was place the cursor in cell E3 and click the FreezePanes button.  So I went to Excel, recorded a macro which looked like:

ActiveWindow.FreezePanes = True

But when I did this from Access it would not work; it kept raising an error:

1004  Unable to set the FreezePanes property of the Window class

This, despite the fact that I modified it slightly to include a reference to my worksheet object (oSHT).  It took three days before one of my MVP buddies mentioned that he found references to the error message which implied it had to do with XL being minimized.  So I added a line to maximize Excel and voila!

oXL.WindowState = xlMaximized
oXL.ActiveWindow.FreezePanes = True

That’s all there was too it.

Leave a Reply

Your email address will not be published. Required fields are marked *

© 2024 Developing Solutions | ScrollMe by AccessPress Themes