Code check

[Miya]

hi what is wrong with this code? i have set ws as Sheet2

With ws
.Copy.SaveAs Filename:= _
"U:\TL\PV\CSV\test.csv", FileFormat:=xlCSV, _
CreateBackup:=False
.Close = False
End With

 

[Miya]

Ok, although i am on a network, the U: is a local as this is my personal drive on the network. I just did a test on my home computer which is local and i still got the same results, blank csv file when i run the code.

You need to use Share name for that drive that should begin with \\

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
            temp = temp & "," & .Cells(i, ii).Text
        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$(temp, 3)
Close #1
End Sub

 

[Legacy 68668]

OOOps,

Sorry

   Print #1, Mid$(temp, 3)

shoule be

   Print #1, Mid$(txt, 3)

 

[Miya]

Seiya , you are a genius, this works. Thanks for your help

OOOps,

Sorry

   Print #1, Mid$(temp, 3)

shoule be

   Print #1, Mid$(txt, 3)

 

[Miya]

Seiya, one issue, i have comma in some of the data, and when i run the code some of data moves

OOOps,

Sorry

   Print #1, Mid$(temp, 3)

shoule be

   Print #1, Mid$(txt, 3)

 

[Legacy 68668]

change

        For ii = 1 To .Columns.Count
            temp = temp & "," & .Cells(i, ii).Text
        Next

to

        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

This will wrap those string includes comma(s) with double quotes, so it will fit to the right column when out put.

 

[Miya]

i get an error "Next without For"

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 = ""
[B]Next[/B]

change

        For ii = 1 To .Columns.Count
            temp = temp & "," & .Cells(i, ii).Text
        Next

to

        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

This will wrap those string includes comma(s) with double quotes, so it will fit to the right column when out put.

 

[Legacy 68668]

Yeah, I think I made it unclear

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

 

[Miya]

Excellent that works, thanks Seiya

Yeah, I think I made it unclear

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

 

[Miya]

Seiya I tried your macro on another set of data which has commas and its moving data to next cell, so not sure why it worked before but not now

Yeah, I think I made it unclear

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

 

[Legacy 68668]

I don't know...

Do you have any data with double quotes with comma ?