Question on Excel and passwords.

[vdennis]

Hey gang. Has anyone had any luck with being able to pass a password when opening or saving an excel file? I am having progress create an excel file that at the end of the process it is proteced, (working) and set a password for it. Trying to avoid having the user set the password before sending it along to the next person in the loop. Thanks! -Dennis-

 

[Rob Fitzpatrick]

What have you tried? Can you share a code snippet?

 

[vdennis]

Usually I will use a marco to copy the key strokes. Here is what I use to protect the worksheet (Normal closed code removed.)
hWorkSheetrotect.

When use protect a worksheet in Excel, it does ask for password or you can leave it blank, but the Macro only return the following:
/******** Macro from Excel *************/
Sub MakePassoword()
'
' MakePassoword Macro
'
'
ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
False
End Sub
So there is no clue to any password object to try.

I might try tagging hExcel:workbooksassword = xxxxx but I don't think it would work.

 

[Stefan]

http://msdn.microsoft.com/en-us/library/ff821529(v=office.14).aspx

Set the Password property on your workbook (the rest of the example copied from an earlier example):

DEF VAR chExcel AS COM-HANDLE NO-UNDO.
DEF VAR chWorkBook AS COM-HANDLE NO-UNDO.
DEF VAR chWorkSheet AS COM-HANDLE NO-UNDO.
 
DEF VAR cexcelname AS CHAR NO-UNDO INIT "c:\temp\excel".
DEF VAR iworksheets AS INT NO-UNDO INITIAL 1.
DEF VAR ii AS INT NO-UNDO.
 
SESSION:SET-WAIT-STATE("GENERAL").
 
CREATE "Excel.Application":U chExcel.
ASSIGN chExcel:VISIBLE = NO.
chExcel:displayAlerts = FALSE.
cExcelName = cExcelName + STRING(TIME).
chWorkbook = chExcel:Workbooks:Add().
 
chWorkBook:Password = "verysecret". /* here */
 
chWorkBook:SaveAs(cExcelName,56,,,,,).
ASSIGN chExcel:displayAlerts = YES.
chWorkBook:CLOSE().
chExcel:QUIT.
 
RELEASE OBJECT chWorkBook.
RELEASE OBJECT chExcel.
 
SESSION:SET-WAIT-STATE("").

 

[vdennis]

Thanks for the info Stefan. Sorry to say that in this case we only want to set a password on the sheet because we are locking and hiding cells. We create the sheet from a template, add data and lock those columns, hide the ones we don't want them to see, and send if off to them to fill in the un-locked cells. The wWorkSheetrotect works in protecting the sheet, but there does not appear a pathway to set a password on the sheet.
When trying hWorkSheetassword = "xxxxx" I get an unknow name error message for the method.
I do see the following:
Public Overridable Sub Protect ( _
<OptionalAttribute> Password As Object, _
But have not yet found the code as yet to use it.

I'll have to see if there is something in the COM Object Viewer on this.

Thanks,

 

[Stefan]

Aha - I was misled by the title of your post, sorry about that. Using the same MSDN reference I got to the Protect method on the worksheet. This results in the following:

DEF VAR chExcel      AS COM-HANDLE NO-UNDO.
DEF VAR chWorkBook   AS COM-HANDLE NO-UNDO.
DEF VAR chWorkSheet  AS COM-HANDLE NO-UNDO.
 
DEF VAR cexcelname   AS CHAR NO-UNDO INIT "c:\temp\excel".
 
cExcelName = cExcelName + STRING( TIME ).
 
SESSION:SET-WAIT-STATE("GENERAL").
 
CREATE "Excel.Application":U chExcel.
chExcel:Visible = FALSE.
chExcel:DisplayAlerts = FALSE.
chWorkbook = chExcel:Workbooks:Add().
chWorkSheet = chWorkbook:Worksheets(1).
chWorkSheet:Protect("supersecret"). /* <-- here */
chWorkBook:SaveAs(cExcelName,56,,,,,).
chExcel:displayAlerts = TRUE.
chWorkBook:Close().
chExcel:Quit().
 
RELEASE OBJECT chWorkSheet.
RELEASE OBJECT chWorkBook.
RELEASE OBJECT chExcel.
 
SESSION:SET-WAIT-STATE("").

 

[vdennis]

That's IT. I owe you a beer or what ever. I added that page to my Fav's .
-Dennis-