Excel Disphelper help
Excel Disphelper help
I am using Disphelper with Excel. There is one function I can't figure out how to do. I would like to freeze the pane at the currently selected cell. In VBA the function is - Spreadsheet1.Activewindow.Freezepanes = true. Any help would be greatly appreciated.
Last edited by phishguy on Mar 14, 2008 14:13, edited 2 times in total.
-
- Posts: 40
- Joined: Aug 06, 2007 8:06
- Location: Altus, Ok, USA
- Contact:
Excel.ActiveWindow.FreezePanes as boolean..
If you have the Excel 12.0 object library, try under the
ActiveWindow class. FreezePanes should be a bool function there..
this is just a guesstimation..:
hope this helps in spite of being not real familiar with excel
If you have the Excel 12.0 object library, try under the
ActiveWindow class. FreezePanes should be a bool function there..
this is just a guesstimation..:
Code: Select all
#define UNICODE
#include "disphelper/disphelper.bi"
dhCreateObject( "Excel.ActiveWindow", NULL, @Excel_AW_OBJ )
'...
'...
'...
dhGetValue( "%b", @FrzPns_Result, Excel_AW_OBJ, ".FreezePanes()" )
'to receive status
'...
dhPutValue( Excel_AW_OBJ, "FreezePanes=%b", TRUE )
'to change status
While I am more familiar with excel than disphelper - to set a freeze Pane - you need to set the boundry first... look for a function that does that and execute that function first
In a Excel macro it looks like this:
So it looks like you may be missing the 'SplitRow' (Horizontal split) or 'SplitColumn' (Vertical Split) first... ??
edit: It looks like the split is based on cell count (in cells) from top left (maybe this is localized..?? - top left is cell A0 for me in US english). thus why '4.4666667' - looks like fractional cell, but 'snaps' to that cell (Row 4) when the freeze is applied.
But, If I do a splitColumn - it snaps as I drag the split handle... *before* I apply the freeze.
So - to set the split to your currently selected cell - you would also need to get that cell position also - extract the row or column you want to set the split at - then apply the freeze.
You get the selected cell address in any of the following ways, which one you go with determins what is returned - and thus, what you would do to extract the current row or column.
/edit.
hope that helps...
In a Excel macro it looks like this:
Code: Select all
ActiveWindow.SplitRow = 4.46666666666667
ActiveWindow.FreezePanes = True
edit: It looks like the split is based on cell count (in cells) from top left (maybe this is localized..?? - top left is cell A0 for me in US english). thus why '4.4666667' - looks like fractional cell, but 'snaps' to that cell (Row 4) when the freeze is applied.
But, If I do a splitColumn - it snaps as I drag the split handle... *before* I apply the freeze.
So - to set the split to your currently selected cell - you would also need to get that cell position also - extract the row or column you want to set the split at - then apply the freeze.
You get the selected cell address in any of the following ways, which one you go with determins what is returned - and thus, what you would do to extract the current row or column.
Code: Select all
Set mc = Worksheets("Sheet1").Cells(1, 1)
MsgBox mc.Address() ' returns $A$1
MsgBox mc.Address(RowAbsolute:=False) ' returns $A1
MsgBox mc.Address(ReferenceStyle:=xlR1C1) ' returns R1C1
MsgBox mc.Address(ReferenceStyle:=xlR1C1, _
RowAbsolute:=False, _
ColumnAbsolute:=False, _
RelativeTo:=Worksheets(1).Cells(3, 3)) ' returns R[-2]C[-2]
/edit.
hope that helps...
This code solved my problem
Code: Select all
dhCallMethod( xlSheet, "cells(%u,%u).Select",2,1 )
dhPutValue(xlapp, "activewindow.FreezePanes=%b", 1 )
Here's another one I can't figure out how to convert for use with Disphelper.
ActiveSheet.PageSetup.PrintTitleRows = ActiveSheet.Rows(1).Address
ActiveSheet.PageSetup.PrintTitleColumns = _
ActiveSheet.Columns("A:C").Address
I am kind of getting the hang of using Disphelper with Excel. The many examples that forum members have given me has helped a lot. This one has me stumped though. Any ideas?
ActiveSheet.PageSetup.PrintTitleRows = ActiveSheet.Rows(1).Address
ActiveSheet.PageSetup.PrintTitleColumns = _
ActiveSheet.Columns("A:C").Address
I am kind of getting the hang of using Disphelper with Excel. The many examples that forum members have given me has helped a lot. This one has me stumped though. Any ideas?
A couple of thoughts while you're waiting for more knowledgeable responses..
You know I can't test, I don't have Excel..
Code: Select all
'pseudo-disphelper
'
dim ASRowAddress as wstring * 32
dim ASColAddress as wstring * 32
'
dhGetValue("%S",@ASRowAddress,xlSheet,"ActiveSheet.Rows(1).Address")
'or:
'dhGetValue("%S",@ASRowAddress,xlSheet,"ActiveSheet.Rows(%d).Address",1)
dhPutValue(xlSheet,"ActiveSheet.PageSetup.PrintTitleRows = %S",@ASRowaddress)
'
dhGetValue("%S",@ASColAddress,xlSheet,"ActiveSheet.Columns(%S).Address",@"A:C")
dhPutValue(xlSheet,"ActiveSheet.PageSetup.PrintTitleColumns = %S",@ASColAddress)
'or just:
'dhPutValue(xlSheet,"ActiveSheet.PageSetup.PrintTitleColumns = %S",@"A:C")
'or:
'ASColAddress = "A:C"
'dhPutValue(xlSheet,"ActiveSheet.PageSetup.PrintTitleColumns = %S",@ASColAddress)
'
'
'ActiveSheet.PageSetup.PrintTitleRows = ActiveSheet.Rows(1).Address
'ActiveSheet.PageSetup.PrintTitleColumns = _
'ActiveSheet.Columns("A:C").Address
Thanks Zippy. Unfortunately none of the methods are working for me. Maybe I'm missing something.
(edit)
I figured it out. Since I want all of row 1 to be repeated, I used the following.
(edit)
I figured it out. Since I want all of row 1 to be repeated, I used the following.
Code: Select all
dhPutValue(xlApp, ".ActiveSheet.Pagesetup.PrintTitleRows = %s","A1" )
Nevermind, I figured it out.
Code: Select all
dhputvalue(xlapp, "activesheet.SaveAs(%s, %d)", "c:\freebasic\blank.xlt",17 )
Another question. I have now added a file open dialog which works fine except to use Disphelper I need to define unicode which messes up the function at the commented lines. How do I fix this?
Code: Select all
Function file_getname( Byval hWnd As HWND ) As String
Dim ofn As OPENFILENAME
Dim filename As Zstring * MAX_PATH+1
With ofn
.lStructSize = Sizeof( OPENFILENAME )
.hwndOwner = hWnd
.hInstance = GetModuleHandle( NULL )
' .lpstrFilter = strptr (filetype)
If selection = 4 Then
.lpstrFilter = strptr ( !"Excel Files (*.xls)\0*.xls\0\0" ) 'fails with unicode
End If
If selection = 1 Or selection = 2 Then
.lpstrFilter = strptr ( !"Template Files (*.xlt)\0*.xlt\0\0") ' fails with unicode
End If
.lpstrCustomFilter = NULL
.nMaxCustFilter = 0
.nFilterIndex = 1
.lpstrFile = @filename
.nMaxFile = Sizeof( filename )
.lpstrFileTitle = NULL
.nMaxFileTitle = 0
.lpstrInitialDir = NULL
.lpstrTitle = @"XLWedge - Select file" 'and here
.Flags = OFN_EXPLORER Or OFN_PATHMUSTEXIST Or OFN_Filemustexist
.nFileOffset = 0
.nFileExtension = 0
.lpstrDefExt = NULL
.lCustData = 0
.lpfnHook = NULL
.lpTemplateName = NULL
End With
If( GetOpenFileName( @ofn ) = FALSE ) Then
Return ""
Else
Return filename
End If
End Function
Ok, I figured out how to change those lines (see code). However, the returned filename is now blank. What else needs to be changed?
Code: Select all
Function file_getname( Byval hWnd As HWND ) As String
dim title as string = "XLWedge select a file"
Dim ofn As OPENFILENAME
Dim filename As Zstring * MAX_PATH+1
With ofn
.lStructSize = Sizeof( OPENFILENAME )
.hwndOwner = hWnd
.hInstance = GetModuleHandle( NULL )
' .lpstrFilter = strptr (filetype)
If selection = 4 Then
.lpstrFilter = strptr ( wstr(!"Excel Files (*.xls)\0*.xls\0\0") )
End If
If selection = 1 Or selection = 2 Then
.lpstrFilter = strptr ( wstr(!"Template Files (*.xlt)\0*.xlt\0\0"))
End If
.lpstrCustomFilter = NULL
.nMaxCustFilter = 0
.nFilterIndex = 1
.lpstrFile = @(filename)
.nMaxFile = Sizeof( filename )
.lpstrFileTitle = NULL
.nMaxFileTitle = 0
.lpstrInitialDir = NULL
.lpstrTitle = @wstr("XLwedge")
.Flags = OFN_EXPLORER Or OFN_PATHMUSTEXIST Or OFN_Filemustexist
.nFileOffset = 0
.nFileExtension = 0
.lpstrDefExt = NULL
.lCustData = 0
.lpfnHook = NULL
.lpTemplateName = NULL
End With
If( GetOpenFileName( @ofn ) = FALSE ) Then
Return ""
Else
Return filename
End If
End Function