topic Re: How to enable Autofilter AND Write protection in XLSX using DDE in SAS Programming

How to enable Autofilter AND Write protection in XLSX using DDE

berndJR — Tue, 13 Feb 2018 07:44:10 GMT

Hi all,

What I need: A given XLSX file should be write-protected in some columns, while others should be changeable. In addition I need an Autofilter which should be applied on the protected range. So filtering and sorting must be possible after adding the protection.

What I have: I've wrote a VBS which can be run from a SAS programm using:

X d:\mypath\vbs_script.vbs
RUN;

This script you can find here in detail:

Set oexcel= CreateObject("Excel.Application")
oexcel.Visible = False

'Open File
Set ThisWorkbook = oexcel.Workbooks.Open("d:\mypath\test.xlsx")
Set sheet = ThisWorkbook.Worksheets("Tabelle1")

'Define write protected range
Set rng = sheet.Range("D:BE")

'Random Password
For x=1 To 15
Randomize
vChar = Int(89*Rnd) + 33
If vChar = 34 Then vChar = 39
RndPassword = RndPassword & Chr(vChar)
Next

'Lock write protected range
rng.Locked = True
sheet.Protect RndPassword

'save File
ThisWorkbook.SaveAs("d:\mypath\test_2.xlsx")

oexcel.Quit

It works for the protection part but now I'm stuck with adding the AutoFilter options. I've googled a lot, but I failed with any attempt so far.

Someone here who can help?

Thank you,

KR Bernd

Re: How to enable Autofilter AND Write protection in XLSX using DDE

RW9 — Tue, 13 Feb 2018 09:00:55 GMT

Whilst it is possible to do such a thing (assuming DDE even has that capability), I really would not recommend it. DDE is ancient, well over 20 years out of support. I would really question why you need such an Excel output file, unless there is specific requirements, and the client is willing to pay big money to faff around with such nonsense then I would drop it. If you can't, then create a template file, and export your data to CSV. Then have a VBA macro which picks up the CSV file, and populates your template file. Its not the greatest process, but would simplify several parts of it.

Re: How to enable Autofilter AND Write protection in XLSX using DDE

AlanC — Thu, 15 Feb 2018 04:31:36 GMT

Excel/SAS has been my bread and butter for a long time. DDE is to be avoided at all costs. VBS is also really bad. These are very deprecated technologies.

How would I solve it?

I use .NET and read the Excel workbook as an object then make the changes. This is very easy technology using free sources. Look at using C# to read Excel then modify that.

You are running into an issue where there isn't a lot of sources because it is so old using the 2 technologies you mention. Also, a system upgrade could destroy them.

Ping me if you need some starting points on code but move away from DDE/VBS.

Re: How to enable Autofilter AND Write protection in XLSX using DDE

RW9 — Thu, 15 Feb 2018 08:56:43 GMT

Alternatively you could drop the whole proprietary file format altogether, and go with an open plain text file format like XML, with a plain text style file which renders the XML in the way you want. As you want write protection, this provides it and you can add filters, buttons and other such things. Not to mention its all plain text, so cross platform, future proof etc.