PDA

View Full Version : Excel Freeze Panes from Python



Mike
05-08-2010, 11:20 AM
I have a python script that automates a database export into Excel, formats it, sorts it, saves it etc. Works quite well (if I can say so myself) :)

Anyway I can't figure out how to do one last task - Freeze Panes. I want to freeze the top row, and the first 3 columns in the XLS file. I can't figure out how to get it to work (just errors when I try different ideas).

I'm using win32com rather than some of the fancy Excel Python modules that are available. Here's an example of the code that does the sorting, formatting, and saving...
from win32com.client import Dispatch

xl = Dispatch("Excel.Application")
xl.Visible = 0
xl.Workbooks.Open("C:/WorkSpace/mike/excel/Export.xls")

lastRow = xl.ActiveSheet.UsedRange.Rows.Count
lastCol = xl.ActiveSheet.UsedRange.Columns.Count

xl.ActiveSheet.Range(xl.ActiveSheet.Cells(2,1),xl. ActiveSheet.Cells(lastRow,lastCol)).Sort(Key1 = xl.Range("A2"), Order1=1, Key2=xl.Range("B2"), Order2=1)
xl.ActiveSheet.Range(xl.ActiveSheet.Cells(1,1),xl. ActiveSheet.Cells(1,4)).Font.Bold = True

xl.ActiveSheet.Cells.Select
xl.ActiveSheet.Cells.EntireColumn.AutoFit()
xl.ActiveWorkbook.Close(SaveChanges=1)

xl.Quit()
xl.Visible = 0
del xlThanks,
Mike.

MushHead
05-08-2010, 01:14 PM
Have you tried doing something like



xl.ActiveWorkbook.Windows(1).FreezePanes = False
xl.Cells.Range("D2").Select
xl.ActiveWorkbook.Windows(1).FreezePanes = True

Mike
05-08-2010, 01:47 PM
I have now :)

You've got me a step closer... it freezes the pane, and there are no errors...

however it freezes it in the centre of the screen, not at the selected cell... and I tested this - put excel on a bigger screen, and the panes freeze at the centre of that one, put it on a smaller screen, and panes freeze at its centre. Odd :) it's a start though...

Mike.

MushHead
05-08-2010, 02:30 PM
Strange. It worked fine for me (within Excel, though). I did notice that setting FreezePanes only works if it's already off, that's why the first line is there. I daresay that somewhere buried in the sheet properties there's one containing the location of the freeze - maybe you can manipulate that directly?

Mike
05-08-2010, 05:40 PM
Strange. It worked fine for me (within Excel, though). I did notice that setting FreezePanes only works if it's already off, that's why the first line is there. I daresay that somewhere buried in the sheet properties there's one containing the location of the freeze - maybe you can manipulate that directly?I'll take a look and see if I can find something. Did a couple of google searches on that problem and found a few posts from people that had a similar issue (freezing at centre of screen) but no solutions.

Mike.