Converting Excel File to CSV

[Slicemahn]

Hi VBExpress Nation!

I am hopeful if a second pair eyes will help out: I am trying to capture cells in my spreadsheet that may have commas and I need to keep the content of that cell together. My code is below

Sub Create CSVDim fname as strong
Dim LText as String
Dim CSText as Variant
Dim myRange as Range, I, j
fname = ThisWorkbook.Path & “ExportData” & “.csv”
 Open fname for Output as #1
With Active Sheet
Set myRange = .Range(“Report”)
For I= 1 to myRange.Rows.Count
For j = 1 to myRange.Columns.Count
If Instr(myRng.Cells(i,j),”,”)> 0 then
CSText = “‘“ & myRange.Cells(I,j) & “‘“
LText = LText & CSText
CSText = “”
else 
LText = IIf(j=1, “”,LText & “,”) & myRange.Cells(I,j)
End If
Next j
print #1, LText
Next I
End With
Msgbox “All complete!”, vbInformation, “Data to CSV Format”
End Sub

Thanks in advance to everyone for their help.

Slice

[大灰狼1976]

Hi Slicemahn!

Sub Create_CSV()
Dim fname As String
Dim LText As String
Dim CSText As Variant
Dim myRange As Range, I, j
fname = ThisWorkbook.path & "\ExportData" & ".csv"
Open fname For Output As #1
With ActiveSheet
    Set myRange = .Range("Report")
    For I = 1 To myRange.Rows.Count
        For j = 1 To myRange.Columns.Count
            If InStr(myRange.Cells(I, j), ",") > 0 Then
                CSText = "‘" & myRange.Cells(I, j) & "‘"
                LText = LText & CSText
                CSText = ""
            Else
                LText = IIf(j = 1, "", LText & ",") & myRange.Cells(I, j)
            End If
        Next j
        Print #1, LText
    Next I
End With
Close #1
MsgBox "All complete!", vbInformation, "Data to CSV Format"
End Sub

[Slicemahn]

Hi, this is pretty good but it still doesn't solve the problem: If a cell has the value "Simpson, Bart", the .csv format will seperate those names into columns that it should not. I would like to keep the name together in the same format and I am trying to find a solution to do so.

Slicemahn

[大灰狼1976]

Sub Create_CSV()
Dim fname As String
Dim LText As String
Dim CSText As Variant
Dim myRange As Range, I, j, sTmp$
fname = ThisWorkbook.Path & "\ExportData" & ".csv"
Open fname For Output As #1
With ActiveSheet
    Set myRange = .Range("Report")
    For I = 1 To myRange.Rows.Count
        sTmp = "": LText = ""
        For j = 1 To myRange.Columns.Count
            If InStr(myRange.Cells(I, j), ",") > 0 Then
                CSText = Chr(34) & myRange.Cells(I, j) & Chr(34)
            Else
                CSText = myRange.Cells(I, j)
            End If
            LText = LText & sTmp & CSText
            sTmp = ","
        Next j
        Print #1, LText
    Next I
End With
Close #1
MsgBox "All complete!", vbInformation, "Data to CSV Format"
End Sub