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.