Change file name - remove specific text

  • Thread starter Thread starter cgilmartin
  • Start date Start date
C

cgilmartin

Newbie, so please be gentle...

I have a macro that runs a macro across multiple CSV files in a folder,
makes formating changes, then renames the file (adds "_ADS"), converts
it to an XLS and saves to a different directory. Everything works
great, except that in keeping the workbook name, it keeps the .csv in
the file name (example filename.csv.xls. This can sometimes exceed the
31 limit. Here's the renaming, converting and saving code:


Sheets("Sheet2").Name = ActiveWorkbook.Name
ActiveWorkbook.SaveAs Filename:="C:\Documents and
Settings\User\Desktop\Ads\" & ActiveWorkbook.Name & "_ADS" & ".xls", _
FileFormat:=xlWorkbookNormal, ReadOnlyRecommended:=False, _
CreateBackup:=False


Is there a way to remove the ".csv" from the workbook name during
renaming, converting?

sorry if this is basic, but I am just getting started with macros.

Thanks,
Chris
 
Try this out - used Left and Len to remove the last 4 characters of
"ActiveWorkbook.Name"

Sheets("Sheet2").Name = ActiveWorkbook.Name
ActiveWorkbook.SaveAs Filename:= _
"C:\Documents and Settings\User\Desktop\Ads\" & _
Left(ActiveWorkbook.Name, (Len(ActiveWorkbook.Name) - 4)) & _
"_ADS" & ".xls", FileFormat:=xlWorkbookNormal, _
ReadOnlyRecommended:=False, CreateBackup:=False

Sandy
 
Hi Martin,

Just adding some...
Or you can use this :

Dim WBNAME as string

WBNAME = Replace(ActiveWorkbook.Name,".csv" ,"")

Sheets("Sheet2").Name = ActiveWorkbook.Name
ActiveWorkbook.SaveAs Filename:="C:\Documents and
Settings\User\Desktop\Ads\" & WBNAME & "_ADS" & ".xls", _
FileFormat:=xlWorkbookNormal, ReadOnlyRecommended:=False, _
CreateBackup:=False
 
Thank you both. Both options worked!

CG
Hi Martin,

Just adding some...
Or you can use this :

Dim WBNAME as string

WBNAME = Replace(ActiveWorkbook.Name,".csv" ,"")

Sheets("Sheet2").Name = ActiveWorkbook.Name
ActiveWorkbook.SaveAs Filename:="C:\Documents and
Settings\User\Desktop\Ads\" & WBNAME & "_ADS" & ".xls", _
FileFormat:=xlWorkbookNormal, ReadOnlyRecommended:=False, _
CreateBackup:=False


--

Regards,

Halim
Click to expand...
 
One more question...

Is there a way to only run code that shortens the name if the name is
31 (or more) characters? I would like to maintain those files that do
not exceed the limit, only changing those that do.

Thanks!
 
Back
Top