connect to protected excel workbook

[AzizKamal]

In an asp page, I call a function, ConnectXL with one argument. Argument is location/path of a macro-enabled (xlsm) excel file.

ConnectXL function is:

sub ConnectXL(filename)

Set cnn = Server.CreateObject("ADODB.Connection")
cnstr="Provider=Microsoft.ACE.OLEDB.12.0;"
cnstr=cnstr & "Data Source=" & filename & ";"
cnstr=cnstr & "Extended Properties=""Excel 12.0 Macro;HDR=YES"";"
cnn.Open cnstr

end sub

With this code, the connection opened successfully for the file LWP.xlsm. Then I protected the workbook LWP.xlsm as follows in Excel 2007:

Review -> Protect Workbook -> Protect Structure and Windows
Entered 123 as password, reentered 123 and clicked OK.

Now when ConnectXL reaches the following line:

cnn.Open cnstr

it generates the following error:
External table is not in the expected format.

I am unable to connect to the workbook after applying protect workbook password.

 

[foxbox]

In thhis article:

http://support.microsoft.com/kb/257819#Connect

i read;

If the Excel workbook is protected by a password, you cannot open it for data access, even by supplying the correct password with your connection settings, unless the workbook file is already open in the Microsoft Excel application. If you try, you receive the following error message:
Could not decrypt file.


That's strange, because you receive:
"External table is not in the expected format"


to me it sounds more like that LWP.xlsm is not compatible with the server Excel version. Your local machine has Excel 2007, the server too?

Save the file with a standard Excel extention.

https://connect.microsoft.com/SQLSe...cel-data-source-doesnt-allow-xlsm-connections

 

[AzizKamal]

Thanks foxbox.

There are two options of password-protecting the workbook in Excel 2007.

1. Review -> Protect Workbook -> Protect Structure and Windows
This will open the workbook but its structure will be protected; for example I cannot rename any sheet, I cannot insert new sheets...

2. Office button -> Save as -> Tools -> General Options -> Password to open. Now the workbook cannot even be opened without password.

I guess in Microsoft Article:

http://support.microsoft.com/kb/257819#Connect

the explanation is about option 2 whereas my issue is related to option 1.

For the example of LWP.xlsm that I explained earlier, my test application is on my own PC. I mean c:\inetpub\

http://wwwroot\applicationfolder

is on my own PC, and oracle database I am connecting to is on another machine. So my local machine has Excel 2007, the application server too has Excel 2007

 

[foxbox]

do a test with a protected test.xls
make sure you save it in Excel format and not XML.

 

[AzizKamal]

Thanks foxbox.

Here are my test results:

1. Created a file in Excel 2007 and saved it as test.xls (Save as type: Excel 97-2003 Workbook).
ConnectXL function opened this file successfully.

2. For test.xls, selected Review -> Protect Workbook -> Protect Structure and Windows and entered 123 as password.
ConnectXL function did not open this file and generate the following error:
Could not decrypt file.

3. Created a file in Excel 2007 and saved it as test2.xlsx (Save as type: Excel Workbook (*.xlsx)).
ConnectXL function opened this file successfully.

4. For test2.xlsx, selected Review -> Protect Workbook -> Protect Structure and Windows and entered 123 as password.
ConnectXL function did not open this file and generate the following error:
External table is not in the expected format.

 

[foxbox]

maybe this helps:

http://www.connectionstrings.com/Articles/Show/how-to-open-password-protected-excel-workbook

 

[AzizKamal]

Thanks foxbox.

I tried this but the script hangs up when GetObject function executes.

sub ConnectXL(filename)
Response.Write filename
end sub

Ouptput is:
c:\inetpub\

http://wwwroot\Payroll\UploadFolder\LWP.xlsm

sub ConnectXL(filename)
Dim xl
Set xl = GetObject(filename)
Response.Write "test"
end sub

and I am not getting the output test. Program remains on current page, does not go to next page…

 

[AzizKamal]

I could not achieve this through asp/vbscript. However, I managed to find a solution close to this through Excel VBA.

For LWP.xlsm file, I wrote the following in Excel VBA:

Private Sub Workbook_Open()
ActiveWorkbook.Protect Password:="123", Structure:=True, Windows:=False
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
ActiveWorkbook.Unprotect Password:="123"
End Sub

Now when the end-user opens Excel Workbook, it is opened as password-protected workbook (which is the requirement). But when asp reads this file, it gets unprotected workbook and hence reads the records successfully.

Interestingly, if I open LWP.xlsm and close it without doing anything, I get the prompt: Do you want to save the changes you made to LWP.xlsm? If I click No, even then asp reads LWP.xlsm successfully, which means Workbook_BeforeClose executes anyway whether I save the changes or not.