| Forum Home | ||||
| Press F1 | ||||
| Thread ID: 111633 | 2010-08-05 00:20:00 | Excel Freeze Panes from Python | Mike (15) | Press F1 |
| Post ID | Timestamp | Content | User | ||
| 1124704 | 2010-08-05 00:20:00 | 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. |
Mike (15) | ||
| 1124705 | 2010-08-05 02:14:00 | Have you tried doing something like xl.ActiveWorkbook.Windows(1).FreezePanes = False xl.Cells.Range("D2").Select xl.ActiveWorkbook.Windows(1).FreezePanes = True |
MushHead (10626) | ||
| 1124706 | 2010-08-05 02:47:00 | 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. |
Mike (15) | ||
| 1124707 | 2010-08-05 03:30:00 | 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? | MushHead (10626) | ||
| 1124708 | 2010-08-05 06:40:00 | 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. |
Mike (15) | ||
| 1 | |||||