Renaming Excel to CSV Using VBA

Question

I am trying to write a Macro in VBA using Excel 2010 that is linked to a button in a worksheet (located in Cell A1). When the button is pressed, a CSV should be generated that deletes columns A and B, so that column C effectively becomes column A. I am trying to also name the newly generated CSV based on the cell contents from cell A30 within the worksheet, but when I run the macro I am getting an error on the SaveAs function. I believe this is because cell A30 is deleted later on in the script. My question is where there is a way to use the Range (A30) to name the new CSV while still deleting that cell later on within the new CSV all within the same sub? I'm still new to VBA, so it is unclear to me why this is an issue when I would think that each command is executed sequentially, so once the CSV is saved with the new name, I would think I'd be able to delete the source of the file name.

Sub rpSaveCSV()

Dim ws As Worksheet
Set ws = ActiveSheet

'Saves current sheet of tracker as a CSV
ws.SaveAs "Y:\Drive\Youth " & Range("A30") & " .csv", FileFormat:=xlCSV

'Copies entire sheet and pastes values to get rid of formulas
  ws.Select
    Cells.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues
    ActiveSheet.Select
    Application.CutCopyMode = False

'Deletes first two columns and all remaining columns without content
Range("A:B").EntireColumn.Delete
Range("BI:XFD").EntireColumn.Delete

'Saves panel CSV
ActiveWorkbook.Save

'Opens Tracker up again
Workbooks.Open Filename:="Y:\Drive\Tracker.xlsm"

End Sub

See how you got Dim ws As Worksheet there? That's declaring a local variable. You can very well do Dim fileName As String and do fileName = Range("A30").Value, and reuse it later down the road. — Mathieu Guindon
– Mathieu Guindon, Commented Apr 30, 2018 at 21:41
I am getting an error on the SaveAs function - what error are you getting exactly? — Mathieu Guindon
– Mathieu Guindon, Commented Apr 30, 2018 at 21:43
As for deleting files, you can do that with the Kill keyword... provided that the kill target is closed. — Mathieu Guindon
– Mathieu Guindon, Commented Apr 30, 2018 at 21:55

Mathieu Guindon · Accepted Answer · 2018-04-30 21:42:44Z

1

Declare a variable to hold the string value:

Dim filename as String
filename = Range("A30")

'verify that "Y:\Drive\Youth " & filename & " .csv" is a valid file name:
Debug.Print "Y:\Drive\Youth " & filename & " .csv" ' looks right? Ctrl+G to find out
ws.SaveAs "Y:\Drive\Youth " & filename & " .csv", FileFormat:=xlCSV

'...delete columns...

'...do stuff...

Debug.Print filename 'value is still here!

answered Apr 30, 2018 at 21:42

Mathieu Guindon

71.5k8 gold badges113 silver badges250 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Ryan Over a year ago

Thank you for the quick reply! I am still getting the same Run-time error '1004': Method 'SaveAs' of object'_Worksheet' failed.

Mathieu Guindon Over a year ago

@Ryan one problem is that Range("A30") is implicitly referring to whatever worksheet is currently active: it's like writing ActiveSheet.Range("A30").Value, which may or may not be what you need to be looking at. Use more explicit code, specify what the sheet is and use that object to qualify that Range call. What's the value of filename? Did you check with Ctrl+G? Does it look right?

JBStovers · Accepted Answer · 2018-04-30 21:58:00Z

0

I would recommend learning to use arrays with Excel data. It can often be far simpler than trying to replication Excel Application functions in VBA. And it is far more efficient/fast.

Here is a function that feeds the data to an array, and then prints the array to a csv (text) file.

Sub CreateCsvFromWorkSheet(leftColumn, rightColumn, FileName)
    Set ws = ActiveSheet
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set f = fso.CreateTextFile(FileName, True)

    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row

    ar = ws.Range(ws.Cells(1, leftColumn), ws.Cells(lastRow, rightColumn))
    For i = 1 To UBound(ar, 1)
        strLine = ""
        For j = 1 To UBound(ar, 2)
            strLine = strLine & ar(i, j) & ","
        Next
        strLine = Left(strLine, Len(strLine) - 1)
        f.WriteLine strLine
    Next
    f.Close
End Sub

You can call the function like this:

Sub TestRun()
    FileName = "Y:\Drive\Youth " & Range("A30") & " .csv"
    CreateCsvFromWorkSheet 3, 60, FileName
    MsgBox "Complete."
End Sub

answered Apr 30, 2018 at 21:58

JBStovers

3183 silver badges13 bronze badges

10 Comments

Mathieu Guindon Over a year ago

Are you saying writing the CSV file one cell at a time will be "far more efficient" than using Excel's built-in SaveAs CSV format?

JBStovers Over a year ago

@Mathieu No. But it may be faster than copying and pasting all cells in the worksheet, then deleting two columns. I really don't know if it's faster in this case. My point was that it is extremely useful to learn arrays.

JBStovers Over a year ago

And when you write an Excel range to an array, then iterate through the elements of the array, that is not the same as iterating through "one cell at a time". When you iterate cells in a worksheet there is communication that occurs between the Excel application and VBA; and that takes time. Arrays are native to VBA and are lightning fast compared to iterating through a range "one cell at a time".

Mathieu Guindon Over a year ago

Yes, I know that, thank you. I meant "one cell/value at a time". You're still iterating thousands of times to write as many things to a file that could be written with one single native operation, while reinventing the CSV wheel.

JBStovers Over a year ago

Hey, you did a great job of quickly solving the OP's problem, and I admire that.

|

Collectives™ on Stack Overflow

Renaming Excel to CSV Using VBA

2 Answers 2

2 Comments

10 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

10 Comments

Your Answer

Sign up or log in

Post as a guest

Related