VBA Excel file to CSV, keeps CSV filename same as original workbook

Question

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

score 1 · Answer 1 · edited Apr 27 '15 at 06:13

1

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.

edited Apr 27 '15 at 06:13

shA.t

16,580
5
54
111

answered Apr 26 '15 at 07:24

jawood2005

45
4

1

Maybe use `InStr` to look for *".xl"*. That would seem to cover just about any circumstances. – Apr 26 '15 at 07:37
That definitely makes sense. Also, thanks for the edit to do the code box/tag thing! Still kind of learning how to do stuff on this site. – jawood2005 Apr 26 '15 at 07:40
Sorry guys - struggling to make it work. This creates a csv file called ActiveWorkbook (I need it to be the same original filename - i.e. 3WDL_1 (2014-08-07)10secDataTable sit . So should it look like this at the end? 'Columns("A:A").Select Selection.NumberFormat = "0.00" 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!\" & Left(ActiveWorkbook.Name, InStr(ActiveWorkbook.Name, ".") - 1) & ".csv" End Sub' – Patrick Handcock Apr 26 '15 at 07:46
@jawood2005 - No worries, we all started somewhere. [SO](http://stackoverflow.com/help) can take a little getting used to. I still have the [Markdown Editing Help](http://stackoverflow.com/editing-help) page bookmarked for quick reference. – Apr 26 '15 at 08:40
@Jeeped. Hi guys - would you mind just writing out the above new code you have added in full (as it would go into VBA) so I can understand what you mean. Sorry - quite new to this and am a little confused :( – Patrick Handcock Apr 26 '15 at 08:41

score 0 · Answer 2 · answered Apr 26 '15 at 07:29

0

Before saving as csv, get the name of the xls file. You can use the ActiveWorkbook.Nameproperty. Assuming that file is called something.xls(and not .xlsx), try this:

Sub Macro1()
    XLSName = Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 4)
    ActiveWorkbook.SaveAs Filename:="C:\Users\Username\Desktop\" & XLSName & ".csv", _
        FileFormat:=xlCSV, CreateBackup:=False
End Sub

This pulls the workbook name, cuts off the last 4 characters (".xls") and then runs the Save As command appending ".csv" to that. In case your Excel file has the xlsx extension, change line 2 to:

XLSName = Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 5)

Let me know if this works for you.

answered Apr 26 '15 at 07:29

nicolaus-hee

787
1
9
25

when I replace the code after Range("A1").Select with what you have there (using 5 for xlsx) I get the error "Expected End Sub"? – Patrick Handcock Apr 26 '15 at 07:59
Mmh. It works for me. Two things: (1) You can remove all the 'Scrolling' lines, (2) Have you adapted the file path? I guess the path `C:\Users\Username\Desktop\` does not exist on your computer. Choose a path that does. Hope this helps. – nicolaus-hee Apr 26 '15 at 08:09
Thanks again! I can get that to work but the idea was that I didn't want to have to change the filename in code everytime - I just wanted it to replicate the original using a shortcut (otherwise I may as well just save the old fashioned way with tab)? – Patrick Handcock Apr 26 '15 at 08:33
Sorry, please help me understand your comment. My above code does save the XLSX file as a CSV file with the same name. All you need to do is to define the folder you want that to go. What am I missing? – nicolaus-hee Apr 26 '15 at 09:05

score 0 · Answer 3 · answered Apr 26 '15 at 09:00

I would organize the pieces before stitching them together with standard string concatenation. Here is the relevant section of the code using the InStr function.

Dim myPath As String, myFileName As String

myPath = "C:\Users\paddy\Desktop\NEW CSV files whole CGM date ok!"
'possible alternate that gets the environment variable USERNAME
'myPath = "C:\Users\" & Environ("USERNAME") & "\Desktop\NEW CSV files whole CGM date ok!"

'check if the folder exists and if not create it
If Not CBool(Len(Dir(myPath, vbDirectory))) Then _
    MkDir Path:=myPath


myFileName = Left(ActiveWorkbook.Name, InStr(1, ActiveWorkbook.Name, ".xl", vbTextCompare) - 1)
'you don't actually need .csv as the extension if you are explicitly saving as xlCSV or xlCSVMac but here is an alternate
'myFileName = Left(ActiveWorkbook.Name, InStr(1, ActiveWorkbook.Name, ".xl", vbTextCompare) - 1) & ".csv"

'output to the VBE's Immediate window for checking later in case there is a problem
Debug.Print myPath & Chr(92) & myFileName

' the backslash is ASCII character 92
ActiveWorkbook.SaveAs Filename:=myPath & Chr(92) & myFileName, _
    FileFormat:=xlCSVMac, CreateBackup:=False

I'm not sure what all the scrolling was doing; it probably isn't necessary. You might want to add in the number formatting command.

VBA Excel file to CSV, keeps CSV filename same as original workbook

3 Answers3

Linked