4
$\begingroup$

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.

$\endgroup$
2
  • $\begingroup$ Can you use NETLink with Standard Player Pro? $\endgroup$ Apr 12, 2016 at 21:18
  • $\begingroup$ @PeterRoberge: with the standalone Player Pro you can indeed use NETLink, see e.g. player-pro $\endgroup$ Apr 12, 2016 at 22:56

1 Answer 1

2
$\begingroup$

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

$\endgroup$
1
  • $\begingroup$ 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. $\endgroup$
    – Edmund
    Apr 13, 2016 at 16:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.