saving all sheets as CSV (WITH quotes around fields that contain ",")

mike_littlerock

New Member
Joined
Mar 4, 2014
Messages
1
I have been fighting this for awhile and am at a sticking point. I have a workbook that contains many sheets, and I need to be able to:
generate a CSV file for each sheet
name each .csv file based on the sheet name.
some fields contain a "," and I would like to put double quotes around that (as excel does if I manually save as .csv)

Here is what I have so far. this does work as expected for the first two criteria, but my fields do not line up consistently because of the lack of double quotes.

Public Sub WriteCSV()

Dim iFile As Integer
Dim strText As String, strFileName As String
Dim lngCol As Long, lngRow As Long
Dim wks As Worksheet

iFile = FreeFile()

For Each wks In ActiveWorkbook.Worksheets
If wks.Visible = xlSheetVisible Then
strFileName = "C:/3-4/" & wks.Name & ".csv"
Open strFileName For Output As #iFile


For lngRow = 1 To wks.UsedRange.Rows.Count
For lngCol = 1 To wks.UsedRange.Columns.Count
Print #iFile, wks.Cells(lngRow, lngCol).Text & ",";


Next lngCol
Print #iFile, CurrTextStr
Next lngRow
Close #iFile
End If
Next wks

End Sub
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

Forum statistics

Threads
1,223,243
Messages
6,170,964
Members
452,371
Latest member
Frana

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
Back
Top