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


(E-Mail Removed) 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


"(E-Mail Removed)" 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
>
>
> "(E-Mail Removed)" 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
> >
> >
> > "(E-Mail Removed)" 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
> > >
> > >