Macro needed; save a file two (or more places) at the same time

  • Thread starter Thread starter Sigurd
  • Start date Start date
S

Sigurd

I've tried using the simple "record" function and saved a file on two
different locations, but it's not very dymanic since the name of the
workbook always is the same :) - does anyone know of a macro that one
can run in any workbook where I can specify a name and the locations
each time? (The macro is meant to be used for backup causes).

SGu
 
Hi
you may try the following: Put this code in your workbook module:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Dim fileSaveName
ChDrive "C:"
ChDir "C:\Backup" 'change this to your needs
fileSaveName = Application.GetSaveAsFilename( _
InitialFilename:=Activeworkbook.name, _
fileFilter:="Excel Files (*.xls), *.xls")
If fileSaveName <> False Then
ActiveWorkbook.SaveAs Filename:=fileSaveName
End If
end sub
 
No, I don't, but this is exactly what I'm looking for, so
if you find an answewr would you be so kind as to share it
with me?
Thanks
SL
 
Take a look at the FileDialog object, its properties and methods. There is a
lot you can do with it, depending on your needs. Copy this into your
Personal.xls and give it a try. Give it a more creative name and parhaps map
it to a custom macro button.

Sub Main()
With Application.FileDialog(msoFileDialogSaveAs)
.InitialFileName = "C:\backupdir\*bak.xls"
.Show
End With
End Sub

You could also save a copy behind the scenes using something like the
following.
You can derive the saveas name from the existing filename if you like.
Activeworkbook.SaveCopyAs "c:\backupdir\" & activeworkbook.name & "_bak.xls"

You can trigger code execution in worksheet or workbook events.

Search for SaveCopyAs, FileDialog on Google in microsoft.public.excel.*
groups for more examples and discussion. There are many options.
 
Back
Top