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

 

[GTO]

Ah, is that so ?

Thanks, I never thought that.

Shucks, no problem. I was hoping you might have info on that last bit about the date defaulting to mm/dd/yyyy from dd/mm/yyyy, as I am awefully clueless when it comes to different versions based on locale...

 

[Legacy 68668]

Thanks Seiya and Mark for the explanation, it works now, just one question why is it when i have data 31/03/2009 in orignal data but when i open CSV file it changes to 3/31/2009

I think it depends on your country setting/locale of Windows.

 

[Miya]

[/FONT]
I think it depends on your country setting/locale of Windows.

Ok i am confused now, how can i change back to 31/03/2009 without manual input

 

[Legacy 68668]

If you want to save csv and exactly how it looks then try

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
Open "U:\TL\PV\CSV\test.csv" For Output As #1
    Print #1, Mid$(temp, 3)
Close #1
End Sub

 

[Miya]

I tried the code but when i opened the CSV file it was completely blank

If you want to save csv and exactly how it looks then try

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
Open "U:\TL\PV\CSV\test.csv" For Output As #1
    Print #1, Mid$(temp, 3)
Close #1
End Sub

 

[Legacy 68668]

Run the code then return with the message.

Sub test()
Dim temp As String, txt As String, i As Long, ii As Long
With Sheets("sheet2").UsedRange
    MsgBox .Address
    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
MsgBox txt
Open "U:\TL\PV\CSV\test.csv" For Output As #1
    Print #1, Mid$(temp, 3)
Close #1
End Sub

 

[Miya]

ok i got the message it has A3:S141 which is perfect as this my data range in source workbook, but the data is not there when i open the file

Run the code then return with the message.

Sub test()
Dim temp As String, txt As String, i As Long, ii As Long
With Sheets("sheet2").UsedRange
    MsgBox .Address
    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
MsgBox txt
Open "U:\TL\PV\CSV\test.csv" For Output As #1
    Print #1, Mid$(temp, 3)
Close #1
End Sub

 

[Legacy 68668]

Is drive "U" on a network ? or local ?

 

[Miya]

Is drive "U" on a network ? or local ?

Its network

 

[Legacy 68668]

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