How to save a copy of current excel to CSV to new folder?

Question

I am trying to save my current excel called Gaf to a CSV.

ActiveWorkbook.SaveAs Filename:=Workbooks(GAF).Sheets(Sheet1).Range("A2").Value & ".csv", FileFormat:=xlCSV

Any help?

Or.......

 ActiveWorkbook.SaveAs Filename:="C:\User\Test.CSV" & Test1Str & " " & TestStr & " " & ActiveWorkbook.Name

Technically works but not exactly a CSV FILE... I have tried putting in ".csv", xlCSV FileFormat:=xlCSV also gives me errors.

`Workbooks(GAF).Sheets(Sheet1)` Is `GAF` a variable? If it is the name of the workbook then it has to be in double quotes. Similarly the case with `Sheet1` like `Workbooks("GAF").Sheets("Sheet1")` — Siddharth Rout, Jul 02 '17 at 14:55
I'm not sure this should be closed: people make this mistake quite often, but the title should be something like "How to reference a sheet properly" so searches bring users here, and @SiddharthRout's answer should be front and center — paul bica, Jul 02 '17 at 15:25
Honestly I tried all that and it only gave me a error 9 instead. Apparently 'ActiveWorkbook.SaveAs FileName:=FileFolder & (FileName + " " + newName + "-" + Lpad(CStr(i), "0", 2)) & ".csv", FileFormat:=xlCSV' Works. Maybe if I fiddle around with that I can get it working. Here's hoping... — , Jul 03 '17 at 01:43
ActiveWorkbook.SaveAs "C:\Marthinus.csv", fileformat:=6 Works great. But it completely ignores your macro and saves the original document to a new area without any changes as a csv — , Jul 03 '17 at 01:52
@SiddharthRout Sorry for all responses but another could be ActiveWorkbook.SaveAs Filename:="FileDIrectory heret" & Test1Str & " " & TestStr & " " & ActiveWorkbook.Name fileformat:=6. Yet to get any working apart from the earlier comment but there is a huge problem with that as it ignores changes you made. — , Jul 03 '17 at 01:55
@SiddharthRout ActiveWorkbook.SaveAs Filename:="C:\User\Test.CSV" & Test1Str & " " & TestStr & " " & ActiveWorkbook.Name works but not as a CSV. I believe if I edit this code slightly I should be able to get it working. Only errors thus far — , Jul 03 '17 at 02:08
ActiveWorkbook.SaveAs Filename:="C:\Users\Test" & ThisWorkbook.Name & "-" & ".csv", FileFormat:=xlCSV. This almost did the trick. But the changes are not applied to the CSV file at all. — , Jul 03 '17 at 03:09
Hmm CSV does not seem to like highlighted data or tables. This is a problem indeed... — , Jul 03 '17 at 03:59
If your book has may sheets, then occurr error. csv file has a only one sheet. — Dy.Lee, Jul 03 '17 at 04:02
[This](https://stackoverflow.com/questions/44783923/excel-macro-multiple-sheets-to-csv/44792295#44792295) example convert multiple sheets to csv files. — Dy.Lee, Jul 03 '17 at 04:05

score 0 · Accepted Answer · answered Jul 03 '17 at 04:31

This is one of many codes to converting excel sheet to csv.

Sub test()
    Dim WB As Workbook, csvWB As Workbook
    Dim WS As Worksheet
    Dim myPath As String, myFn As String
    Dim vDB
    myPath = ThisWorkbook.Path & "\"
    myFn = myPath & "test.csv"

    Set WB = ActiveWorkbook
    Set WS = WB.ActiveSheet
    vDB = WS.UsedRange

    Set csvWB = Workbooks.Add(Template:=True)
    With csvWB
        Sheets(1).Range("a1").Resize(UBound(vDB, 1), UBound(vDB, 2)) = vDB
        .SaveAs myFn, xlCSV
        .Close (0)
    End With

    Set csvWB = Nothing


End Sub

How to save a copy of current excel to CSV to new folder?

1 Answers1