Difficulty connecting to Excel 2007 password spreadsheet via Powershell

Question

I don't (and can't) have Excel installed on a system that needs access to an Excel worksheet. Per what I've read, accessing it as a database is dramatically faster.

I actually have Office 2012 installed on the system I'm developing on; I'm aware that I'll need to install the Office 2007 Data Access components on any system that runs this script.

I'm finding conflicting info on the web (shocking!) and I'm wondering if I'm missing something obvious. The code is straightforward:

$strFileName   = "C:\Users\[my directory]PWS20130821.xlsx"                                                                                                                                                                          
$strProvider   = "Provider = Microsoft.ACE.OLEDB.12.0"                                                                                                                                                                                   
$strDataSource = "Data Source = $strFileName"
$strExtend     = 'Extended Properties = "Excel 12.0 Xml;IMEX=1;HDR=No"'
$passwd        = get-ssp
$strPass       = "Jet OLEDB:Database Password = $passwd"   
$strConn       = "$strProvider;$strDataSource;$strExtend;$strPass"
$objConn       = New-Object System.Data.OleDb.OleDbConnection($strConn)                                                                                                                           
$objConn.open()

Which gives me "External Table is not in the expected format." My connection string when invoking the $objConn is

Provider = Microsoft.ACE.OLEDB.12.0;Data Source = C:\Users[my directory]\PWS20130821.xlsx;Extended Properties = "Excel 12.0 Xml;IMEX=1;HDR=No";Jet OLEDB:Database Password = [removed, but correct]

Removing the IMEX and HDR have no impact. Putting the password in quotes (there are no spaces or special characters) has no impact.

I did find this: "I'm afraid. You cannot open a connection to a password-protected spreadsheet unless you have already manually opened the spreadsheet in Excel . The described error is with the Excel ODBC provider, but the behavior is identical in the Jet 4.0 OLE DB provider. Your other option is to remove the password from the spreadsheet, and rely on some other security mechanism (like restricting permissions on the folder where the file resides) to control access."

Is this still accurate?

So does this method work in your environment for a workbook that does not have a password? I am curious if it is a broader Excel problem or something peculiar with the password. — Goyuix
I verified that if the excel worksheet is open - this code works. I then checked your suggestion - if the worksheet isn't protected, this code works. — Lee

Goyuix Goyuix · Accepted Answer · 2014-01-01T00:28:30

It looks like the situation you describe is not supported in the current Excel ADO drivers:

From: http://support.microsoft.com/kb/211378

This problem may occur if all of the following conditions are true:
You are using the Microsoft Excel ODBC driver.
-and-
The workbook for which you are creating the data source is protected by a password.
-and-
The workbook for which you are creating the data source is not currently open in Microsoft Excel.

The proposed solutions from the KB article suggest either removing the password or having it open (and presumably unprotected) in Excel prior to executing your script or to remove the password. Additional information can be obtained from this knowledge base article as well: http://support.microsoft.com/kb/257819

Security Consideration: Protecting workbooks/worksheets in an .XLSX file simply adds a workbookProtection or sheetProtection tag that is easily removed from the file. In other words, it protects against casual perusal/modification of a file but really does nothing to encrypt and protect the document as a whole and is easily bypassed. With that in mind, is having a password protected file the right answer anyway? Maybe remove the password first and store in a secure location for processing?

References:

Hack into a protected Excel 2007 Sheet

Hack into a protected Excel 2007 or 2010 Workbook

Difficulty connecting to Excel 2007 password spreadsheet via Powershell

1 Answers