EnableAutoFilter

[jakea]

I've used EnableAutoFilter in a spreadsheet produced from Progress 8, using the excel.application COM object, in order to allow users to filter columns in a spreadsheet with some protected columns. However, I have run into the problem that it needs to be re-applied every time the spreadsheet is opened.

I've found code that has to be inserted in the "this worksheet" module:

Private Sub Workbook_Open()
Dim ws As Worksheet
For Each ws In Worksheets
ws.Unprotect Password:="pass"
ws.EnableAutoFilter = True
ws.Protect Password:="pass", userinterfaceonly:=True
Next ws
End Sub

How do I insert this from Progress while creating the spreadsheet?

 

[Casper]

I haven't tried it but it looks like you need something like:

chWS:Unprotect("pass"). /* chWS is com-handle to worksheet */
chWS:EnableAutoFilter = yes.
chWS:Protect("pass",,,,yes).

Regards,
Casper.

 

[jakea]

Casper,

thanks for getting back to me, but your code is pretty much what I've used to turn this functionality on when the spreadsheet is created and opened. The problem is that although it works when the sheet is first opened, if you re-open a saved version, auto-filter no longer works.

If you insert the code I quoted into the right module, you can auto-filter a reopened spreadsheet. I just don't know how to insert a subroutine like this when creating a spreadsheet from Progress.

Jake.

 

[Casper]

From the msdn help:

This property applies to each worksheet and isn't saved with the worksheet or session.

The problem is that with the com handling you are not able to create an onopen event. (equivalent to the sub Workbook_Open()).

Maybe it is possible to use a macro for this?

Regards,
Casper.

(MSDN is reorganising there library for a change so half the methods are on msdn2 (with Jscript errors) and half is on msdn... )

 

[it@flude.co.uk]

That's what I tend to do with this kind of thing.

Put it into a macro and make the macro auto-execute when the sheet is opened.

So, set up a blank worksheet, put the macro into the sheet and make this a template. When you need a worksheet with this particular property, copy the template to a new worksheet and wok with the new worksheet.

 

[jakea]

That will work (it's the same as adding the subroutine from my initial post), but how do you do this from Progress? How do you specify the template you want to use when creating a spreadsheet with the com object?

We use Citrix, so there's a slight problem of knowing where the template would have to be to be able to call it, but I expect we could get round that.

Thanks for the idea,

Jake.

 

[it@flude.co.uk]

I normally use a little cheat.

Copy the template excel file to the path/name you want the new worksheet to be, then open the new worksheet, rather than creating a new one. By the way, it isn't a Template in the same way that Word has Templates, this is just a normal Excel Worksheet that you can use as a base.

I can't remember the last time I created a brand new Excel document from scratch, I always copy from a series of templates.

Do you have mapped network drives? If so, store it on a network drive and copy it in. I'm not that familiar with Citrix, though.

So:
def var worksheet_template as char initial "u:\templates\autofilter.xls" no-undo.

def var new_worksheet as char initial "c:\documents and settings\me\my documents\autofilter1.xls" no-undo.

os-copy value (worksheet_template) value (new_worksheet).

......