ExcelBanter - macro file name

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - macro file name (https://www.excelbanter.com/excel-programming/362445-macro-file-name.html)

Hi,

I have made a macro for a project which i am working on. I have to run this
macro every week on different files. These files have names which include the
date of the data. Now without overwriting this file i want to run the macro
but want the date of this file to be included in the final file which i have
made. Is there anyway to do this?

for eg: Data file : Name : ABC 10-3-06

i want the final output files name to be : XYZ 10-3-06

Can somebody please help me.

Aziz.

change date size to reflect the number of characters in your date. In your
example, it is 7.

Sub ABC()
Dim sname As String, s As String, ipos As Long
Dim s1 As String, s2 As String, sext As String
Dim datesize as Long

datesize = 7
sname = ActiveWorkbook.Name
s = sname
sext = ""
ipos = InStr(1, sname, ".", vbTextCompare)
If ipos 0 Then
sext = Right(s, Len(s) - (ipos - 1))
s = Left(s, ipos - 1)
End If
s1 = Left(s, Len(s) - datesize)
s2 = Right(s, datesize)
s = "XYZ " & s2 & sext
'ActiveWorkbook.SaveAs ActiveWorkbook.Path & "\" & s
Debug.Print sname, s

End Sub

--
Regards,
Tom Ogilvy

"Aziz Ahmedabadwala" wrote:

Hi,

I have made a macro for a project which i am working on. I have to run this
macro every week on different files. These files have names which include the
date of the data. Now without overwriting this file i want to run the macro
but want the date of this file to be included in the final file which i have
made. Is there anyway to do this?

for eg: Data file : Name : ABC 10-3-06

i want the final output files name to be : XYZ 10-3-06

Can somebody please help me.

Aziz.

Tom,

Thanks for your help.

I am a real novice and dont really know much about coding.

i tried using your code. I am sure it is correct but i dont think i am using
it correctly.

below is the actual file names that i use so if you can help me code that
would be a real help. Thanks a lot.

Original File Name : glo eg 0423 rpt

Final File Name : F - E - Analysis - 0423

Many thanks for your help. It is much appritiated.
Aziz

"Tom Ogilvy" wrote:

change date size to reflect the number of characters in your date. In your
example, it is 7.

Sub ABC()
Dim sname As String, s As String, ipos As Long
Dim s1 As String, s2 As String, sext As String
Dim datesize as Long

datesize = 7
sname = ActiveWorkbook.Name
s = sname
sext = ""
ipos = InStr(1, sname, ".", vbTextCompare)
If ipos 0 Then
sext = Right(s, Len(s) - (ipos - 1))
s = Left(s, ipos - 1)
End If
s1 = Left(s, Len(s) - datesize)
s2 = Right(s, datesize)
s = "XYZ " & s2 & sext
'ActiveWorkbook.SaveAs ActiveWorkbook.Path & "\" & s
Debug.Print sname, s

End Sub

--
Regards,
Tom Ogilvy

"Aziz Ahmedabadwala" wrote:

Hi,

I have made a macro for a project which i am working on. I have to run this
macro every week on different files. These files have names which include the
date of the data. Now without overwriting this file i want to run the macro
but want the date of this file to be included in the final file which i have
made. Is there anyway to do this?

for eg: Data file : Name : ABC 10-3-06

i want the final output files name to be : XYZ 10-3-06

Can somebody please help me.

Aziz.

this should get you started:

Sub ABC()
Dim sNewname As String
Dim sChar As String, sDate As String
Dim sStart As String, SReplacement As String
sStart = "glo eg 0423 rpt"
SReplacement = "F - E - Analysis - "
For i = 1 To Len(sStart)
sChar = Mid(sStart, i, 1)
If IsNumeric(sChar) Then
sDate = sDate & sChar
End If
If sChar = " " And Len(sDate) < 0 Then
Exit For
End If
Next
sNewname = SReplacement & sDate
MsgBox "New name is: " & sNewname
End Sub

You will have to decide how you will populate sStart with the current name
of the file.

perhaps

sStart = Left(activeworkbook.name,len(activeworkbook.name)-4)

--
Regards,
Tom Ogilvy

"Aziz Ahmedabadwala" wrote:

Tom,

Thanks for your help.

I am a real novice and dont really know much about coding.

i tried using your code. I am sure it is correct but i dont think i am using
it correctly.

below is the actual file names that i use so if you can help me code that
would be a real help. Thanks a lot.

Original File Name : glo eg 0423 rpt

Final File Name : F - E - Analysis - 0423

Many thanks for your help. It is much appritiated.
Aziz

"Tom Ogilvy" wrote:

change date size to reflect the number of characters in your date. In your
example, it is 7.

Sub ABC()
Dim sname As String, s As String, ipos As Long
Dim s1 As String, s2 As String, sext As String
Dim datesize as Long

datesize = 7
sname = ActiveWorkbook.Name
s = sname
sext = ""
ipos = InStr(1, sname, ".", vbTextCompare)
If ipos 0 Then
sext = Right(s, Len(s) - (ipos - 1))
s = Left(s, ipos - 1)
End If
s1 = Left(s, Len(s) - datesize)
s2 = Right(s, datesize)
s = "XYZ " & s2 & sext
'ActiveWorkbook.SaveAs ActiveWorkbook.Path & "\" & s
Debug.Print sname, s

End Sub

--
Regards,
Tom Ogilvy

"Aziz Ahmedabadwala" wrote:

Hi,

I have made a macro for a project which i am working on. I have to run this
macro every week on different files. These files have names which include the
date of the data. Now without overwriting this file i want to run the macro
but want the date of this file to be included in the final file which i have
made. Is there anyway to do this?

for eg: Data file : Name : ABC 10-3-06

i want the final output files name to be : XYZ 10-3-06

Can somebody please help me.

Aziz.

Tom,

This is Excellent. It works. U r the best. This is exactly what i was trying
to do. U r great.

Thanks a lot.

Aziz

"Tom Ogilvy" wrote:

this should get you started:

Sub ABC()
Dim sNewname As String
Dim sChar As String, sDate As String
Dim sStart As String, SReplacement As String
sStart = "glo eg 0423 rpt"
SReplacement = "F - E - Analysis - "
For i = 1 To Len(sStart)
sChar = Mid(sStart, i, 1)
If IsNumeric(sChar) Then
sDate = sDate & sChar
End If
If sChar = " " And Len(sDate) < 0 Then
Exit For
End If
Next
sNewname = SReplacement & sDate
MsgBox "New name is: " & sNewname
End Sub

You will have to decide how you will populate sStart with the current name
of the file.

perhaps

sStart = Left(activeworkbook.name,len(activeworkbook.name)-4)

--
Regards,
Tom Ogilvy

"Aziz Ahmedabadwala" wrote:

Tom,

Thanks for your help.

I am a real novice and dont really know much about coding.

i tried using your code. I am sure it is correct but i dont think i am using
it correctly.

below is the actual file names that i use so if you can help me code that
would be a real help. Thanks a lot.

Original File Name : glo eg 0423 rpt

Final File Name : F - E - Analysis - 0423

Many thanks for your help. It is much appritiated.
Aziz

"Tom Ogilvy" wrote:

change date size to reflect the number of characters in your date. In your
example, it is 7.

Sub ABC()
Dim sname As String, s As String, ipos As Long
Dim s1 As String, s2 As String, sext As String
Dim datesize as Long

datesize = 7
sname = ActiveWorkbook.Name
s = sname
sext = ""
ipos = InStr(1, sname, ".", vbTextCompare)
If ipos 0 Then
sext = Right(s, Len(s) - (ipos - 1))
s = Left(s, ipos - 1)
End If
s1 = Left(s, Len(s) - datesize)
s2 = Right(s, datesize)
s = "XYZ " & s2 & sext
'ActiveWorkbook.SaveAs ActiveWorkbook.Path & "\" & s
Debug.Print sname, s

End Sub

--
Regards,
Tom Ogilvy

"Aziz Ahmedabadwala" wrote:

Hi,

I have made a macro for a project which i am working on. I have to run this
macro every week on different files. These files have names which include the
date of the data. Now without overwriting this file i want to run the macro
but want the date of this file to be included in the final file which i have
made. Is there anyway to do this?

for eg: Data file : Name : ABC 10-3-06

i want the final output files name to be : XYZ 10-3-06

Can somebody please help me.

Aziz.