I don't know...
Do you have any data with double quotes with comma ?
Sub test()
Dim temp As String, txt As String, i As Long, ii As Long
With Sheets("sheet2").UsedRange
.EntireColumn.AutoFit
For i = 1 To .Rows.Count
For ii = 1 To .Columns.Count
If Cells(i, ii).Text Like "*,*" Then flg = True
temp = temp & "," & IIf(flg,Chr(34), "") & _
.Cells(i, ii).Text & IIf(flg,Chr(34), "")
flg = False
Next
txt = txt & vbCrLf & Mid$(temp, 2) : temp = ""
Next
End With
CreateObject("WScript.Shell").<WBR>CurrentDirectory = "\\server\TL\PV\CSV"
Open "test.csv" For Output As #1
Print #1, Mid$(txt, 3)
Close #1
End Sub
As I said earlier, Cells.Text property get what it looks like on the sheet, so if the cell width is not wide enough to show all the data, it will be cut off.
I'm not sure if that is a case though,
try
Rich (BB code):Sub test() Dim temp As String, txt As String, i As Long, ii As Long With Sheets("sheet2").UsedRange .EntireColumn.AutoFit For i = 1 To .Rows.Count For ii = 1 To .Columns.Count If Cells(i, ii).Text Like "*,*" Then flg = True temp = temp & "," & IIf(flg,Chr(34), "") & _ .Cells(i, ii).Text & IIf(flg,Chr(34), "") flg = False Next txt = txt & vbCrLf & Mid$(temp, 2) : temp = "" Next End With CreateObject("WScript.Shell").<WBR>CurrentDirectory = "\\server\TL\PV\CSV" Open "test.csv" For Output As #1 Print #1, Mid$(txt, 3) Close #1 End Sub
Sub test()
Dim temp As String, txt As String, i As Long, ii As Long
With Sheets("sheet2").UsedRange
For i = 1 To .Rows.Count
For ii = 1 To .Columns.Count
flg = InStr(.Cells(i, ii).Text, ",") > 0
temp = temp & "," & IIf(flg,Chr(34), "") & _
.Cells(i, ii).Text & IIf(flg,Chr(34), "")
Next
txt = txt & vbCrLf & Mid$(temp, 2) : temp = ""
Next
End With
CreateObject("WScript.Shell").<WBR>CurrentDirectory = "\\server\TL\PV\CSV"
Open "test.csv" For Output As #1
Print #1, Mid$(txt, 3)
Close #1
End Sub
Hummm
Does this make any difference ?
Rich (BB code):Sub test() Dim temp As String, txt As String, i As Long, ii As Long With Sheets("sheet2").UsedRange For i = 1 To .Rows.Count For ii = 1 To .Columns.Count flg = InStr(.Cells(i, ii).Text, ",") > 0 temp = temp & "," & IIf(flg,Chr(34), "") & _ .Cells(i, ii).Text & IIf(flg,Chr(34), "") Next txt = txt & vbCrLf & Mid$(temp, 2) : temp = "" Next End With CreateObject("WScript.Shell").<WBR>CurrentDirectory = "\\server\TL\PV\CSV" Open "test.csv" For Output As #1 Print #1, Mid$(txt, 3) Close #1 End Sub
was not picking up the data with the comma at the end.Rich (BB code):If Cells(i, ii).Text Like "*,*" Then flg = True