VB code nearly there

[c hassett]

Hi all,
I'm taking my first steps into VB code and had some success, until now.
I found some code on here for saving a workbook to multiple locations with the addition to the file name of the date

ActiveWorkbook.SaveCopyAs Filename:="O:\MRC\Development" & _
bdFileName & " BU " & Format(Now, " yyyy-mm-dd") & _
".xls"

And I recorded a Macro where I lock all the cells, protect the workbook and all the sheets and save that to another drive as a read only file with password to modify.

What I'd like to do is add to the recorded Macros generated file name Now " yyyy-mm-dd". Haven't worked out how, it gets to the part about password:="" and stops.

Ultimatly I want the the two as one so that it generates the BU with the Now first, then adds all the protection to the workbook and saves the read only version to the other drive.

I'm confident it can do it, I'm just not sure how to tell it to.
Thanks in advance,

 

[xenou]

As long as each routine works, you should be able to use them in turn.

To save as read-only you just want to set the argument in the SaveAs statement:

Sub Macro1()

    ActiveWorkbook.SaveAs Filename:= "C:\Alex.xls", WriteResPassword:="mypassword"
End Sub

The macro recorder sets the password to "" but this really means no password. The password argument refers to a password to open the file. the WriteResPassword argument refers to a password to open the file with write access (this is where you get the option to open read only).

For filename just build the string you need:

myString = "C:\Alex" & & Format(Now, "yyyy-mm-dd") & "_BU.xls"
ActiveWorkbook.SaveAs Filename:=myString

Don't get confused about the differences between Save, SaveAs, and SaveCopyAs...each acts slightly differently, but all need filenames and file locations. Also think about what Excel is doing with its location when you save in different places and try not to make inaccurate assumptions about where things are being saved or what is the active workbook.

Regards.