Problem with Opening Workbook

StrangeLuck · May 9th, 2002, 08:19 AM

Hello,

I have written a macro to automate some monotonous tasks. One of the workbooks opened by the macro is password protected. In the VBA of my macro, I open the workbook using the line
Workbooks.Open FileName:="filename.xls"
and enter the password at the prompt. No problem so far, hmm?

The problem occurs when changes have been made to this workbook by the macro and I want to save it and close it. Excel tells me now that the workbook is opened as "Read-Only" and I cannot save it.

Any suggestions?

Thanks,
StrangeLuck

Nibbles · May 9th, 2002, 08:55 AM

Hi,

Not sure if this will work as I cannot test it at the moment, but off the top of my head you could try this:

If ActiveWorkbook.ReadOnly Then
ActiveWorkbook.SaveAs fileName:="NEWFILE.XLS"
End If

If you set the filename as the name it already has, then it should write over itself with the new version. Check out 'ReadOnly' in the VBE help.

Nibbs

StrangeLuck · May 9th, 2002, 12:15 PM

Alas, the problem is that the macro does not close the password protected workbook, as the user must check over the workbook to make sure that the changes made to it are correct.

Still, when the workbook is opened by:Workbooks.Open FileName:="filename.xls", ReadOnly:=False
the file is still shown as being read-only.

What gives? Any helpers?

Thanks,
StrangeLuck

[ This Message was edited by: StrangeLuck on 2002-05-09 11:16 ]

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

May 9th, 2002, 08:19 AM	#1
StrangeLuck New Member Join Date: Mar 2002 Posts: 15	Hello, I have written a macro to automate some monotonous tasks. One of the workbooks opened by the macro is password protected. In the VBA of my macro, I open the workbook using the line Workbooks.Open FileName:="filename.xls" and enter the password at the prompt. No problem so far, hmm? The problem occurs when changes have been made to this workbook by the macro and I want to save it and close it. Excel tells me now that the workbook is opened as "Read-Only" and I cannot save it. Any suggestions? Thanks, StrangeLuck

May 9th, 2002, 08:55 AM	#2
Nibbles Board Regular Join Date: Apr 2002 Location: Surrey, United Kingdom Posts: 75	Hi, Not sure if this will work as I cannot test it at the moment, but off the top of my head you could try this: If ActiveWorkbook.ReadOnly Then ActiveWorkbook.SaveAs fileName:="NEWFILE.XLS" End If If you set the filename as the name it already has, then it should write over itself with the new version. Check out 'ReadOnly' in the VBE help. Nibbs

May 9th, 2002, 12:15 PM	#3
StrangeLuck New Member Join Date: Mar 2002 Posts: 15	Alas, the problem is that the macro does not close the password protected workbook, as the user must check over the workbook to make sure that the changes made to it are correct. Still, when the workbook is opened by:Workbooks.Open FileName:="filename.xls", ReadOnly:=False the file is still shown as being read-only. What gives? Any helpers? Thanks, StrangeLuck [ This Message was edited by: StrangeLuck on 2002-05-09 11:16 ]