Excel ...how to freezepanes?

asaw

I am working on a report that exports all the data to excel. Additional formattings like autofilter, column autofit, freezepanes etc are required.

Right now i am using Excel Buffer table to "transfer" the data out to excel.
However, i cannot seem to use the "freezepanes" function, it keeps giving me error that says i need to define the variable "FreezePanes"

XLWorkSheet.Range('A2:S2').FreezePanes := TRUE;

I also tried to use Rows to do freezepanes, but also got error....apparently, that method takes no parameters.

XlWorkSheet.Rows("2:2").Select;

Would really appreciate your recommendations on how to do this?

Thanks
A.

Savatage

i don't know the answer but maybe you can ask the MS Excel MVP on

http://www.mvps.org/

---

This Freezes Panes above and to the left of the cursor position

ActiveWindow.FreezePanes = True

**now you just need to get the cursor the the correct cell. :-k

kriki

Have you tried this:
-start Excel
-start the macro recorder
-do some things (select a sheet, enter some data)
-Select a cell and freeze the pane.
-stop the recorder,
-go in the visual basic editor and check the code.
-This is what you have to do in Navision.

asaw

Thank you all for your replies.
Actually, i did use the Excel Macro feature to record my freezepanes action and i got this:

Rows("2:2").Select
ActiveWindow.FreezePanes = True

But when used in the excel buffer table functions,
it has to be accosiated with a worksheet, so i did this:
XlWorkSheet.Rows("2:2").Select
XlWorkSheet.ActiveWindow.FreezePanes := True

but i got compiler errors because if you use F5 to bring up the C/AL symbol menu, there is no "ActiveWindow" property or function available for XlWorkSheet, and also the Row() method available for XlWorkSheet does not take any parameters, that's why i got compiler error !

I know that for automation objects, sometimes, C/AL symbol menu only shows the first level methods, in this case, i already got compiler error on the first dot (.)

Thanks !
Ailee

asaw

Oh i figured out how to do it.


XlApplication.Range('A2').Select;
XlApplication.ActiveWindow.FreezePanes := TRUE;

ActiveWindow does not belong to XlWorkSheet....how stupid of me duh !

Thanks for trying to help !

Ailee

FranVLC

Thanks, you helped me a lot! =D>