PDA

View Full Version : Write Reserved Error With Save As Macro



brandonb8
04-05-2007, 10:18 AM
About a month ago I got some help from Matt about making a macro that would allow me to save a file based on cell values and create the directories if they didnt exist. That post is the link below.

http://www.vbaexpress.com/forum/showthread.php?t=11572

Everything works well but I wanted to make the file read only unless the person knew the password. This was done by using the Save As/ Tools/General/ password to modify. Now my macro will not allow me to transfer or save as with the macro button giving me the write protected error.

If it is difficult to understand here is the scenario i would ideally want.

Data is entered to the workbook. It is then transferred to the network drive as write protected. Others can view as read only but only those with password can edit. The person (with the password) can copy that file to their local drive and update the data. It can then be resaved to the network drive as write protected.

I am able to do a "save over" by going to file/save as/ and pointing it to the network drive, but the macro doesnt work. It is probably as simple as entering a password string but I cannot find out where to put it at or exactly what it would say. Any help is appreciated.


Brandon

mvidas
04-05-2007, 10:30 AM
Hi Brandon,

I think it really depends on if you also have code that opens up the write-reserved workbook or not. Here is the code you'd use either way: 'when opening, provide password using WriteResPassword
Workbooks.Open "C:\yourfile.xls", WriteResPassword:="password"

'when using SaveAs, also provide password using WriteResPassword
ActiveWorkbook.SaveAs "C:\adslk.xls", WriteResPassword:="password"

'If you open a workbook with a writerespassword specified, a normal .save will
' continue having a write-reserved password until you saveas without oneMatt

brandonb8
04-05-2007, 12:27 PM
Im not sure I totally understand how this works. When I open the worksheet it asks me for a password to modify or to open as read only. I put in the password and it opens it up. I can edit the data and point it to the J: Drive and save it manually without it asking me for a password. I cannot, however get it to save to the local drive (D:) or transfer to the (J:) using my macro. It tells me that the file is write protected. I'm not sure I understand the c:"yourfile".xls because my saved location is based on a cell value. When I first open up the sheet (CoreSheet1.3) I can save to local and transfer to the J. Once it has been assigned a name based on that value (Name) I can no longer save it to the local using the button. I'm baffled as to what the difference is between initially saving it and trying to reopen it. Is there any way you can put that code inside that vba code you wrote a month or two ago? Thanks again for the help. I'm just dumb enough to be dangerous with excel.

mvidas
04-06-2007, 05:57 AM
Ok, sorry about this, I think I'm still a little confused...
First off, give this a try:ActiveWorkbook.SaveAs CheckMakeUNCPath("J:\Asphalt Core Data\" & _
Range("B6").Text & "\" & Range("B8").Text) & Range("B4").Text & ".xls", _
WriteResPassword:="password"
I just added the WriteResPassword argument to what I gave you before to save the file to your J drive, which should allow you to save it to your network drive and have the "please enter the password to modify the sheet or click read-only to open it read-only" prompt when opened.

What I think I'm confused about is when you're getting the error saying the sheet is write protected. When does this happen?

brandonb8
04-06-2007, 11:35 AM
Here is the deal. I put that code into the macro and it all worked fine. I am initially allowed to save the file to the local and the network drive. When I try to resave it to the local and network drive, it gives me the write reserved 1004 error. Its like it will not allow me to update the sheet once it is initially saved because it is now "write reserved." I can go to the file menu, save as, and save it to the local and network drive by pointing it there. It does not prompt me for a password or anything. Im not sure why there is a difference between the macro saving the file and manually pointing it there.

PM me with an email address and I can email you the sheet. I'm not sure if you have a d drive, but you could edit the macro to save to the c: drive and duplicate the error by clicking on the save to local. If there is an easier way to make the file read only unless a password is known then I am all for it. I just want to have the file available for read only to some and full access to others. I cannot control the security settings on the folder it saves to because it is a government folder.

mvidas
04-09-2007, 11:58 AM
Hi,

Sorry for the delay, been a crazy day so far.. you could try emailing me (mvidas <at> hastalavidas <d> com) but my webmail occasionally has issues with attachments (it has plenty of issues! only occasionally with attachs :shifty: )

I'm guessing the issue has to do with the order/method of opening and saving, but testing it would be great. If you didn't want to email it, you could probably create a dumbed-down version of your workbook and attach it here (avoids any confidentiality issues too, though if you email me I'll just test it and delete it after). Let me know if you send it and I can take a look for it.
Matt