I'm developing an Excel-based VBA program that performs
some calculations and updates on a master budget sheet.

However, before I allow the user to go modifying this
sheet, I would like to "back it up" to a new Excel
spreadsheet in the very-likely event I have to correct
their errors.

How do you copy/export/backup the entire workbook and/or
selected sheets to a new Excel workbook?

Thanks in advance for your help!

There are many ways to do this. Have you thought of using saveas or sheet
copy?

"Roy" wrote in message
...
I'm developing an Excel-based VBA program that performs
some calculations and updates on a master budget sheet.

However, before I allow the user to go modifying this
sheet, I would like to "back it up" to a new Excel
spreadsheet in the very-likely event I have to correct
their errors.

How do you copy/export/backup the entire workbook and/or
selected sheets to a new Excel workbook?

Thanks in advance for your help!

Roy,

If you want to do it as an event before the user gets at it, I would suggest
using the FileSystemObject in a Workbook_Open event. The problem with SaveAs
is that it changes the name of the workbook being worked upon (to whatever
was specified in SaveAs).

Private Sub Workbook_Open()

Dim oFSO As Object
Dim sThisFile As String
Dim sBackup As String

sThisFile = ThisWorkbook.FullName
sBackup = Left(sThisFile, Len(sThisFile) - 3) & Format(Now, "dd mmm yyyy
hh-mm-ss") & ".bak"

Set oFSO = CreateObject("Scripting.FileSystemObject")
oFSO.copyfile sThisFile, sBackup
Set oFSO = Nothing

End Sub

The reason I use FSO rather than FileCopy is that FileCopy does not work on
an open file, which you need in this case.

The file saved is the original filename, without the 'xls' extension, and
with a date and time appended, and finally '.bak.' This will leave you as
many versions as you want to keep in the same folder.

This code is workbook code, so it goes in the ThisWorkbook code module.

--

HTH

Bob Phillips

"Roy" wrote in message
...
Mr. Walkenbach didn't cover those in his book (Excel 2000
Power Programming with VBA), or at least not in a way
that I could understand!

Could you provide some more detail or a reference site?


-----Original Message-----
There are many ways to do this. Have you thought of
using saveas or sheet
copy?

"Roy" wrote in message
...
I'm developing an Excel-based VBA program that performs
some calculations and updates on a master budget sheet.

However, before I allow the user to go modifying this
sheet, I would like to "back it up" to a new Excel
spreadsheet in the very-likely event I have to correct
their errors.

How do you copy/export/backup the entire workbook
and/or
selected sheets to a new Excel workbook?

Thanks in advance for your help!


.

You may want to look at SaveCopyAs. It's documented in VBA's help.

Roy wrote:

Mr. Walkenbach didn't cover those in his book (Excel 2000
Power Programming with VBA), or at least not in a way
that I could understand!

Could you provide some more detail or a reference site?

-----Original Message-----
There are many ways to do this. Have you thought of
using saveas or sheet
copy?

"Roy" wrote in message
...
I'm developing an Excel-based VBA program that performs
some calculations and updates on a master budget sheet.

However, before I allow the user to go modifying this
sheet, I would like to "back it up" to a new Excel
spreadsheet in the very-likely event I have to correct
their errors.

How do you copy/export/backup the entire workbook
and/or
selected sheets to a new Excel workbook?

Thanks in advance for your help!


.


--

Dave Peterson

Roy posted a further question to me direct re deleting old files. I offered
this morsel, but I am also posting here in case anyone has an alternative.
BTW, Roy asked to keep a number, but I gave aged files, as the file has a
date property.

Bob

Roy,

Here's a little routine that deletes files not modified in a given time.

Sub DeleteOldFiles(path As String, Optional age As Long = 10)
Dim oFSO As Object, oFolder As Object
Dim oFiles As Object, oFile As Object
Dim cFiles As Long, i As Long
Dim sFiles As String

Set oFSO = CreateObject("Scripting.FileSystemObject")
Set oFolder = oFSO.GetFolder(path)
Set oFiles = oFolder.Files

For Each oFile In oFiles
If oFile.DateLastModified < (Date - age) Then
cFiles = cFiles + 1
sFiles = sFiles & oFile.path & Chr(13)
oFile.Delete
End If
Next

MsgBox sFiles & Chr(13) & "are the files deleted"

End Sub

This is how you call it
DeleteOldFiles "c:\myTest\myTest"
or
DeleteOldFiles "c:\myTest\myTest", 25


"Bob Phillips" wrote in message
...
Roy,

If you want to do it as an event before the user gets at it, I would
suggest
using the FileSystemObject in a Workbook_Open event. The problem with
SaveAs
is that it changes the name of the workbook being worked upon (to whatever
was specified in SaveAs).

Private Sub Workbook_Open()

Dim oFSO As Object
Dim sThisFile As String
Dim sBackup As String

sThisFile = ThisWorkbook.FullName
sBackup = Left(sThisFile, Len(sThisFile) - 3) & Format(Now, "dd mmm
yyyy
hh-mm-ss") & ".bak"

Set oFSO = CreateObject("Scripting.FileSystemObject")
oFSO.copyfile sThisFile, sBackup
Set oFSO = Nothing

End Sub

The reason I use FSO rather than FileCopy is that FileCopy does not work
on
an open file, which you need in this case.

The file saved is the original filename, without the 'xls' extension, and
with a date and time appended, and finally '.bak.' This will leave you as
many versions as you want to keep in the same folder.

This code is workbook code, so it goes in the ThisWorkbook code module.

--

HTH

Bob Phillips

"Roy" wrote in message
...
Mr. Walkenbach didn't cover those in his book (Excel 2000
Power Programming with VBA), or at least not in a way
that I could understand!

Could you provide some more detail or a reference site?


-----Original Message-----
There are many ways to do this. Have you thought of
using saveas or sheet
copy?

"Roy" wrote in message
...
I'm developing an Excel-based VBA program that performs
some calculations and updates on a master budget sheet.

However, before I allow the user to go modifying this
sheet, I would like to "back it up" to a new Excel
spreadsheet in the very-likely event I have to correct
their errors.

How do you copy/export/backup the entire workbook
and/or
selected sheets to a new Excel workbook?

Thanks in advance for your help!


.