macro copy a specific sheet in a workbook and save as csv file format

[RAM72]

Hi all

Have a workbook with several sheets but only one sheet named upload needs to be copied and save on my desktop folder named ready for upload ,however prompts message box to ask on what name it will save that is aaaa.csv or bbbb.csv

Thanks for those who can help

 

[Chihiro]

Can you upload sample workbook? If not, adopt code in the link to your need (change xlText part to one of csv formats; xlCSVMSDOS, xlCSV, xlCSVWindows etc).

To specify file name, just use input box.

http://chandoo.org/forum/threads/need-help-copy-excel-data-and-paste-to-notepad.28125/#post-168392

 

[RAM72]

when in xlsx text format is ok

but in applying macro and opening the csv in my folder , the format changes to general

where there digits 0 in front it becomes 917000 instead of 0917000

I d' ont know where i am going wrong

Sub CopyCSV()
   
    ThisWorkbook.Sheets("MySheet").Copy
    ActiveWorkbook.SaveAs "C:\MyPath\Copy2.csv", FileFormat:=xlCSVMSDOS, CreateBackup:=False
End Sub

 

[Chihiro]

If you open the csv file from note pad, you will notice that leading zero(s) are preserved.

It's when you open csv in Excel that leading zero(s) are removed.

To avoid this issue, follow steps below.

1. Open Excel, New workbook.
2. Go to data tab and select "Get External Data"->"From Text"
3. Open the csv file
4. Follow dialog and when you get to step 3 of 3 highlight the column with leading zero(s) and select "Text" format.

5. Finish.

See result below.

 

[RAM72]

Ok thanks for help
However if i understand your explanation,if i do not open it in excel but load this csv file directly on an interface
It should work provided i do not opened in excel.
I make a try at office on monday.

 

[Chihiro]

If csv is associated with Excel, opening the file by double clicking will also get rid of leading zero(s). Right click on file and "Open With"->Note Pad will show leading zero(s).

If you are just importing into another tool/system, just import csv as is and it should work fine.

 

[RAM72]

Thanks for help and explanation