Excel97 to 2003 spreadsheet migration problem

[croydon]

We have a spreadsheet that was created in Excel 97. Our users are upgrading to Excel 2003. The workbook is password protected.

When it is opened in Excel 97 the main worksheet is displayed and the user can start working immediately.

However, when it is opened in Excel 2003 the user is presented with a message box requesting the password.

Obviously telling the user the password would have security implications.

I have searched a number of forums but cannot find a reason for this. Any suggestions to resolve the problem would be appreciated.

[Bryan Hessey]

We have a spreadsheet that was created in Excel 97. Our users are upgrading to Excel 2003. The workbook is password protected.

When it is opened in Excel 97 the main worksheet is displayed and the user can start working immediately.

However, when it is opened in Excel 2003 the user is presented with a message box requesting the password.

Obviously telling the user the password would have security implications.

I have searched a number of forums but cannot find a reason for this. Any suggestions to resolve the problem would be appreciated.

Hi,

I found the easy cure for '97 - 2003 conversions was to create a new book, select the first tab in the old book, Shift-select the last tab (to select ALL sheets,) edit, Move or copy, tick Copy and select 'New Book'.

there may be a little cleaning up to do afterwards, check the Named ranges Macros, etc, and SaveAs.

If you find something easier for your password, and have no other problems then ignore this, but experience shows that where a problem shows then others will become evident later.

Let me know how you go with this.
---

[mudraker]

It sounds like in Excel 97 their maybe a macro that unprotects the workbook when it is opened.

If this is the case then you need to change the Macro Security setting in Excel 2003 by going to Tools > Option > Macro Security > Select 2nd or 3rd option.

2nd option prompts user to allow macros to run
3rd option allows all macros to run.

Another option is to get a digital signature for the workbook.

[croydon]

There is a Workbook_Open subroutine that runs when the book is opened. This includes code to protect the main worksheet and re-renters the workbook password in the code.

Later, when the user tries to enter or delete a row it checks that the book password is correct against the same password.

The problem as I mentioned before is that in Excel97 it did not ask for the user for a password when the book was opened.

To try to overcome the problem in 2003, I have set the password to "" (empty) in Workbook_BeforeClose, and next time it is opened it does not ask for the password.

However, when I try to add a row, for some reason the password entered in Workbook_Open does not match that compared in the AddRow routine (although it is the same!). I don't understand this.

I tried copying the sheets from the 97 into a new 2003 book but the same problem occurred.

Thanks for your help.

[Bryan Hessey]

There is a Workbook_Open subroutine that runs when the book is opened. This includes code to protect the main worksheet and re-renters the workbook password in the code.

Later, when the user tries to enter or delete a row it checks that the book password is correct against the same password.

The problem as I mentioned before is that in Excel97 it did not ask for the user for a password when the book was opened.

To try to overcome the problem in 2003, I have set the password to "" (empty) in Workbook_BeforeClose, and next time it is opened it does not ask for the password.

However, when I try to add a row, for some reason the password entered in Workbook_Open does not match that compared in the AddRow routine (although it is the same!). I don't understand this.

I tried copying the sheets from the 97 into a new 2003 book but the same problem occurred.

Thanks for your help.

Hi,

have you re-typed both passwords? - copied one to paste as the other? - no Case errors? - no spurious spaces, hidden characters?

no other code that sets a different password?

---
with the 2003 copy, can you strip enough data out to leave just the problem code and post that here?
---

[croydon]

To double-check, I copied and pasted the password. I checked the project and there are no other passwords.

[mudraker]

Please post your macro code

[croydon]

mudraker, due to the nature of the business I cannot post the macro code.

However, I have found a workaround. I enter the Workbook.Password in the VBA code when the spreadsheet is opened and blank it when it is closed. When the user adds or deletes a row (there are buttons on the sheet for this) I use the stored Worksheet.Password to unprotect the sheet then protect it again after the action.

Not brilliant but it seems to work.

Thanks to everyone for their suggestions on this thread.