copying data into a file with a modify pw & read-only recommended

[rockhammer]

I have a macro in sourceFile which opens a destFile to copy data from
sourceFile into destFile and then tries to save the destFile. My destFile in
this case is one which has a modify password set and also has the open
read-only recommended option set.

What I am experiencing is that even though I enter the password when the
prompt comes up when the destFile is opened and I click "ok" instead of "read
only", the destFile is still opened as read-only.

My question is: Is there a way to modify my code such that the destFile will
NOT be opened read only when the proper password is entered when prompted?
Excerpts of my code below - it all works except that destFile is always
opened read only no matter what and therefore the line that saves the
destFile gives an error.

Thanks a lot.

' this is the file open sub
Sub threed_file_open()
fileOpened = False
Application.DisplayAlerts = False
Application.ScreenUpdating = False
If Not fileSelected Then
MsgBox "No 3D template file selected yet!"
Exit Sub
End If
sourceFile = ActiveWorkbook.Name
Workbooks.Open Filename:=sourceFileName, Notify:=False
fileOpened = True
destFile = ActiveWorkbook.Name
Windows(sourceFile).Activate
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub


' this is the code which does the copying & saving
Windows(sourceFile).Activate
topbot.Activate ' this is the tab in sourceFile I want to copy from
Range(topbotExport1stCell, topbotExportLastCell).Select
Selection.Copy
Windows(destFile).Activate
Worksheets(topbotImportTab).Activate
topbotImportCell.Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False
ActiveWorkbook.Save
MsgBox "Changes to " & destFile & " saved."

 

[JLGWhiz]

This code (copied from your post) indicates that the destination file is the
same as the source file, same workbook, so I guess I don't understand the
question.

sourceFile = ActiveWorkbook.Name
Workbooks.Open Filename:=sourceFileName, Notify:=False
fileOpened = True
destFile = ActiveWorkbook.Name

Is it the worksheet that is protected within the workbook?

 

[rockhammer]

Once the workbooks.open line executes, the activeworkbook becomes the opened
workbook so sourceFile <> destFile.

The destFile has a modify password set and also the open-read-only option
set via File->Save As->Options->General, etc.

What I am experiencing is that when destFile is opened via the vb code I
have, the normal popup that prompts for the password shows up. However,
unlike when I open destFile via File->Open, even if I entered the correct
password and click "ok" (not "read only"), destFile is still opened as
read-only. Therefore the line that saves the file generates a run-time error.

Thanks.

 

[rockhammer]

I just found the solution on the web. It involves setting
IgnoreReadOnlyRecommended:=True in the Workbooks.open line.

Once this option is set, the pop up that prompts for the password still
comes up. However, unlike before, entering the correct password and clicking
"ok", not read-only, will open the destFile WITHOUT read-only set to true.

I have also tested the case where I intentionally clicked "read-only" when
the prompt comes up. As expected, the files opens as read=only.

Thanks for your efforts. Hope this solution is useful.