Detect if Excel file has IRM (Information Rights Management) Restrictions using VBA without opening the file

Question

I have an application written in Microsoft Access using VBA which opens Excel files, processes the contents in various ways and then closes them. If the a file cannot be opened, then I need to detect this and skip the file otherwise the application effectively freezes.

The Excel files come from numerous sources and if they are restricted I don't have the account credentials to open them.

With a password protected file I can supply an incorrect password, detect the error and then skip the file.

Application.Workbooks.Open(FileName, False, , , "xxxx", , True)

If the Excel file has had IRM (Information Rights Management) Restrictions applied to it, then when you open the file in the Excel application you are prompted to sign into Excel with an account that has permission to open the file.

If you try to open the file using the VBA code above with the Excel application not visible, then the process just halts and no error is generated.

What I need to do is either detect that the file has IRM applied to it before trying to open it or try to open it and generate an error that I can detect.

Thanks in advance for any help in solving this.

question from:https://stackoverflow.com/questions/65905721/detect-if-excel-file-has-irm-information-rights-management-restrictions-using

Answer 1

After trying a number of approaches to this I found that restricted files contain a string that indicates that they are secured with rights management.

So if you open the file and examine the contents you can detect if it's restricted without trying to load it into Excel.

This function works with all the files that I have tried it with.

Function IsFileRestriced(FileName As String) As Boolean

   Dim lngFile As Long, lngPos As Long
   Dim strContent As String

   'Open the file in binary mode
   lngFile = FreeFile
   Open FileName For Binary As lngFile

   'Read the whole file into a string
   strContent = Space$(LOF(lngFile))
   Get #lngFile, , strContent

   'Check if the file has the rights management string
   lngPos = InStr(1, strContent, "Microsoft Rights Label")

   'Close the file
   Close #lngFile

   'Return the result
   If lngPos > 0 Then IsFileRestriced = True

End Function