deleting last 4 characters from file name
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 |
deleting last 4 characters from file name
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 "Hans" wrote in message ... 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 |
deleting last 4 characters from file name
Hi Hans,
Hans schrieb: 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"? 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) |
deleting last 4 characters from file name
try something like:
stOldName = Left(ThisWorkbook.Name, Len(ThisWorkbook.Name) - 4) Hans wrote: 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 |
deleting last 4 characters from file name
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 "Hans" wrote in message ... 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 . |
All times are GMT +1. The time now is 03:42 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com