Question
OpenSpan 7.1 How to open password protected Excel workbook
Is it possible to open a password protected Excel workbook using MicorsoftExcel Connector ?
If it is possible how can I do it?
Chunzhi
***Updated by Moderator: Marissa to update categories***
**Moderation Team has archived post**
This post has been archived for educational purposes. Contents and links will no longer be updated. If you have the same/similar question, please write a new post.
Accepted Solution
Hi Chunzhi,
Here is how you would do open a password protected worksheet. It is important to note that Excel must be running first for this to work. If you load an empty spreadsheet to start with that will do the trick like this.
Then open your sheet - obviously this has some hard coded values which you may choose to input using variables. Create the _ApplicationProxy1 and the workbooksProxy1 by extracting a proxy from ExcelApplication and Workbooks.
The script to provide the Missing value object is:
Chunzhi,
The excel connector does not have a build in function or method that allows you to unlock an excel workbook. However, I would suggest creating a script to do so. You should be able to pass the workbook/worksheet into the script through automation and use the following method call to unprotect the worksheet:
sheet1.unprotect(Password);
The following link describes how to iterate through a list of worksheets through automation. What's really useful about this link is that it also shows what references should be needed to work with Excel through script.
http://help.openspan.com/71/Components/How_to_Use_the_Script_Component.htm
Accepted Solution
Hi Chunzhi,
Here is how you would do open a password protected worksheet. It is important to note that Excel must be running first for this to work. If you load an empty spreadsheet to start with that will do the trick like this.
Then open your sheet - obviously this has some hard coded values which you may choose to input using variables. Create the _ApplicationProxy1 and the workbooksProxy1 by extracting a proxy from ExcelApplication and Workbooks.
The script to provide the Missing value object is:
Hi Jeff,
I was able to open a password protected excel using above reference, but how to use "GetCellValue" from a WorkbooksProxy? When I use MicrosoftExcel1-GetCellValue, its picking up from Empty.xlsx and not from PasswordTest.xslx file...
Thanks,
Arun
Hi arun,
You found answer for above question,if found please share the answer
I was able to open a password protected excel using above reference, but how to use "GetCellValue" from a WorkbooksProxy? When I use MicrosoftExcel1-GetCellValue, its picking up from Empty.xlsx and not from PasswordTest.xslx file
Since I need this information too, have created the following thread:
https://collaborate.pega.com/question/how-setget-cell-values-password-protected-excel-workbook
Hello!
I believe you have asked this question as a new post: Excel Component - Text to Columns Property
Please continue the discussion in the new post.
Thank you,