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