Forcing SaveAs to new name

[Rob]

Hi,

I would like to know how to force a user to save a workbook upon closing,
with a new name, so that the original is never changed. I realise this can
be done by saving the file as a template, but I'd rather not have it as a
template as I have some other VBA procedures that go back and forth between
other workbooks, and if I use the template method I can't guarantee the file
name. I guess there might be a resolution here if I force a save to the file
name I'm after, but I wonder if that might be problematic if a file by that
name already exists........

Furthermore, once the file has been saved by a different name, what would
need to happen so the new file CAN be saved as its existing name.

I hope that's clear, but if not....
The file I never want changed is called "A".
When "A" is opened and some procedures are run, I then need the user to HAVE
TO save it under a new file and a name which he can decide (let's say "B").
When "B" is opened, any changes can be made to "B" and saved as usual.

Rob

 

[Guest]

You could force a save on open

Private Sub Workbook_Open()
Dim sFilename
sFilename = Application.GetSaveAsFilename( _
fileFilter:="Excel Files (*.xls), *.xls")
If sFilename <> False Then
ThisWorkbook.SaveAs sFilename
Else
ThisWorkbook.Close False
End If
End If

End Sub

'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code

Bob Phillips created this force event code.

 

[Rob]

Thanks Sunday....,

However, it's not totally what I require, but it's a start. I'll have a
play with it and see. I don't really want the option to save presented on
opening, only if changes are made, and then to not allow it to be saved as
the same name as the file.
I suspect what I'm trying to do may be beyond the scope of Excel.

Rob