I have a report (report.xls) that is progressively automatically generated
each month as production batch sheets are generated and printed from my
workbook(s).

What I would like to be able to do is when the workbook is launched for the
first time on the first day of each month, save (save as) the report to a new
file, the name of which is last month (eg. September2005.xls). Then, clear
the contents of the worksheet which was being used to store the data
throughout the month (report.xls) and then start again for the new month. The
workbook may be opened and closed several times on the first day of the
month, so upon subsequent openings on the first day of the month, the system
would need to know that the monthly report has already been saved.

Hi,

like this:

Sub Workbook_Open()
Dim OldName As String
Dim OldPath As String
Dim NewName As String
Dim NewPath As String

OldPath = ActiveWorkbook.Path & "\"
OldName = ActiveWorkbook.Name

NewPath = "c:\reports\"
NewName = Format(Date, "YYYY-MM") & ".xls"
Application.DisplayAlerts = False
If Dir(NewPath & NewName, vbNormal) = "" Then
ActiveWorkbook.SaveAs OldPath & OldName
ActiveWorkbook.SaveAs NewPath & NewName
Workbooks.Open OldPath & OldName
Workbooks(NewName).Close
End If
End Sub

Remember the name of the active workbook
get year and month, create a filename form it,
check whether such a file already exists,
if not, save the active workbook with that name.
open the old workbook,
close the new workbook.

Clearing all data is not a problem, I assume.


Helmut Weber

Hi Helmut,

The following is a bit simpler:

Sub Workbook_Open()
Dim OldName As String
Dim OldPath As String
Dim NewName As String
Dim NewPath As String

OldPath = ActiveWorkbook.Path & "\"
OldName = ActiveWorkbook.Name

NewPath = "c:\reports\"
NewName = Format(Date, "YYYY-MM") & ".xls"
If Dir(NewPath & NewName, vbNormal) = "" Then _
ActiveWorkbook.SaveCopyAs NewPath & NewName
End Sub

Best Regards,
Walt Weber


Helmut Weber wrote:
Hi,

like this:

Sub Workbook_Open()
Dim OldName As String
Dim OldPath As String
Dim NewName As String
Dim NewPath As String

OldPath = ActiveWorkbook.Path & "\"
OldName = ActiveWorkbook.Name

NewPath = "c:\reports\"
NewName = Format(Date, "YYYY-MM") & ".xls"
Application.DisplayAlerts = False
If Dir(NewPath & NewName, vbNormal) = "" Then
ActiveWorkbook.SaveAs OldPath & OldName
ActiveWorkbook.SaveAs NewPath & NewName
Workbooks.Open OldPath & OldName
Workbooks(NewName).Close
End If
End Sub

Remember the name of the active workbook
get year and month, create a filename form it,
check whether such a file already exists,
if not, save the active workbook with that name.
open the old workbook,
close the new workbook.

Clearing all data is not a problem, I assume.


Helmut Weber

Hi Helmut and Walt,

Thanks for your replies.

From my (very) limited knowledge of VB programming, the code looks as though
it first opens the report workbook, then sees if there is already a current
month file. If not it then saves the workbook as MMMYY.xls (or something
similar). Is this correct?

Is it possible to check whether the file exists first and then, if it
doesn't, open the report workbook and save it as the month name? I intend to
run this code from a workbook (program.xls) other than the report workbook,
so as is, every time program.xls is open and "OK" is clicked on the opening
sheet, the report workbook would open and the check would occur. This would
slow things down I think.

Also, is there something in this code that says that it only needs to occur
the first time program.xls is opened in a new month?

To clarify, what I would like to do is, on the first day that program.xls is
opened in October, save the data that had been entered into report.xls as
September05.xls (or similar) and clear report.xls so that Octobers data can
be saved into it (and then saved as October05.xls in November.

Perhaps it is easier to create October05.xls the first time program.xls is
run in October and save all of Octobers data to that. Then November05.xls the
first time the program is run in november etc etc.

Sorry for these silly question, but I am very new to VB programming.

"Walt" wrote:

Hi Helmut,

The following is a bit simpler:

Sub Workbook_Open()
Dim OldName As String
Dim OldPath As String
Dim NewName As String
Dim NewPath As String

OldPath = ActiveWorkbook.Path & "\"
OldName = ActiveWorkbook.Name

NewPath = "c:\reports\"
NewName = Format(Date, "YYYY-MM") & ".xls"
If Dir(NewPath & NewName, vbNormal) = "" Then _
ActiveWorkbook.SaveCopyAs NewPath & NewName
End Sub

Best Regards,
Walt Weber


Helmut Weber wrote:
Hi,

like this:

Sub Workbook_Open()
Dim OldName As String
Dim OldPath As String
Dim NewName As String
Dim NewPath As String

OldPath = ActiveWorkbook.Path & "\"
OldName = ActiveWorkbook.Name

NewPath = "c:\reports\"
NewName = Format(Date, "YYYY-MM") & ".xls"
Application.DisplayAlerts = False
If Dir(NewPath & NewName, vbNormal) = "" Then
ActiveWorkbook.SaveAs OldPath & OldName
ActiveWorkbook.SaveAs NewPath & NewName
Workbooks.Open OldPath & OldName
Workbooks(NewName).Close
End If
End Sub

Remember the name of the active workbook
get year and month, create a filename form it,
check whether such a file already exists,
if not, save the active workbook with that name.
open the old workbook,
close the new workbook.

Clearing all data is not a problem, I assume.


Helmut Weber

Hieverybody,

and thanks to Walt for "savecopyas".

From my (very) limited knowledge of VB programming, the code looks as though
it first opens the report workbook, then sees if there is already a current
month file. If not it then saves the workbook as MMMYY.xls (or something
similar). Is this correct?

Not quite. I thought of putting the code
into the, lets call it "master report". So it doesn't open the
master report, it just runs if the master report is openened.

Is it possible to check whether the file exists first and then, if it
doesn't, open the report workbook and save it as the month name?

If the code is in a third workbook like "program.xls", yes.

Also, is there something in this code that says that it only needs to occur
the first time program.xls is opened in a new month?

Not really, but the very existence of e.g. 2005-09.xls indicates,
that the master report was once opened in September and saved
as the monthly report.

To clarify, what I would like to do is, on the first day that program.xls is
opened in October, save the data that had been entered into report.xls as
September05.xls (or similar) and clear report.xls so that Octobers data can
be saved into it (and then saved as October05.xls in November.

Hm, I see, then get the month, eg 9, deduct 1, check whether 2005-08
already exists. Though with that you'll have to deduct 1 from
the number of the year in January, too, and set the month to 12!


And with more than 1 workbook open, working with
activeworkbook is dangereous, rather use
e.g. workbooks("NameofBook.xls").

Sorry for these silly question, but I am very new to VB programming.

And I'm rather new to Excel-programming.

--
Greetings from Bavaria, Germany

Helmut Weber

Win XP, Office 2003
"red.sys" & Chr$(64) & "t-online.de"

Hi Helmut and Walt,

Today was my first day back at work since my initial post, so I was able to
try out the code.

In the end, I have decided that it is easier to create a new file (eg
October05.xls) on the first of the month from the report template file and
have the data saved to that throughout the month (by using the generic
MMMMYY.xls format to open the correct report file and import the data), so
the code now looks a little different to the one you supplied.

However, without your help, I would not have been able to written the code I
am now using. As I said, I am very new to VB programming, so to do this alone
I would not have known where to start. I find that the help in VB editor
pretty much useless.

A very big thank you from Melbourne, Australia to you both for your
assistance.



"Helmut Weber" wrote:

Hieverybody,

and thanks to Walt for "savecopyas".

From my (very) limited knowledge of VB programming, the code looks as though
it first opens the report workbook, then sees if there is already a current
month file. If not it then saves the workbook as MMMYY.xls (or something
similar). Is this correct?

Not quite. I thought of putting the code
into the, lets call it "master report". So it doesn't open the
master report, it just runs if the master report is openened.

Is it possible to check whether the file exists first and then, if it
doesn't, open the report workbook and save it as the month name?

If the code is in a third workbook like "program.xls", yes.

Also, is there something in this code that says that it only needs to occur
the first time program.xls is opened in a new month?

Not really, but the very existence of e.g. 2005-09.xls indicates,
that the master report was once opened in September and saved
as the monthly report.

To clarify, what I would like to do is, on the first day that program.xls is
opened in October, save the data that had been entered into report.xls as
September05.xls (or similar) and clear report.xls so that Octobers data can
be saved into it (and then saved as October05.xls in November.

Hm, I see, then get the month, eg 9, deduct 1, check whether 2005-08
already exists. Though with that you'll have to deduct 1 from
the number of the year in January, too, and set the month to 12!


And with more than 1 workbook open, working with
activeworkbook is dangereous, rather use
e.g. workbooks("NameofBook.xls").

Sorry for these silly question, but I am very new to VB programming.

And I'm rather new to Excel-programming.

--
Greetings from Bavaria, Germany

Helmut Weber

Win XP, Office 2003
"red.sys" & Chr$(64) & "t-online.de"