Converting Excel File to CSV

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

Code:

---

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

Hi Slicemahn!

Code:

---

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

---

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

Code:

---

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

---