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
 

Livin404

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

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
1,295
Office Version
  1. 2013
Platform
  1. Windows
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

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

Well-known Member
Joined
Sep 10, 2010
Messages
1,295
Office Version
  1. 2013
Platform
  1. Windows
Try ...

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

Livin404

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

ADVERTISEMENT

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,


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

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
1,295
Office Version
  1. 2013
Platform
  1. Windows
You are welcome and thanks for the feedback.
 

Livin404

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

ADVERTISEMENT

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

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,504
It should be..

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

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

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

Watch MrExcel Video

Forum statistics

Threads
1,129,879
Messages
5,638,800
Members
417,053
Latest member
SaturdayNight

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