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

 

[Domenic]

It's unclear to me what problem you're having, can you please clarify?

 

[Livin404]

It's unclear to me what problem you're having, can you please clarify?

It is saving as a CSV and in the correct folder, it’s not saving the rows of data properly. Starting on Column A and going to Column AB, I would like the VBA to select the rows included in that range that have data. In the VBA I’m using I put ("A2:AB350") since the most I may ever get at one single time would be 350. The current VBA saves the data but saves it all in row 1. Remember I don’t want the whole worksheet saved as a CSV. Row 1 is the header which is why I began on Row 2.
Thank you

 

[Domenic]

First, you can define a dynamic range by replacing...

Set rngToSave = Range("A2:AB350")

with

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

Then, try replacing...

Print #fNum, Left(csvVal, Len(csvVal) - 2)

with

Print #fNum, Left(csvVal, Len(csvVal) - 1)

By the way, it looks like you have a typo in your posted code. It should be...

Close #fNum

Hope this helps!

 

[Livin404]

Thank you, message received, I’ll give it a go, when I arrive to work later this afternoon.

 

[Livin404]

Would I try both those ranges you typed.

 

[Domenic]

No, you should replace your range with...

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

 

[Livin404]

Thank you, I did work the lines you suggested changing, and I ended up with a Run-time error 1004 Application-defined or object-defined error. Debugging highlighted this line:

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

I've also attached the whole VBA with the recommended changes. Thank you so much!

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:AB" & Cells(Rows.Count, "A").End(xup).Row)

    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) - 1)
        csvVal = ""
    Next

    Close #fNum
End Sub

 

[Domenic]

Sorry, but there's a typo in that line that I gave you, it should be...

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

However, I would suggest that you qualify your reference to your range with references to your workbook and worksheet. So, for example, assuming that the workbook running the code contains the data, and that Sheet1 contains the data, I would use the following code to define your range...

    With ThisWorkbook.Worksheets("Sheet1")
        Set rngToSave = .Range("A2:AB" & .Cells(.Rows.Count, "A").End(xlUp).Row)
    End With

 

[Livin404]

Hey it's actually saving now i the CSV format and the amount of rows is correct, but the strangest thing is, it is inputting parenthesis virtually in every cell. I'm sure it has to do with my original source, I'm thinking it is in the following lines that is causing it. Thank you,

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) - 1)
        csvVal = ""