SAS AMO Add-In, Stored Process Execution, and VBA

[LucasD]

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

[RW9]

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]

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.