The Excel VBA to CSV file retains the same CSV file name as the original workbook

advertisements

I am trying to find a fast way to save my xlsx files as csv files with the same file-name as the xlsx file (just in csv format). I have recorded a macro with shortcut, But the issue is that whenever I try with a new file it saves as a the same file-name I recorded initial macro with (i.e. see below, probably because I have the file labelled in code as: 3WDL_1 (2014-08-07)10secDataTable sit.csv). Is there something I need to replace 3WDL_1 (2014-08-07)10secDataTable sit.csv with to make the macro save with the same file-name as the actual workbook I am working with.

So basically I have a folder full of xlsx files and I want to use a shortcut/macro on each xslx file to convert them into a csv files that have exactly the same name as original xlsx file, and are saved into the same folder.

Sub xlstocsv()
'
' xlstocsv Macro
'
' Keyboard Shortcut: Ctrl+a
'
    Columns("A:A").Select
    Range("A41243").Activate
    Selection.NumberFormat = "0.00"
    ActiveWindow.ScrollRow = 41231
    ActiveWindow.ScrollRow = 41090
    ActiveWindow.ScrollRow = 39753
    ActiveWindow.ScrollRow = 30184
    ActiveWindow.ScrollRow = 26385
    ActiveWindow.ScrollRow = 13017
    ActiveWindow.ScrollRow = 10976
    ActiveWindow.ScrollRow = 8162
    ActiveWindow.ScrollRow = 4785
    ActiveWindow.ScrollRow = 4503
    ActiveWindow.ScrollRow = 774
    ActiveWindow.ScrollRow = 1
    Range("A1").Select
    ChDir "C:\Users\paddy\Desktop\NEW CSV files whole CGM date ok!"
    ActiveWorkbook.SaveAs Filename:= _
        "C:\Users\paddy\Desktop\NEW CSV files whole CGM date ok!\3WDL_1 (2014-08-07)10secDataTable sit.csv" _
        , FileFormat:=xlCSVMac, CreateBackup:=False
End Sub


Right now, you've got the file-name hard-coded in after "ActiveWorkbook.SaveAs" so it's saving everything with that hard-coded name.

I think you'll want to use "ActiveWorkbook.Name" to get the name of the current file and concatenate it into the "Filename" variable that you have there (without the file extension) with the new extension. For example:

"C:\Users\padd\Desktop\NEW CSV...ok!\" & Left(ActiveWorkbook.Name, InStr(ActiveWorkbook.Name, ".") - 1) & ".csv")

This is a kind of a dirty way to do it, but it should serve your needs. Also, depending on which version of Excel you use, I think you might need to use "ThisWorkbook" instead of "ActiveWorkbook" but I'm not sure.