4

I have a password protected Excel (XLSX) file that I would like to Import into Mathematica. Is there a hidden option in Import to pass the password? Can this option take an Encrpyted password so that I don't have to store the password in the notebook in the clear?

Any suggestions on how to get the data into Mma without removing the password on the Excel file are welcome. However, I am unable to use any 3rd party packages and I am targeting Player Pro from a Standard (not Enterprise) Mma license.

Edmund
  • 42,267
  • 3
  • 51
  • 143

1 Answers1

2

Using NETLink we can come close to your specs.

Note that passworded.xlsx is the password protected file, the password is "peter" and notPassworded.xlsx is the temp copy of a non-protected file.

Needs["NETLink`"]
LoadNETType["System.GC"];

excel = CreateCOMObject["Excel.Application"];

excel[Visible] = True;
workbook = 
  excel@Workbooks@
    Open["C:\\Users\\peter\\Desktop\\passworded.xlsx", 0, 0, 5, "peter"];
  workbook@SaveAs["C:\\Users\\peter\\Desktop\\notPassworded.xlsx", 
    workbook@FileFormat, ""];

excel@Quit[];

GC`WaitForPendingFinalizers[]
GC`Collect[]

ReleaseCOMObject[workbook]
ReleaseCOMObject[excel]

SetDirectory["C:\\Users\\peter\\Desktop"];
Import["notPassworded.xlsx"]
DeleteFile["notPassworded.xlsx"]

Now where I am stumped is that this seems to produce a memory leak on my computer win 10 pro // i76600

Would love suggestions and solutions to this.

Sources

Open

Save As

Some NETLink Formatting

A quick google for COM object memory leak finds this is common:

COM Leak

Community
  • 1
Peter Roberge
  • 2,357
  • 11
  • 19
  • This is very promising. (+1) I know Excel VBA quite well so I'm hoping that I will be able to use Range and a few others to get the data I need without having to save the file unprotected. I'll let it sit for a bit; as is customary. – Edmund Apr 13 '16 at 16:53