Excel Sensitivity Label - how can I improve my solution?

[TomaszSadlon]

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:

1. 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
   

2. The macro, passed in the macro variable, looks like this (Label properties are xxx'd out for sec purposes, of course 😉 ):

   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

   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*

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.PRIVILEGED

My 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 If

This, 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
------------------------------

[PvD_SE]

Hi T,

As a workaround, would it be feasible to not create a new document but instead open an existing template with the sensitivity label already set, populate that and then do a 'Save as'? The template can either be totally blank or contain template text, as long as the sensitivity label has already been assigned in it.

------------------------------
Happy coding!
---------------
Paul
Sweden

(By all means, do not mark this as the 'Best answer'! )
------------------------------

[Anton__Hosang]

Try getting rid of any reference to the Office dll. VB has the  implicit typing/infer option set in BP, so try declare the Sensitivity & labelInfo variables as Object and hopefully their COM implementations stand up (some members can be mangled such as some sendermail stuff in Outlook, and the removeDuplicates method in Excel).

Put things back as they were, and can't you just use the value of the enum?

.AssignmentMethod = 1       'MsoAssignmentMethod.PRIVILEGED = 1

On another point wouldn't it be better to return error strings after catch and have the exception logging for all your VBO calls in a separate page?

I doubt BP will address this as their VBO is meant as a starter codeset - and very much needs your own considered enhancement.

------------------------------
Anton Hosang
Director
Omega RPA Ltd
------------------------------

[Athiban_Mahamathi]

Hi Tom,

There is an asset for M365 - Excel in DX but unfortunately it doesn't have assign sensitivity label action. You need to create the custom action to assign the sensitivity label. Refer the below Graph API documentation to create the custom action.

DX Asset : Microsoft 365 - Excel - 1.5.0

Graph API : Graph API - assignSensitivityLabel

------------------------------
Athiban Mahamathi - https://www.linkedin.com/in/athiban-mahamathi-544a008b/
Technical Consultant,
SimplifyNext PTE LTD,
Singapore
------------------------------

[John__Carter]

I would go with Paul's suggestion to use a template.

• Create an Excel file manually with all the structure, formatting, formulas, sensitivity etc you need.
• Import it into the initial value of a BP binary data item.
• Design your process to write the binary data item out to create a new file.
• Open the file, add data or whatever you need to do.
• Save and close.

------------------------------
John Carter
Blue Prism
------------------------------

[ewilson]

@Athiban Mahamathi Mathialagan,

Glad you pointed this out. For the longest time Microsoft only supported this under the beta release of the Graph API. Now that it shows up as part of the officially supported 1.0 release, we will look to add it to the connectors we have on the DX. 😉

Cheers,

------------------------------
Eric Wilson
Director, Integrations and Enablement
Blue Prism Digital Exchange
------------------------------

[LeonardoSQueiroz]

Hi Tomaz,

I normally use the solution proposed by Paul and I haven't had any problems with it, it works perfectly.

The only point of attention is to keep the file protected in the original directory and instruct the team to copy it and not write to it directly.

Regards

------------------------------
Leonardo Soares
RPA Developer 
América/Brazil
------------------------------

[Anton__Hosang]

@TomaszSadlon I quickly knocked up an action to use this property as it hadn't been in use for me before.

Works for me as simple as said above.

Take out any referencing in the Initialize page re office dlls and declare as Objects. Then you simply need to use the Int value:

.AssignmentMethod = 1

In general, when trying to convert VBA code to VB.NET, just use the Integer value of any Enums (find by hovering over a started routine in VBA editor, or look it up on the docs page)

------------------------------
Anton Hosang
Director
Omega RPA Ltd
------------------------------

[TomaszSadlon]

Hey Anton,

I tried doing this before and failed... Maybe you could point out my mistake 🙂

This is the code stage:

	Dim xlApp as Object = GetInstance(handle)
	Dim xlwb as Object = GetWorkbook(handle,workbookname)

	Dim sensLabel As Object
	Dim labelInfo As Object
    
    If (xlwb.SensitivityLabel.GetLabel() = "") Then
        sensLabel = xlwb.SensitivityLabel
        labelInfo = sensLabel.CreateLabelInfo()

        With labelInfo
            .AssignmentMethod = 1
            .LabelId = "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx"
            .LabelName = "xxxxx"
            .SiteId = "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx"
        End With
    sensLabel.setLabel(labelInfo, labelInfo)
    End If

Here are the references:

And this is the error I'm getting:

Internal : Could not execute code stage because exception thrown by code stage: Overload resolution failed because no Public '=' can be called with these arguments:
    'Public Shared Operator =(a As String, b As String) As Boolean':
        Argument matching parameter 'a' cannot convert from '__ComObject' to 'String'.

Any idea what am I missing?

------------------------------
Tomasz Sadlon
RPA Dev
Nordea AB
Europe/Warsaw
------------------------------

[Anton__Hosang · Accepted solution]

The GetLabel call results in a LabelInfo (COM)Object

In VBA this is an Object that will auto-resolve to a string(in fact the LabelId property of the LabelInfo Object)

Here you either try to explicitly call .ToString() on the end - if that does give the same outcome as VBA

If xlwb.SensitivityLabel.GetLabel().ToString() = ""

OR better yet, go for the known property that you want to test(which is LabelId I assume):

If xlwb.SensitivityLabel.GetLabel().LabelId = "" Then

------------------------------
Anton Hosang
Director
Omega RPA Ltd
------------------------------