SaveAs CreateBackup - remove password protection, SaveAsUI

Hi,

I have a Tracker workbook that opens another workbook (PT, as master copy), copies across some details, creates an appropriate directory and saves the PT workbook under another name (client and case number).

The PT workbook requires a password to open, has its own macro and has SaveAsUI 'disabled'. The population and general control of the PT workbook is handled by the Tracker workbook - but the user has the ability to 'stamp' the PT workbook to update the status of a process.

So far this all works fine. The trouble comes when I try and save a copy of the PT workbook to another location (different drive share, for a different audience). I want this PT Copy to be updated (overwritten) each time the workbook is saved to this secondary location. Sometimes prompted by the Tracker macro sometimes by the user directly - it doesn't make any difference.

I have used SaveCopyAs in the past but it won't work in this case as I need to remove the password to open file and prevent the user from running any macros. I've tried using SaveAs and CreateBackup but it doesn't behave how I would expect / need, and haven't been able to find out much on the interweb-googly-whatsit.

This is the code I currently have in the PT workbook:

Please Login or Register  to view this content.

When this code is executed the Recs copy is saved but retains password protection and the Command Button (StampExit1) is still visible - exactly the same as the normal working version.

Am I barking up the wrong tree here? I tried going down the route of copying the sheet contents to another workbook (but sometimes there are more sheets than one) and it's a pain to change the colour pallette, formatting, delete hidden rows & columns etc, etc. It just feels so clunky and wrong.

I'm after SaveCopyAs functionality but with the password removed, the command button hidden and the 'main working copy' of the file to remain open for the user.

Is this possible? Feel like I'm missing something here...

Thanks as always, TC

Sorry, it is a bit complicated. Basically I want to save a copy of a file without a password, macro's non-triggerable to a different location and save the 'main working' version as is - Like SaveCopyAs does.

I don't want to disable events as I want it to still save the 'main working' file at the end (after BeforeSave) and leave it for the user to continue working in.

Am I missing the point?

Okay. Well it doesn't appear to get stuck in a loop when I step through it, nor when I run it normally - but I'm well aware of how little I know so added events disable and saved all within BeforeSave.

Doesn't fix my problem though - I need a SaveCopyAs but remove PW to open. What if I saved the file with no password, then did a SaveCopyAs, then saved it again with the password to open? Hmmm. Off to give it a try. Still feels a bit clunky. I'm not a fan of clunky.

So any ideas what CreateBackup:=True does? I couldn't find anything on this other than it's optional and boolean. I'm guessing my interpretation is wrong...

Okay, so that didn't work at all like I was expecting.

I did however find a workaround to achieve what I was after, it's a bit messy and requires multiple saves, but was the only way I could think to achieve what I wanted.
So here's the bit of code I'm using that:

• hides the macro command button
• saves the file without a password
• saves a copy to an alternative location
• unhides the macro command button
• saves the file with the password to open

This gives me a copy of the file in an alternative location that can be opened without a password but the macros cannot be triggeres (everything is protected and can only be altered via macro):

Please Login or Register  to view this content.

N.B. Application.EnableEvents set to false at start and true at the end

Hope this makes sense to anyone passing by.

Thanks, TC