Excel Macros Help

[Flox]

I just started using Macros and need some help. Using Excel 2007 I managed to record a Macros and everything seems to be working except for the File Save as part.. I need to set it to use the orignal file name but the format set to csv instead of xls.

I have several folders with excel sheets named January to December, Once the Macros is complete, I want to keep the same file name but set the format to csv.

Here is the last paragraph of the macros

ChDir _
        "C:\Documents and Settings\User\My Documents\ST\Time Tables\cONVERTING\KlerkTest\ptc30 generated"
    ActiveWorkbook.SaveAs Filename:= _
        "C:\Documents and Settings\User\My Documents\ST\Time Tables\cONVERTING\KlerkTest\ptc30 generated\Month.csv" _
        , FileFormat:=xlCSV, CreateBackup:=False

The file I used to test was January.xls and Saved it as Month.csv when recording the Macros, but when I run the Macros on February.xls it gives an error as Month.csv already exists.. If possible I would like each month to still have the Month name (i.e January.csv, February.csv etc), instead of the filename being Month.csv

I would also like it to use the original folder/current directory where the xls was opened from.

 

[dabean]

ActiveWorkbook.SaveAs Filename:= _
        ActiveWorkbook.Path & "\" & Format(Now, "mmmm") & ".csv" _
        , FileFormat:=xlCSV, CreateBackup:=False

 

[Flox]

ActiveWorkbook.SaveAs Filename:= _
        ActiveWorkbook.Path & "\" & Format(Now, "mmmm") & ".csv" _
        , FileFormat:=xlCSV, CreateBackup:=False

Thanks.. It creates a new file.. but the file name saves as ptc30 generatedSeptember.csv.. thats the folder name and the current month... I just need the month part to be the same as the orignal .xls file

 

[dabean]

I edited the post to include the missing "\" between the folder and filename, you probably copied the code before that.

If you just need the extension changed the easiest will probably be something like this:

ActiveWorkbook.SaveAs Filename:= _
        ActiveWorkbook.Path & "\" & Replace(ActiveWorkbook.Name, ".xls", ".csv") _
        , FileFormat:=xlCSV, CreateBackup:=False

 

[Flox]

I edited the post to include the missing "\" between the folder and filename, you probably copied the code before that.

If you just need the extension changed the easiest will probably be something like this:

ActiveWorkbook.SaveAs Filename:= _
        ActiveWorkbook.Path & "\" & Replace(ActiveWorkbook.Name, ".xls", ".csv") _
        , FileFormat:=xlCSV, CreateBackup:=False

Thanks, I just need the extension changed, and the above works 100%

Thanks alot