CODEWITHSUNDEEP
excelvbacsvsave-as
Ryan
Ryan

Reputation: 3

Renaming Excel to CSV Using VBA

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

Upvotes: 0

Views: 914

Answers (2)

JBStovers
JBStovers

Reputation: 318

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

Upvotes: 0

Mathieu Guindon
Mathieu Guindon

Reputation: 71217

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!

Upvotes: 1

Related Questions