deleting last 4 characters from file name

H

Hans

I have the following macro for saving a file under the
existing name with "For Reporting" added to it:

Sub SaveAsReporting()
Dim stOldName As String
Dim stNewName As String
stOldName = ThisWorkbook.FullName
stNewName = stOldName & " For Reporting"
ActiveWorkbook.SaveAs stNewName
End Sub

However, the new name also includes the extension ".xls"
("filename.xls For Reporting") and I don't like that. Is
there a simple way to save the file without the ".xls"?

Hans
 
N

Norman Jones

Hi Hans,

Try this adaptation:

Sub SaveAsReporting()
Dim stOldName As String
Dim stNewName As String
Dim stPartName As String

stOldName = ThisWorkbook.FullName
stPartName = Left(stOldName, Len(stOldName) - 4)
stNewName = stPartName & " For Reporting.xls"
ActiveWorkbook.SaveAs stNewName
End Sub
 
M

Melanie Breden

Hi Hans,
I have the following macro for saving a file under the
existing name with "For Reporting" added to it:

Sub SaveAsReporting()
Dim stOldName As String
Dim stNewName As String
stOldName = ThisWorkbook.FullName
stNewName = stOldName & " For Reporting"
ActiveWorkbook.SaveAs stNewName
End Sub

However, the new name also includes the extension ".xls"
("filename.xls For Reporting") and I don't like that. Is
there a simple way to save the file without the ".xls"?
Click to expand...

starting from Excel2000 you can use the Replace function:

stOldName = VBA.Replace(ThisWorkbook.FullName, ".xls", "")


or:

ActiveWorkbook.SaveAs Replace(ThisWorkbook.FullName, ".xls", "") & _
" For Reporting"

--
Mit freundlichen Grüssen

Melanie Breden
- Microsoft MVP für Excel -

http://excel.codebooks.de (Das Excel-VBA Codebook)
 
S

SmilingPolitely

try something like:

stOldName = Left(ThisWorkbook.Name, Len(ThisWorkbook.Name) - 4)
 
H

Hans

Hello Norman

It works. Thanks!

regards,
Hans
-----Original Message-----
Hi Hans,

Try this adaptation:

Sub SaveAsReporting()
Dim stOldName As String
Dim stNewName As String
Dim stPartName As String

stOldName = ThisWorkbook.FullName
stPartName = Left(stOldName, Len(stOldName) - 4)
stNewName = stPartName & " For Reporting.xls"
ActiveWorkbook.SaveAs stNewName
End Sub

---
Regards,
Norman




.
Click to expand...
 
Top