Excel Disphelper help

External libraries (GTK, GSL, SDL, Allegro, OpenGL, etc) questions.
Post Reply
phishguy
Posts: 1201
Joined: May 05, 2006 16:12
Location: West Richland, Wa

Excel Disphelper help

Post by phishguy »

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.
wallyfblu
Posts: 69
Joined: May 24, 2006 10:58
Location: ITALY

Post by wallyfblu »

hi phishguy, I to run my old excel code in order to try help you, but I had some errors that I don't figure out:

C:/FreeBASIC/inc/win/windef.bi(61) error 3: Expected End-of-Line, found 'ptr'
type PWINBOOL as integer ptr

Maybe some 0.18.3 problems? I never used disphelper after this release
crysstaafur
Posts: 40
Joined: Aug 06, 2007 8:06
Location: Altus, Ok, USA
Contact:

Post by crysstaafur »

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..:

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
hope this helps in spite of being not real familiar with excel
maddogg6
Posts: 824
Joined: Dec 07, 2005 22:58
Contact:

Post by maddogg6 »

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:

Code: Select all

    ActiveWindow.SplitRow = 4.46666666666667
    ActiveWindow.FreezePanes = True
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.

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...
phishguy
Posts: 1201
Joined: May 05, 2006 16:12
Location: West Richland, Wa

Post by phishguy »

Thanks for your help guys. Still can't get it to work. I cheated by sending keystroke commands to lock the scroll window. It's kind of a hack.
phishguy
Posts: 1201
Joined: May 05, 2006 16:12
Location: West Richland, Wa

Post by phishguy »

This code solved my problem

Code: Select all

dhCallMethod( xlSheet, "cells(%u,%u).Select",2,1 )

dhPutValue(xlapp, "activewindow.FreezePanes=%b", 1 ) 
phishguy
Posts: 1201
Joined: May 05, 2006 16:12
Location: West Richland, Wa

Post by phishguy »

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?
Zippy
Posts: 1295
Joined: Feb 10, 2006 18:05

Post by Zippy »

A couple of thoughts while you're waiting for more knowledgeable responses..

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 
You know I can't test, I don't have Excel..
phishguy
Posts: 1201
Joined: May 05, 2006 16:12
Location: West Richland, Wa

Post by phishguy »

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.

Code: Select all

dhPutValue(xlApp, ".ActiveSheet.Pagesetup.PrintTitleRows = %s","A1" )
phishguy
Posts: 1201
Joined: May 05, 2006 16:12
Location: West Richland, Wa

Post by phishguy »

Argh, does anyone have an example for doing a saveas function for Excel using Disphelper?
phishguy
Posts: 1201
Joined: May 05, 2006 16:12
Location: West Richland, Wa

Post by phishguy »

Nevermind, I figured it out.

Code: Select all

dhputvalue(xlapp, "activesheet.SaveAs(%s, %d)", "c:\freebasic\blank.xlt",17  )
 
phishguy
Posts: 1201
Joined: May 05, 2006 16:12
Location: West Richland, Wa

Post by phishguy »

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
phishguy
Posts: 1201
Joined: May 05, 2006 16:12
Location: West Richland, Wa

Post by phishguy »

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
Zippy
Posts: 1295
Joined: Feb 10, 2006 18:05

Post by Zippy »

I'll bet you've fingered this out already..

Try changing this:

Dim filename As Zstring * MAX_PATH+1

to:

Dim filename As wstring * MAX_PATH+1

..tested..
phishguy
Posts: 1201
Joined: May 05, 2006 16:12
Location: West Richland, Wa

Post by phishguy »

No, I hadn't figured that out. Thanks again. It works great now.
Post Reply