How to preserve formatting in excel after saving the file as .csv through vba

    I have macro that creates a new workbook process the data and converts column N into text and saves in .csv format.

    Now, if Column N has value=052880806 saved as text. After saving as .csv that value truncates leading 0(s) and shows in a scientific manner 5.29E+14.

    While debugging I see the text format preserving until the file save line, after wb.Saveas book1.csv,XLCSV line the formats are gone.


    I cannot have the excel saved in other formats except .csv because the file is getting loaded to another application which allows only .csv type of files. Is there a workaround?

    Are you saying that the format changes on-screen as soon as you execute that line? I've never seen that.

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

    Sounds like you're reopening the file in Excel. When you do that, Excel interprets the data as numeric and converts the format. Open the file in Notepad and you'll see that it is stored correctly.

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

    Yes, when I opened in notepad the values are correctly shown. I'm opening it in excel since that excel.csv should be fed to another application and I cannot upload .txt which is the notepad version of the same.


    Is it possible to prevent the conversion when opening in excel?

    Why do you need to reopen it in Excel if it's going to another application? I was only suggesting opening it in Notepad so that you could see the CSV file is actually correctly formatted.

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login