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.
"rockhammer" wrote:
> 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.
>
>
> "JLGWhiz" wrote:
>
> > 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" wrote:
> >
> > > 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."
> > >
|
Similar Threads
