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


wrote:
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

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


" wrote:

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

Thank you both. Both options worked!

CG

Halim wrote:
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


" wrote:

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

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!


CG wrote:
Thank you both. Both options worked!

CG

Halim wrote:
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


" wrote:

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