BookmarkSubscribeRSS Feed
LucasD
Calcite | Level 5

I create stored processes for my business partners to execute via SAS E.G. / Add-in for Microsoft Excel (AMO). My business partners generally execute a report I've written in SAS E.G. through Excel where they can view the data on-demand versus waiting for my team to provide it.

This generally works out great, but there is a new requirement I'm trying to satisfy and I'm not entirely sure how to go about resolving it. The management of my business partners has asked that we protect/lock the cells that the data is output to. The output is predictable, so I can use something like R1C1:R70C3 to protect the sheet.

I believe this can be done using DDE, but I'm not entirely sure. The users would be connected to our server at work via their SAS metadata profile which I believe would have an impact. Admittedly, I'm not that familiar with DDE, but basically I'm just trying to lock a row/column combo each time the program is execute.

Here's code that I can use through Excel, I'm just not sure how to integrate this into my SAS program.

Sub ProtectActiveSheet()Worksheets("Sheet1").Protect UserInterfaceOnly:=True
End Sub

 

 

2 REPLIES 2
RW9
Diamond | Level 26 RW9
Diamond | Level 26

I would definitely not use DDE - it is well over 20 years out of support.  So first off, why the locking of the cells?  It is just an output is it not, they don't have any read privilege backwards so it doesn't matter?  

Not familiar with the AMO, how do you generate the file, or send the data across?  Does that feature allow you to send VBA code, or can you run an intermediary step on the created file before presentation?  I suspect that you just have a pipe from data you derive to the Excel file they have open, in which case I don't see any method of locking things unless the file they open on their end already has the locks in place.  I.e. have a template that they open, which contains all the locks, and the button to pull the data in.  However I also suspect that if the cells are locked, then its unlikely the process would write to them either.  Anyways, to me the point is moot, either they want a real time view on the data in a file they can take away and do what they want with, or you go to a harder to edit file type like pdf and provide that.  Excel really isn't a good tool for any purpose.

LucasD
Calcite | Level 5

Excel is just a means for non-tech business partners to get their data. They use this data to work on 'items out of a queue'. The thing is, they can change the data, so the 'items in their queue' are easier to work. In order to mitigate this risk, we need to have the stored process we're executing, through AMO, to become locked after the data is populated. You are correct, I tried a template with a lock on it, and it wouldn't allow data to be written by the add-in. Still trying to determine a solution.

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!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

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
  • 2 replies
  • 1227 views
  • 0 likes
  • 2 in conversation