Save as...

[Francis Hookham]

One of my spreadsheets has the following macro writen sometime ago with help
from a MVP:

Application.DisplayAlerts = False 'so a previous version of same date can
be overwritten

ActiveWorkbook.SaveAs FileName:="RotaryCalendar" & Format(Date,
"ddmmmyy"), FileFormat:=xlNormal, Password:= _
"", WriteResPassword:="", ReadOnlyRecommended:=False,
CreateBackup:=False

Application.DisplayAlerts = True 'to revert back to being alerted

which is fine

In another spreadsheet I should like to include a similar macro to Save
As... the workbook with concatenated name/ref number/date

The name held in say B3 and the ref number held in say D7 and the date as
above

Not sure how to do this - maybe I have to set two variables which will pick
up the name and ref number but you usually have smarter direct ways of
gathering this sort of thing - please!

Francis Hookham

PS
Can I/should I leave out some/all of this since password is not needed not
backup:
Password:= "", WriteResPassword:="", ReadOnlyRecommended:=False,
CreateBackup:=False

 

[Bernard Rey]

When recording a macro, you just get every possible property with it's
default status if nothing special has been modified. As you suspect it, you
don't need to keep them.

As to your question, you could simply write it this way:

ActiveWorkbook.SaveAs Filename:=Range("B3") & Range("D7") & _
Format(Date, "ddmmmyy")

You can also use two variables if you like.



Francis Hookham:

 

[JE McGimpsey]

In another spreadsheet I should like to include a similar macro to Save
As... the workbook with concatenated name/ref number/date

The name held in say B3 and the ref number held in say D7 and the date as
above

One way:

Application.DisplayAlerts = False
With ActiveWorkbook.Sheets("Sheet1")
.Parent.SaveAs _
Filename:=.Range("B3").Text & "/" & .Range("D7").Text & _
"/" & Format(Date, "ddmmmyy"), _
FileFormat:=xlWorkbookNormal, _
Password:="", _
WriteResPassword:="", _
ReadOnlyRecommended:=False, _
CreateBackup:=False
End With
Application.DisplayAlerts = True

Change the worksheet name as desired.

PS
Can I/should I leave out some/all of this since password is not needed not
backup:
Password:= "", WriteResPassword:="", ReadOnlyRecommended:=False,
CreateBackup:=False

You can leave everything after the file name out and take the defaults.
I tend to include all arguments in my applications, if for no other
reason than self-documentation - for instance, it makes it obvious that
the file isn't password protected.

 

[JE McGimpsey]

As to your question, you could simply write it this way:

ActiveWorkbook.SaveAs Filename:=Range("B3") & Range("D7") & _
Format(Date, "ddmmmyy")

You can also use two variables if you like.

Just a note: If you have multiple worksheets in the workbook, using the
unqualified Range("B3") may give unpredictable results, since the file
will be saved with whatever values are on the active sheet.