Saving to CSV File

Livin404

Active Member
Joined
Jan 7, 2019
Messages
393
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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:

VBA Code:
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
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,520
It's unclear to me what problem you're having, can you please clarify?
 

Livin404

Active Member
Joined
Jan 7, 2019
Messages
393
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,520
First, you can define a dynamic range by replacing...

VBA Code:
Set rngToSave = Range("A2:AB350")

with

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

Then, try replacing...

VBA Code:
Print #fNum, Left(csvVal, Len(csvVal) - 2)

with

VBA Code:
Print #fNum, Left(csvVal, Len(csvVal) - 1)

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

VBA Code:
Close #fNum

Hope this helps!
 

Livin404

Active Member
Joined
Jan 7, 2019
Messages
393
Office Version
  1. 365
  2. 2019
Platform
  1. Windows

ADVERTISEMENT

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

Livin404

Active Member
Joined
Jan 7, 2019
Messages
393
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Would I try both those ranges you typed.
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,520

ADVERTISEMENT

No, you should replace your range with...

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

Livin404

Active Member
Joined
Jan 7, 2019
Messages
393
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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:
Excel Formula:
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!

VBA Code:
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

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,520
Sorry, but there's a typo in that line that I gave you, it should be...

VBA Code:
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...

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

Livin404

Active Member
Joined
Jan 7, 2019
Messages
393
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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,

VBA Code:
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 = ""
 

Watch MrExcel Video

Forum statistics

Threads
1,130,174
Messages
5,640,593
Members
417,152
Latest member
DayTimeSeby

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top