CODEWITHSUNDEEP
vbaexcelcsvfilenames
Patrick Handcock
Patrick Handcock

Reputation: 29

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

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

Upvotes: 0

Views: 6238

Answers (3)

jawood2005
jawood2005

Reputation: 45

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.

Upvotes: 1

user4039065
user4039065

Reputation:

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.

Upvotes: 0

nicolaus-hee
nicolaus-hee

Reputation: 789

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.

Upvotes: 0

Related Questions