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