Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
copying data into a file with a modify pw & read-only recommended
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." |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
copying data into a file with a modify pw & read-only recommended
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." |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
copying data into a file with a modify pw & read-only recommen
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." |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
copying data into a file with a modify pw & read-only recommen
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." |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Read-Only Recommended | Excel Discussion (Misc queries) | |||
Change Read-Only Recommended Prompt | Excel Programming | |||
Articles recommended to read, where can they be found? | Excel Worksheet Functions | |||
How to set Read-Only Recommended as default save in Excel 2002? | Excel Discussion (Misc queries) | |||
Recommended Read-Only Property | Excel Programming |