How to programmatically set Excel Sensitivity Labels using Python?

Question

Recently our IT dept has applied a policy where we have to specify Sensitivity Label in Excel. This caused a disruption to my automated py codes to generate personalised excel files.

Besides using Excelwriter to write into the file, i also use win32com to password protect the individual files with random password. Whenever win32com opens the file, it is prompted to select the sensitivity label which it cannot get around and thus unable to proceed further.

Is there a way to programmatically default select 'confidential' as the sensitivity label?

following is briefly how i generate the files

while rc < idcount: 
        var_id= df['idnum'].iloc[rc]
        var_password = df['password'].iloc[rc]
        main_df = df[(df['idnum'] == var_id)]
        
        wsname = 'testsheet'
        staff_file = pd.ExcelWriter(dummyloc + 'Dummy.xlsx', engine='xlsxwriter')
        main_df.to_excel(staff_file,wsname,startrow=4,index=False)

        workbook = staff_file.book
        workbook.set_vba_name('ThisWorkbook')
        workbook.add_vba_project(dummyloc + './vbaProject.bin') #i added a macro for some internal controls.
        staff_file.save()
        staff_file.close() 

        excel = Dispatch('Excel.Application')
        wb = excel.Workbooks.Open(dummyloc +  'Dummy.xlsx') 
        excel.Worksheets[wsname].Activate()         

        excel.ActiveSheet.Protect('ABCD',True ,True ,True ,False ,False , False ,                                  False , False , False , False , False ,False , True , True , False)
        wb.SaveAs(Filename = dummyloc + 'Excel\\' + 'Dummy1',FileFormat= 52,Password=str(var_password))
        wb.Close()

        rc = rc+ 1

I have also found a suggested solution but somehow. It is supposed to execute a vba macro to inject the password. However, maybe i didnt code it correctly, it didn't change anything

def set_password(excel_file_path, pw):

from pathlib import Path

excel_file_path = Path(excel_file_path)

vbs_script = \
f"""' Save with password required upon opening

Set excel_object = CreateObject("Excel.Application")
Set workbook = excel_object.Workbooks.Open("{excel_file_path}")

excel_object.DisplayAlerts = False
excel_object.Visible = False

workbook.SaveAs "{excel_file_path}",, "{pw}"

excel_object.Application.Quit
"""

# write
vbs_script_path = excel_file_path.parent.joinpath("set_pw.vbs")
with open(vbs_script_path, "w") as file:
    file.write(vbs_script)

#execute
subprocess.call(['cscript.exe', str(vbs_script_path)])

# remove
vbs_script_path.unlink()

return None

score 2 · Answer 1 · answered Aug 23 '22 at 13:47

Just faced a similar issue with an excel 365 update in my workplace. In short, what I managed by surfing the web is a fast VBA function that gets the sensitivity label from an already labelled file and then assigns it to all the files I need.

Forgive me for putting VBA code in a Python question, but since you are using win32com, the adaptation should be very straightforward.

This is the function I wrote:

Sub put_label()
    'Puts sensitivity labels copied from active workbook to a list of files.
    Dim ex_lab  'To store the label object
    Dim fs, f, archivos, curarch
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set f = fs.GetFolder(Range("C2").Value2)
    Set archivos = f.Files  'List of files to be labelled
    
    'This line gets the label object from the already labelled file.
    Set ex_lab = ActiveWorkbook.SensitivityLabel.GetLabel()
    
    'The label is applied to all the files.
    For Each curarch In archivos
        Workbooks.Open curarch.path, False
        'ActiveWorkbook is now the just opened workbook
        ActiveWorkbook.SensitivityLabel.SetLabel ex_lab, ex_lab
        ActiveWorkbook.Save
        ActiveWorkbook.Close False
    Next
   
   MsgBox "Done"
    
End Sub

So, from what you have, you could use your excel_object and directly refer to workbook object and so on, since the methods usually look the same as in VBA. Only be careful that when using it in Python, all the functions go with (). So for example, VBA's:

ActiveWorkbook.Close False

Shall be in Python code something like:

excel_object.ActiveWorkbook.Close(False)

Also, the Set statement is VBA's way to declare objects instead of builtin variables; not necessary in Python.

Sometimes there's some case sensitivity issues, but I suppose you already have experience using VBA from Python throught the COM object.

Hope this helps, been all the morning worried about this problem :)

I ran into 'Object variable or With Block variable not set' error message when I tried this. I like the idea of taking the label from a known good file and applying it to the next file where I need it, but I am unable to get this working from your example. The ex_lab object appears to be empty. — MarkF, Oct 21 '22 at 17:50
I found that I had to dim ex_lab as Office.LabelInfo object, which also required adding Microsoft Office in References. That fixed it for me. — MarkF, Oct 21 '22 at 18:17

How to programmatically set Excel Sensitivity Labels using Python?

1 Answers1