02-13-2018 02:44 AM
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?
02-13-2018 04:00 AM
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.
02-14-2018 11:31 PM
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.
02-15-2018 03:56 AM
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.