BookmarkSubscribeRSS Feed
berndJR
Calcite | Level 5

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

3 REPLIES 3
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

AlanC
Barite | Level 11

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.

 

 

https://github.com/savian-net
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 580 views
  • 0 likes
  • 3 in conversation