Excel VB to make a copy of opened file

[Chrisso]

Hi there.

I have a spreadsheet that I would like to make a back up of every
week.

My plan is:
* on workbook open logic inspects last back up date and back up
period
* if it has been longer since the last back up then the configured
period then make the back up

I have this working fine - the problem comes when I write the code to
actually perform the back up.

At the moment the only way I can see of doing this is using
ActiveWorkbook.SaveAs to create the backup with the backup name. The
problem then is the user has the backup open and not the actual
spreadsheet. Therefore I would have to re-open the actual spreadsheet
and close the back-up to finish the manoeuvre.

As this will happen when any old user opens the spreadsheet I want it
to be fast and invisible - this is not possible with my above solution
as saves and opens on our network take some time.

Is there some other API available where I can just tell Excel to make
a copy with a specified name and leave the user with the original
sporeadsheet open?

Cheers
Chrisso

 

[Norman Jones]

Hi Chrisso,

See the SaveCopyAs method and example in VBA help.

The SaveCopyAs method saves a copy but the current
file remains open.

 

[NickHK]

..SaveCopyAs ?

NickHK

 

[Chrisso]

Thanks Norman & Nick - I guess I could not see the wood for the trees!
Chrisso