Saving to CSV File

[Livin404]

Greetings I'm trying to save a range in an Excel file to a Comma Delimited File (CSV). The VBA I have thus far is definitely on the right track for it does keep the format from the source file and saves it in the proper place. However, it will not save all the rows. This has to be uploaded into another system as a CSV file, so no header or empty cells below the last row of data. As you see with my current VBA I am using ("A2:AB2") which currently puts everything in one row (Row 2). The number of rows will vary, so that will have to kept in mind. My current VBA is:

Sub exportRangeToCSVFile()

    Dim myCSVFileName As String
    Dim myWB As Workbook
    Dim rngToSave As Range
    Dim fNum As Integer
    Dim csvVal As String

    Set myWB = ThisWorkbook
    myCSVFileName = myWB.Path & "\" & "XMan-" & VBA.Format(VBA.Now, "dd MMM yyyy hhmm") & ".csv"
    csvVal = ""
    fNum = FreeFile
    Set rngToSave = Range("A2:AB350")

    Open myCSVFileName For Output As #fNum

    For i = 1 To rngToSave.Rows.Count
        For j = 1 To rngToSave.Columns.Count
            csvVal = csvVal & Chr(34) & rngToSave(i, j).Value & Chr(34) & ","
        Next
        Print #fNum, Left(csvVal, Len(csvVal) - 2)
        csvVal = ""
    Next

    Close #fileNumber
End Sub

 

[Livin404]

I do not actually see the parenthesis or the comma when I look at the CSV, but when I go to upload it to the destination file, I see them then, and of course that is causing all sorts of errors.

 

[GWteB]

I do not actually see the parenthesis or the comma when I look at the CSV

At this point your code is giving a correct CSV with double quotation marks and commas as delimiters. It's consistent and all rows are incorporated.
If you're having troubles with the application using your CSV, probably the commas should be kept and the quotations marks should be dropped.

 

[Livin404]

At this point your code is giving a correct CSV with double quotation marks and commas as delimiters. It's consistent and all rows are incorporated.
If you're having troubles with the application using your CSV, probably the commas should be kept and the quotations marks should be dropped.

Exactly what would be the best course of action to drop the quotations? It makes sense since I'm asking for a comma delimitator. I 100% do not want the quotes. If I save a file the traditional way of selecting save AS etc. I never get the quotes. Is there something I can tweak with our current formula.? Thank you,

 

[GWteB]

Try ...

Public Sub exportRangeToCSVFile_r2()

    Dim myCSVFileName   As String
    Dim myWB            As Workbook
    Dim rngToSave       As Range
    Dim fNum            As Long
    Dim csvVal          As String
    
    Dim i As Long, j As Long
    
    Set myWB = ThisWorkbook
    myCSVFileName = myWB.Path & "\" & "XMan-" & VBA.Format(VBA.Now, "dd MMM yyyy hhmm") & ".csv"
    csvVal = ""
    fNum = FreeFile
    Set rngToSave = Range("A2:AB350")

    Open myCSVFileName For Output As #fNum

    For i = 1 To rngToSave.Rows.Count - 1
        For j = 1 To rngToSave.Columns.Count - 1
            csvVal = csvVal & rngToSave(i, j).Value & ","
        Next j
        Print #fNum, Left(csvVal, Len(csvVal) - 1)
        csvVal = ""
    Next i
    Close #fNum
End Sub

 

[Livin404]

That's great I had to update the range just a tad, for it wanted to include a bunch of empty rows. It all works exactly the way I hoped it would. Thank you,

Set rngToSave = Range("A3:AB" & Cells(Rows.Count, "A").End(xlUp).Row)

 

[GWteB]

You are welcome and thanks for the feedback.

 

[Livin404]

You are welcome and thanks for the feedback.

Thank you for thanking me. It still works fine, and the exact format I need and want. However I notice that it is only copying out to Column AA. Is there an offset that is causing it not to copy that last column? Thank you so much!

 

[Domenic]

It should be..

    For i = 1 To rngToSave.Rows.Count
        For j = 1 To rngToSave.Columns.Count
            csvVal = csvVal & rngToSave(i, j).Value & ","
        Next j
        Print #fNum, Left(csvVal, Len(csvVal) - 1)
        csvVal = ""
    Next i

.

 

[Livin404]

It should be..

    For i = 1 To rngToSave.Rows.Count
        For j = 1 To rngToSave.Columns.Count
            csvVal = csvVal & rngToSave(i, j).Value & ","
        Next j
        Print #fNum, Left(csvVal, Len(csvVal) - 1)
        csvVal = ""
    Next i

.

Outstanding! Thank you.