Mass saving xls as csv

Question

hi, here's the trick. gotta convert 'bout 300 files from xls to csv, wrote some simple macro to do it, here's the code:

Dim wb As Workbook

For Each wb In Application.Workbooks
wb.Activate

    Rows("1:1").Select
    Selection.Delete Shift:=xlUp
    ActiveWorkbook.SaveAs Filename:= _
        "C:\samplepath\CBM Cennik " & ActiveWorkbook.Name & " 2010-04-02.csv" _
        , FileFormat:=xlCSV, CreateBackup:=False
Next wb

but it doesn't do exactly what i want - saves file "example.xls" as "example.xls 2010-04-02.csv", what i need is "example 2010-04-02.csv"

need support guys ;)

Answer 1

You could try to remove the extension in your filename.

Instead of ActiveWorkbook.Name use

Left(ActiveWorkbook.Name, InStrRev(ActiveWorkbook.Name, ".") - 1)

to remove everything after the last . in the filename (including the .).

Answer 2

A quick and dirty method is to

Replace(ActiveWorkbook.Name, ".xls", "")