python - How to unlock a password protected excel file using R and VBA

Question

I have some password protected files received from my colleagues on a daily basis and I would like to unlock them programmatically so that I can read them do build my reports.

In Python I was able to unlock files with the win32com.client default package. How about R?

Here's how I did it in Python,

def unprotect_xlsx(filename, pwd):
    try:
        xcl = win32com.client.Dispatch('Excel.Application')
        wb = xcl.Workbooks.Open(filename, False, False, None, pwd)
        wb.Unprotect(pwd)
        wb.UnprotectSharing(pwd)

        xcl.DisplayAlerts = False
        wb.SaveAs(filename.split('.xlsx')[0]+"_unlocked"+".xlsx", None, '', '')
        xcl.Quit()
    except Exception as e:
        print("Error:")
        print(e)

标签： pythonrvbapasswordsunlock

Answer 1

However, if you are using R, you must have come acroos this RDCOMClient library but it is merely an interface to work with a variety of clients without exactly having a built-in function that can do the unlocking.

So I had a workaround. I used RDCOMClient to interface with an macro-enabled excel VBA file which does the unlocking.

I created the macro-enabled excel (macro_unlocker.xlsm), enabled the developer option and wrote this VBA code which will take in the filename and password from R and unlock the file as an Excel program,

Public Sub unlock_file(filename, pwd)
Dim wb As Workbook
Dim filename_path As String

filename_path = filename

'MsgBox filename_path & " " & pwd
Set wb = Workbooks.Open(filename:=filename, Password:=pwd)

save_filepath = Split(filename, ".xlsx")(0) & "_unlocked.xlsx"
wb.SaveAs filename:=save_filepath, Password:=""

End Sub

The RDCOMClient interfaces with the VBA by passing the arguments to the VBA script. It unlocked the file by setting the password as an empty string.

Then in R, I can write a function to pass the arguments to it. And this is what I have, I put this in an external R file called myUtils.R just to make things neater,

unlock_xlsx <- function(filename, pwd){
  if (!require("pacman")) install.packages("pacman")
  pacman::p_load(devtools)

  devtools::install_github("dkyleward/RDCOMClient")
  library(RDCOMClient)

  # Open a specific workbook in Excel:
  xlApp <- COMCreate("Excel.Application")

  xlWbk <- xlApp$Workbooks()

  xlWbk$Open(paste(getwd(), "/<utils folder>/macro_unlocker.xlsm",sep=""))

  # this line of code might be necessary if you want to see your spreadsheet:
  xlApp[['Visible']] <- TRUE

  # Run the macro:
  xlApp$Run("unlock_file", filename, pwd)

  # Close the workbook and quit the app:
  xlWbk$Close(FALSE)
  xlApp$Quit()

}

So finally to run the functions and the VBA macro in R so that I can unlock the files programmatically and do my work I wrote this in R,

# call in myUtils.R where the function unlock_xlsx resides
source(paste(getwd(), "./myUtils.R", sep=""))

# file to unlock
password_locked_file_FILEPATH <- Sys.glob(file.path("<sub-folder>", "<sub-folder>","<an_excel.xlsx>"))

# send the file_filepath to the function
file_to_unlock <- paste("../", password_locked_file_FILEPATH, sep="")
password_locked_file_pwd <- "<password>"

# result will be suffixed with '_unlocked' followed by '.xlsx'
unlock_xlsx(filename = file_to_unlock, pwd = password_locked_file_pwd)

Tada! The file is unlocked and saved with the suffix _unlocked. And you can definitely cycle through your files to be unlocked and provide the corresponding passwords and everything would work like clockwork.

This works as long you have R and Excel installed in your machine.

Note: I do understand there are some issues with the installation of RDCOMClient its not compatible with the latest R version 3.6. I had a workaround by installing dkyleward/RDCOMClient and it installed perfectly well on a Windows machine but failed on MacOS.