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

 

[Legacy 68668]

Then the file is damaged.

 

[Miya]

Then the file is damaged.

I tried the code on a new workbook and still get the same results, but if i run the code from sheet 2 then it works, if i run the macro from sheet 1 then i need to put sheet2.activate at the starts, then i t works

Sub csv()
Dim ws As Worksheet
Sheet2.Activate
Set ws = Sheets("Sheet2")
ws.SaveAs "U:\TL\PV\CSV\test.csv", xlCSV
ActiveWorkbook.Close True
End Sub

 

[Jaymond Flurrie]

Just as a random question/note, does it matter if the sheet "programmatic name" is changed from VBE?

 

[GTO]

I tried the code on a new workbook and still get the same results, but if i run the code from sheet 2 then it works, if i run the macro from sheet 1 then i need to put sheet2.activate at the starts, then i t works

Sub csv()
Dim ws As Worksheet
Sheet2.Activate
Set ws = Sheets("Sheet2")
ws.SaveAs "U:\TL\PV\CSV\test.csv", xlCSV
ActiveWorkbook.Close True
End Sub

Did you try what I said at #10?

ActiveWorkbook.Close False

Mark

 

[Legacy 68668]

If it is working, no reason to not to "Activate", normally no need to.

 

[Miya]

Did you try what I said at #10?

ActiveWorkbook.Close False

Mark

OK that works but i need to active workbook saved as well before closing, as i will lose data

 

[Legacy 68668]

Close/False shoud not make any difference in this case.

 

[GTO]

OK that works but i need to active workbook saved as well before closing, as i will lose data

Sorry, do you mean you want to save ThisWorkbook before doing the SaveAs (.csv)? If so:

Sub csv()
Dim ws As Worksheet
    ThisWorkbook.Save
 
    Set ws = Sheets("Sheet2")
 
    ws.SaveAs "U:\TL\PV\CSV\test.csv", xlCSV
    ActiveWorkbook.Close False
End Sub

I agree with Seiya, both that there's no reason to select/activate and that once a reference is set to the sheet, this remains explicit. The problem was that when you tossed the TRUE in behind the ActiveWorkbook.Close, you were essentially doing the same thing as when that dang irratating dialog (are you really really sure? You'll lose formatting, are you super sure???) comes up after manually saving as .csv. Now since sheet 1 happens to be active, the TRUE means you were overwriting the already saved sheet2.csv.

Does that make sense?

Mark

Edit: Hi Seiya I belive if you test, you'll see that the False (vs True) does indeed matter.

 

[Miya]

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

Sorry, do you mean you want to save ThisWorkbook before doing the SaveAs (.csv)? If so:

Sub csv()
Dim ws As Worksheet
    ThisWorkbook.Save
 
    Set ws = Sheets("Sheet2")
 
    ws.SaveAs "U:\TL\PV\CSV\test.csv", xlCSV
    ActiveWorkbook.Close False
End Sub

I agree with Seiya, both that there's no reason to select/activate and that once a reference is set to the sheet, this remains explicit. The problem was that when you tossed the TRUE in behind the ActiveWorkbook.Close, you were essentially doing the same thing as when that dang irratating dialog (are you really really sure? You'll lose formatting, are you super sure???) comes up after manually saving as .csv. Now since sheet 1 happens to be active, the TRUE means you were overwriting the already saved sheet2.csv.

Does that make sense?

Mark

Edit: Hi Seiya I belive if you test, you'll see that the False (vs True) does indeed matter.

 

[Legacy 68668]

Edit: Hi Seiya I belive if you test, you'll see that the False (vs True) does indeed matter.

Ah, is that so ?

Thanks, I never thought that.