Hey guys,
This is gonna be a pretty long post 😉
So lately my organization introduced mandatory sensitivity labels in Office 365 documents. We're unable to save any newly created document unless we set the label during saving (if no label is already present, of course). This is not a functionality supported in Excel VBO, so I started investigating the subject to come up with a way to implement that.
There is, of course, the Microsoft Information Protection VBO, but there are some downsides to it:
- As far as I can see, it works only on already saved documents (so it won't work on a freshly created instance of Excel, since we're unable to save it first)
- It needs to be specifically implemented in all of the processes (which we have hundreds of, and would take a lot of time and work)
What I already have:
For now, this is what I came up with as a quick workaround:
- I've edited the Excel VBO, so that right before attempting to save the document it launches the following code stage. Basically it just inserts a VBA module with a macro, invokes the macro and cleans up after itself.
Dim xlApp as Object = GetInstance(handle) Dim xlwb as Object = GetWorkbook(handle,workbookname) xlApp.DisplayAlerts = False if xlwb.SensitivityLabel.getlabel().LabelId = "" Then 'add vba module Dim vbamodule = xlwb.VBProject.VBComponents.Add(1) 'inject macro into the module we created vbamodule.CodeModule.AddFromString(macro) 'run macro to change the sensitivity label xlApp.Run(macro_name) 'erase our module xlApp.VBE.ActiveVBProject.VBComponents.Remove(vbamodule) End If xlApp.DisplayAlerts = True -
The macro, passed in the macro variable, looks like this (Label properties are xxx'd out for sec purposes, of course 😉 ):
So basically we have the ability to set the label using a macro before saving the document and all is well in the world :)... *huge "BUT" incoming*Sub setLabel() On Error GoTo saveError Dim label As SensitivityLabel Dim labelInfo As Office.labelInfo If (Application.ThisWorkbook.SensitivityLabel.GetLabel() = "") Then Set label = Application.ThisWorkbook.SensitivityLabel Set labelInfo = label.CreateLabelInfo() With labelInfo .AssignmentMethod = MsoAssignmentMethod.PRIVILEGED .LabelId = "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxx" .LabelName = "xxxxxxx" .SiteId = "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxx" End With label.setLabel labelInfo, labelInfo End If Exit Sub saveError: Dim fs, f, currentdte Set fs = CreateObject("Scripting.FileSystemObject") Set f = fs.OpenTextFile("C:\temp\sensitivityLabel.log", 2, True) f.WriteLine CStr(Now) + " - " + Err.Description f.Close End Sub
This solution works for the most part. Not sure why, but in certain processes, under some circumstances, which I'm unable to pinpoint, this solution gives out an error:
Could not execute code stage because exception thrown by code stage: Can't perform operation since the project is protected.Plus, it just seems... Hacky? Can't really say, that this is a stable, production-ready solution. At least not to my standards as a dev 😉 I feel we can do better here.
What I'm after:
I'm attempting to implement the whole thing as a code stage with no macro-injecting-hope-it-works-hoop-jumping.
The main issue here is this line of the macro code:
.AssignmentMethod = MsoAssignmentMethod.PRIVILEGEDMy research shows, that it's connected to the Microsoft Office 16.0 Object Library, thus there's no problem to call it via VBA. Soo, I should be able to reference the MSO.dll in the Excel VBO's Initialize page and use it in my code, right? Well, no. The moment I add mso.dll to the references, this happens:
So far I also had no success in using the Microsoft.InformationProtection.dll to create a valid AssignmentMethod object.
So, my questions are:
1) How do you guys handle the Sensitivity Label in your work envs?
2) Do you have any idea how can I improve my solution to be more bullet-proof (in case of my workaround solution) or work at all? (in case of my "What I'm after" solution)?
3) Does BluePrism, as the provider of the Excel VBO, has any plans to actually address this problem (which is not exactly new, according to my findings)?
SOLUTION:
In case anyone's looking for a solution, here's the code stage (no additional references required):
Dim xlApp as Object = GetInstance(handle)
Dim xlwb as Object = GetWorkbook(handle,workbookname)
Dim sensLabel As Object = xlwb.SensitivityLabel
Dim labelInfo As Object = sensLabel.CreateLabelInfo()
If (sensLabel.GetLabel().LabelId() = "") Then
With labelInfo
.AssignmentMethod = 1
.LabelId = "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx"
.LabelName = "xxxxxx"
.SiteId = "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx"
End With
sensLabel.setLabel(labelInfo, labelInfo)
End IfThis, of course, sets a given label just in case of lack of a set label in the workbook. You can modify/delete the If statement to alter this behavior 🙂
------------------------------
Tomasz Sadlon
RPA Dev
Nordea AB
Europe/Warsaw
------------------------------
