Hi! How can u create a Macro that will save your client previous month
statement ie 'Tesco 01.04.07' with the a new date ie Tesco 01.05.07

Is this to be done from the previous month's statement file itself?

Where do you intend to get the date from: the system, or a cell on a sheet?

And is that mm.dd.yy or dd.mm.yy for part of the filename?

I will presume that you want to save it with new filename when date in a
cell on a particular sheet is changed.

This code would go into the Worksheet_Change() event processor. To put it
there, right-click on the sheet's name tab and choose [View Code] from the
list. Cut and paste this code into the module opened up in the VB Editor.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim theDay As String
Dim theMonth As String
Dim theYear As String
Dim newFileName As String
'change this address to the address of the
'cell with the date to be used in it
'be sure to use absolute addressing
'using the $symbols before the column ID
'and row number.
If Target.Address < "$A$1" Then
Exit Sub ' no date change
End If
If Not IsDate(Target) Then
Exit Sub ' invalid as a date
End If
theDay = Trim(Str(Day(Target)))
If Len(theDay) = 1 Then
theDay = "0" & theDay
End If

theMonth = Trim(Str(Month(Target)))
If Len(theMonth) = 1 Then
theMonth = "0" & theDay
End If

theYear = Trim(Str(Year(Target)))
If Len(theYear) = 1 Then
theYear = "0" & theYear
Else
theYear = Right(theYear, 2)
End If
If InStr(ThisWorkbook.FullName, " ") = 0 Then
'you need to save it initially with
'proper name
MsgBox "Cannot auto-save at this time. Save the file with correct
name now.", vbOKOnly, "Name Problem"
Exit Sub
End If
'this presumes that the last space
'in the filename is the space just
'before the date portion of the filename
newFileName = Left(ThisWorkbook.FullName, _
InStrRev(ThisWorkbook.FullName, " ")) & _
theDay & "." & theMonth & "." & theYear _
& ".xls"
Application.DisplayAlerts = False
ThisWorkbook.SaveAs Filename:=newFileName
Application.DisplayAlerts = True
End Sub

I can envision one or two things going wrong, one we catch: the workbook has
never been saved before. The other is if the filenaming convention you use
is not as shown. The code depends on there being at least a single space
between the customer name portion and the date portion, and no more spaces in
the name after that. If you adhere to that convention, it should work
reliably for you.

You need to change the address of the cell with the date in it, and if you
want a different sequence for the day.month portion of the filename, just
change the sequence they are built up in the newFilename= statement.


"Joe" wrote:

Hi! How can u create a Macro that will save your client previous month
statement ie 'Tesco 01.04.07' with the a new date ie Tesco 01.05.07

thanks for your reply. YOu idea was good
You seem to know a lot of programming. Unfortunately, it dont know anything.
I entered the code but it doesn work. it give an error in MsgBox "Cannot
auto-save at this time. Save the file with correct
name now.", vbOKOnly, "Name Problem"
*I could get the date from a cell in the spreadshee and format dd.mm.yy

Could you give me a macro that will work everytime that I use it? that will
rename the file with the new date

Another thing, is it possible to create a macro that will insert the date of
the computer in a cell in the spreadsheet?

Many thanks,
jose

Is this to be done from the previous month's statement file itself?

Where do you intend to get the date from: the system, or a cell on a sheet?

And is that mm.dd.yy or dd.mm.yy for part of the filename?

I will presume that you want to save it with new filename when date in a
cell on a particular sheet is changed.

This code would go into the Worksheet_Change() event processor. To put it
there, right-click on the sheet's name tab and choose [View Code] from the
list. Cut and paste this code into the module opened up in the VB Editor.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim theDay As String
Dim theMonth As String
Dim theYear As String
Dim newFileName As String
'change this address to the address of the
'cell with the date to be used in it
'be sure to use absolute addressing
'using the $symbols before the column ID
'and row number.
If Target.Address < "$A$1" Then
Exit Sub ' no date change
End If
If Not IsDate(Target) Then
Exit Sub ' invalid as a date
End If
theDay = Trim(Str(Day(Target)))
If Len(theDay) = 1 Then
theDay = "0" & theDay
End If

theMonth = Trim(Str(Month(Target)))
If Len(theMonth) = 1 Then
theMonth = "0" & theDay
End If

theYear = Trim(Str(Year(Target)))
If Len(theYear) = 1 Then
theYear = "0" & theYear
Else
theYear = Right(theYear, 2)
End If
If InStr(ThisWorkbook.FullName, " ") = 0 Then
'you need to save it initially with
'proper name
MsgBox "Cannot auto-save at this time. Save the file with correct
name now.", vbOKOnly, "Name Problem"
Exit Sub
End If
'this presumes that the last space
'in the filename is the space just
'before the date portion of the filename
newFileName = Left(ThisWorkbook.FullName, _
InStrRev(ThisWorkbook.FullName, " ")) & _
theDay & "." & theMonth & "." & theYear _
& ".xls"
Application.DisplayAlerts = False
ThisWorkbook.SaveAs Filename:=newFileName
Application.DisplayAlerts = True
End Sub
I can envision one or two things going wrong, one we catch: the workbook has
never been saved before. The other is if the filenaming convention you use
is not as shown. The code depends on there being at least a single space
between the customer name portion and the date portion, and no more spaces in
the name after that. If you adhere to that convention, it should work
reliably for you.

You need to change the address of the cell with the date in it, and if you
want a different sequence for the day.month portion of the filename, just
change the sequence they are built up in the newFilename= statement.


"Joe" wrote:

Hi! How can u create a Macro that will save your client previous month
statement ie 'Tesco 01.04.07' with the a new date ie Tesco 01.05.07
Did this post answer the question?

If you are actually getting a VB Error message at that line of code, it's
because the editor here split it into two lines in the code. If you see it
in your workbook as

MsgBox "Cannot auto-save at this time. Save the file with correct"
name now.", vbOKOnly, "Name Problem"

then go to the " at the end of the first line and delete it and hit [Del] a
few more times to make it all 1 line in the code module. Or try replacing
that section with this:

If InStr(ThisWorkbook.FullName, " ") = 0 Then
'you need to save it initially with
'proper name
MsgBox "Cannot auto-save at this time. " _
& "Save the file with correct name now.", _
vbOKOnly, "Name Problem"
Exit Sub
End If


Joe, the reason you're getting that 'error' is that the filename at the time
the code runs does not have a space character in it. I was going by the
example of the filename you gave in your first post - "Tesco 01.04.07" it
looks to me as if there is a space character between the "o" and the first 0
(oh and first zero).


If you still have a problem after that, then consider all of this, please -
I'll need to know the EXACT format of a filename you use. Such as "Tesco
01.04.07.xls" or "Tesco01.04.07.xls"

The main reason I put that check in there for the space was to keep from
trying to build up a bad filename should you be creating a new file from a
template which wouldn't have a space in it unless the template name itself
had one.

That message is telling you that it couldn't find a space in the filename
and it thinks you need to 'start the ball rolling', so to speak, by first
saving the file with a filename of the type/format it expects. But I have to
know what I'm working with here.

Let me know whether the new code snippet above fixes things or not.


"Joe" wrote:

thanks for your reply. YOu idea was good
You seem to know a lot of programming. Unfortunately, it dont know anything.
I entered the code but it doesn work. it give an error in MsgBox "Cannot
auto-save at this time. Save the file with correct
name now.", vbOKOnly, "Name Problem"
*I could get the date from a cell in the spreadshee and format dd.mm.yy

Could you give me a macro that will work everytime that I use it? that will
rename the file with the new date

Another thing, is it possible to create a macro that will insert the date of
the computer in a cell in the spreadsheet?

Many thanks,
jose

Is this to be done from the previous month's statement file itself?

Where do you intend to get the date from: the system, or a cell on a sheet?

And is that mm.dd.yy or dd.mm.yy for part of the filename?

I will presume that you want to save it with new filename when date in a
cell on a particular sheet is changed.

This code would go into the Worksheet_Change() event processor. To put it
there, right-click on the sheet's name tab and choose [View Code] from the
list. Cut and paste this code into the module opened up in the VB Editor.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim theDay As String
Dim theMonth As String
Dim theYear As String
Dim newFileName As String
'change this address to the address of the
'cell with the date to be used in it
'be sure to use absolute addressing
'using the $symbols before the column ID
'and row number.
If Target.Address < "$A$1" Then
Exit Sub ' no date change
End If
If Not IsDate(Target) Then
Exit Sub ' invalid as a date
End If
theDay = Trim(Str(Day(Target)))
If Len(theDay) = 1 Then
theDay = "0" & theDay
End If

theMonth = Trim(Str(Month(Target)))
If Len(theMonth) = 1 Then
theMonth = "0" & theDay
End If

theYear = Trim(Str(Year(Target)))
If Len(theYear) = 1 Then
theYear = "0" & theYear
Else
theYear = Right(theYear, 2)
End If
If InStr(ThisWorkbook.FullName, " ") = 0 Then
'you need to save it initially with
'proper name
MsgBox "Cannot auto-save at this time. Save the file with correct
name now.", vbOKOnly, "Name Problem"
Exit Sub
End If
'this presumes that the last space
'in the filename is the space just
'before the date portion of the filename
newFileName = Left(ThisWorkbook.FullName, _
InStrRev(ThisWorkbook.FullName, " ")) & _
theDay & "." & theMonth & "." & theYear _
& ".xls"
Application.DisplayAlerts = False
ThisWorkbook.SaveAs Filename:=newFileName
Application.DisplayAlerts = True
End Sub
I can envision one or two things going wrong, one we catch: the workbook has
never been saved before. The other is if the filenaming convention you use
is not as shown. The code depends on there being at least a single space
between the customer name portion and the date portion, and no more spaces in
the name after that. If you adhere to that convention, it should work
reliably for you.

You need to change the address of the cell with the date in it, and if you
want a different sequence for the day.month portion of the filename, just
change the sequence they are built up in the newFilename= statement.


"Joe" wrote:

Hi! How can u create a Macro that will save your client previous month
statement ie 'Tesco 01.04.07' with the a new date ie Tesco 01.05.07
Did this post answer the question?

Thanks, I will check tomorrow

"JLatham" wrote:

If you are actually getting a VB Error message at that line of code, it's
because the editor here split it into two lines in the code. If you see it
in your workbook as

MsgBox "Cannot auto-save at this time. Save the file with correct"
name now.", vbOKOnly, "Name Problem"

then go to the " at the end of the first line and delete it and hit [Del] a
few more times to make it all 1 line in the code module. Or try replacing
that section with this:

If InStr(ThisWorkbook.FullName, " ") = 0 Then
'you need to save it initially with
'proper name
MsgBox "Cannot auto-save at this time. " _
& "Save the file with correct name now.", _
vbOKOnly, "Name Problem"
Exit Sub
End If


Joe, the reason you're getting that 'error' is that the filename at the time
the code runs does not have a space character in it. I was going by the
example of the filename you gave in your first post - "Tesco 01.04.07" it
looks to me as if there is a space character between the "o" and the first 0
(oh and first zero).


If you still have a problem after that, then consider all of this, please -
I'll need to know the EXACT format of a filename you use. Such as "Tesco
01.04.07.xls" or "Tesco01.04.07.xls"

The main reason I put that check in there for the space was to keep from
trying to build up a bad filename should you be creating a new file from a
template which wouldn't have a space in it unless the template name itself
had one.

That message is telling you that it couldn't find a space in the filename
and it thinks you need to 'start the ball rolling', so to speak, by first
saving the file with a filename of the type/format it expects. But I have to
know what I'm working with here.

Let me know whether the new code snippet above fixes things or not.


"Joe" wrote:

thanks for your reply. YOu idea was good
You seem to know a lot of programming. Unfortunately, it dont know anything.
I entered the code but it doesn work. it give an error in MsgBox "Cannot
auto-save at this time. Save the file with correct
name now.", vbOKOnly, "Name Problem"
*I could get the date from a cell in the spreadshee and format dd.mm.yy

Could you give me a macro that will work everytime that I use it? that will
rename the file with the new date

Another thing, is it possible to create a macro that will insert the date of
the computer in a cell in the spreadsheet?

Many thanks,
jose

Is this to be done from the previous month's statement file itself?

Where do you intend to get the date from: the system, or a cell on a sheet?

And is that mm.dd.yy or dd.mm.yy for part of the filename?

I will presume that you want to save it with new filename when date in a
cell on a particular sheet is changed.

This code would go into the Worksheet_Change() event processor. To put it
there, right-click on the sheet's name tab and choose [View Code] from the
list. Cut and paste this code into the module opened up in the VB Editor.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim theDay As String
Dim theMonth As String
Dim theYear As String
Dim newFileName As String
'change this address to the address of the
'cell with the date to be used in it
'be sure to use absolute addressing
'using the $symbols before the column ID
'and row number.
If Target.Address < "$A$1" Then
Exit Sub ' no date change
End If
If Not IsDate(Target) Then
Exit Sub ' invalid as a date
End If
theDay = Trim(Str(Day(Target)))
If Len(theDay) = 1 Then
theDay = "0" & theDay
End If

theMonth = Trim(Str(Month(Target)))
If Len(theMonth) = 1 Then
theMonth = "0" & theDay
End If

theYear = Trim(Str(Year(Target)))
If Len(theYear) = 1 Then
theYear = "0" & theYear
Else
theYear = Right(theYear, 2)
End If
If InStr(ThisWorkbook.FullName, " ") = 0 Then
'you need to save it initially with
'proper name
MsgBox "Cannot auto-save at this time. Save the file with correct
name now.", vbOKOnly, "Name Problem"
Exit Sub
End If
'this presumes that the last space
'in the filename is the space just
'before the date portion of the filename
newFileName = Left(ThisWorkbook.FullName, _
InStrRev(ThisWorkbook.FullName, " ")) & _
theDay & "." & theMonth & "." & theYear _
& ".xls"
Application.DisplayAlerts = False
ThisWorkbook.SaveAs Filename:=newFileName
Application.DisplayAlerts = True
End Sub
I can envision one or two things going wrong, one we catch: the workbook has
never been saved before. The other is if the filenaming convention you use
is not as shown. The code depends on there being at least a single space
between the customer name portion and the date portion, and no more spaces in
the name after that. If you adhere to that convention, it should work
reliably for you.

You need to change the address of the cell with the date in it, and if you
want a different sequence for the day.month portion of the filename, just
change the sequence they are built up in the newFilename= statement.


"Joe" wrote:

Hi! How can u create a Macro that will save your client previous month
statement ie 'Tesco 01.04.07' with the a new date ie Tesco 01.05.07
Did this post answer the question?

Thanks for that.

Still doesnt work. I have installed and when I go to save it it doesnt
change the date

1. yeap, you are right it should be a space in between.
2. Could you create this as a macro, so I could use it every time I need it.
3. Is there anyway to relate a formula that takes the current day from the
computer ie dd.mm.yy. I create a very simple macro for the date, but I need
to update the date in the macro every time I use it

Thanks for all your help
jose

"JLatham" wrote:

If you are actually getting a VB Error message at that line of code, it's
because the editor here split it into two lines in the code. If you see it
in your workbook as

MsgBox "Cannot auto-save at this time. Save the file with correct"
name now.", vbOKOnly, "Name Problem"

then go to the " at the end of the first line and delete it and hit [Del] a
few more times to make it all 1 line in the code module. Or try replacing
that section with this:

If InStr(ThisWorkbook.FullName, " ") = 0 Then
'you need to save it initially with
'proper name
MsgBox "Cannot auto-save at this time. " _
& "Save the file with correct name now.", _
vbOKOnly, "Name Problem"
Exit Sub
End If


Joe, the reason you're getting that 'error' is that the filename at the time
the code runs does not have a space character in it. I was going by the
example of the filename you gave in your first post - "Tesco 01.04.07" it
looks to me as if there is a space character between the "o" and the first 0
(oh and first zero).


If you still have a problem after that, then consider all of this, please -
I'll need to know the EXACT format of a filename you use. Such as "Tesco
01.04.07.xls" or "Tesco01.04.07.xls"

The main reason I put that check in there for the space was to keep from
trying to build up a bad filename should you be creating a new file from a
template which wouldn't have a space in it unless the template name itself
had one.

That message is telling you that it couldn't find a space in the filename
and it thinks you need to 'start the ball rolling', so to speak, by first
saving the file with a filename of the type/format it expects. But I have to
know what I'm working with here.

Let me know whether the new code snippet above fixes things or not.


"Joe" wrote:

thanks for your reply. YOu idea was good
You seem to know a lot of programming. Unfortunately, it dont know anything.
I entered the code but it doesn work. it give an error in MsgBox "Cannot
auto-save at this time. Save the file with correct
name now.", vbOKOnly, "Name Problem"
*I could get the date from a cell in the spreadshee and format dd.mm.yy

Could you give me a macro that will work everytime that I use it? that will
rename the file with the new date

Another thing, is it possible to create a macro that will insert the date of
the computer in a cell in the spreadsheet?

Many thanks,
jose

Is this to be done from the previous month's statement file itself?

Where do you intend to get the date from: the system, or a cell on a sheet?

And is that mm.dd.yy or dd.mm.yy for part of the filename?

I will presume that you want to save it with new filename when date in a
cell on a particular sheet is changed.

This code would go into the Worksheet_Change() event processor. To put it
there, right-click on the sheet's name tab and choose [View Code] from the
list. Cut and paste this code into the module opened up in the VB Editor.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim theDay As String
Dim theMonth As String
Dim theYear As String
Dim newFileName As String
'change this address to the address of the
'cell with the date to be used in it
'be sure to use absolute addressing
'using the $symbols before the column ID
'and row number.
If Target.Address < "$A$1" Then
Exit Sub ' no date change
End If
If Not IsDate(Target) Then
Exit Sub ' invalid as a date
End If
theDay = Trim(Str(Day(Target)))
If Len(theDay) = 1 Then
theDay = "0" & theDay
End If

theMonth = Trim(Str(Month(Target)))
If Len(theMonth) = 1 Then
theMonth = "0" & theDay
End If

theYear = Trim(Str(Year(Target)))
If Len(theYear) = 1 Then
theYear = "0" & theYear
Else
theYear = Right(theYear, 2)
End If
If InStr(ThisWorkbook.FullName, " ") = 0 Then
'you need to save it initially with
'proper name
MsgBox "Cannot auto-save at this time. Save the file with correct
name now.", vbOKOnly, "Name Problem"
Exit Sub
End If
'this presumes that the last space
'in the filename is the space just
'before the date portion of the filename
newFileName = Left(ThisWorkbook.FullName, _
InStrRev(ThisWorkbook.FullName, " ")) & _
theDay & "." & theMonth & "." & theYear _
& ".xls"
Application.DisplayAlerts = False
ThisWorkbook.SaveAs Filename:=newFileName
Application.DisplayAlerts = True
End Sub
I can envision one or two things going wrong, one we catch: the workbook has
never been saved before. The other is if the filenaming convention you use
is not as shown. The code depends on there being at least a single space
between the customer name portion and the date portion, and no more spaces in
the name after that. If you adhere to that convention, it should work
reliably for you.

You need to change the address of the cell with the date in it, and if you
want a different sequence for the day.month portion of the filename, just
change the sequence they are built up in the newFilename= statement.


"Joe" wrote:

Hi! How can u create a Macro that will save your client previous month
statement ie 'Tesco 01.04.07' with the a new date ie Tesco 01.05.07
Did this post answer the question?