How to add "" on every cell and saves as csv and open as txt file

[ec4excel]

Hi, I need help to add "" on every cell follow with a , for import files to Oracle. Had wrote a script from sample from internet but after adding these "" to the text in the cell, the formating of the 00 in the cell was gone.

For example, the original data was 12345, I add 00 using custom to make it show as 0012345, but after running the macro to make the cell shows as "0012345", the result became "12345" without the 00 in front.

1st requirement: to add "00" in front of column K,U and AI
2nd requirement: column AB needs to add "0" in front of 1, to show as 01
3rd requirement: column AE needs to show as 00
4th requirement: column AF needs to show as 00000
5th requirement: column AG needs to show as 0000
6th requirement: column AH needs to show as 000
7th requirement: column Y needs to show as blank when with " " with a spacing as blank
FINAL and most important requirement, all cells after convert to txt file, needs to add "" for each data and separate with ,

The result of the txt file should shows like this:

"2022","OMS Invoices","01_OM_WARE_INV","LINE","ABC","WAREHOUSE STORAGE CHARGES","SGD","Sep Invoice","STANDARD GST","31-Aug-2018","0073096","H063","H063","101","1","248.4","248.4","User","1","NET 30","0073096","31-Aug-2018","310070-06","1", ,"REV","248.4","01","000","310001","00","00000","0000","000","0073096","310070-06","1","ABC","101"

I had attached a sample excel file with the 3 macros and 1 txt file as the final desired result. Hoped someone can help.

[Keebellah]

When you select save as a Text file you can choose the options and it automatically inserts " and delimiters of your choice.

[ec4excel]

Hi, Keebellah, sorry, I could not see this option, do you mind to show me?

[Keebellah]

Even you select save as and then options ...

[Keebellah]

My mistake, It used to be possible in the older versions of Excel.
I'll check your macro and see if I can help you with your problem.

[Keebellah]

One thing you have to understand is the fact that a number format is to 'display' the way you look at the values.
If you check your cells where you added the 00 if you look in the formula bar you will NOT see the 00 unless the cell is formatted as text
Excel is not 'way you see is what you get'

[Keebellah]

This macro will do the job I have attached the result too

Please Login or Register  to view this content.

[ec4excel]

Dear Keebellah,

Really appreciate your respond but I could not make the macro work. I had attached the file where I had added your macro and would appreciate it if you could check why it cannot work.

Actually I have no knowledge on how to write Visual Basic programming.

Once again, I would like to say Thank you for your help.

[Keebellah]

Can you tell me what's wrong?
The result file looks the same as the one I sent you earlier. Oracle(1).txt is the previous one

[Keebellah]

If it's that you want to open the file as text file just add the code to open a text file
Google "vba excel open text file in notepad" you'll get enough hits, and... if you ask for VBA then you should at least learn the basics, (free) tutorials enough on the web