Save .txt file to .csv in VBA

[lliang27]

Hi List,

User has 4 CSV format files. Since there are extra spaces in the files
when open them from Excel, they have to open each of them using NotePad
and delete the extra spaces. They can't do this from CSV file directly.
If they deleted the extra empty column from Excel, and open it again
with NotePad, the " " will disappear.

They want these steps can be handle using a macro, so they don't need
to manually open each file and delete the spaces.

What I did:

I open csv file as input, use trim(string) to get rid of the extra
spaces. then i write each row to a .txt file.

if i open the txt file using NotePad then do SaveAs to a csv file. it
works fine. when user reviewing it from NotePad, they still see " "
around each field. But my question is, how can I accomplish this step
using VBA?

I tried to open the txt file from Excel and save it as .csv file, when
i open the .csv file from NotePad, all the quotes were gone.

I have posted my questions for a couple of days, but didn't get any
answers so far. Please, if anyone has any idea, please let me know.

Thanks so much.

 

[JE McGimpsey]

Take a look at

http://www.mcgimpsey.com/excel/textfiles.html

I don't see any other posts in these groups on the same subject. You can
find all of your previous posts at

http://groups-beta.google.com/groups?as_ugroup=*excel*&as_uauthors=lliang
27%40yahoo.com&as_scoring=d

If you've posted under another address search the archives using that
address - it's unusual for a request like this to go unanswered:

http://groups.google.com/advanced_group_search?as_ugroup=*excel*

 

[lliang27]

Thanks for your reply.

I can finish the steps to create a txt file with quotes around each
fields. But eventually user needs each file with the extension .csv. So
I tried to open .txt from Excel then save it to .csv, however, if user
view the .csv file with notepad, all quotes are gone. But if i open the
..txt file from notepad, then do a saveas to a .csv format, then review
the new .csv file from notepad, i got the result user wants to see -
quotes with each filed. for example,

"Name", "Street", "City", "State",

But how can i make this a macro step for user?

Please help.

Thanks.

 

[Tom Ogilvy]

http://support.microsoft.com/default.aspx?scid=kb;en-us;291296
Procedure to export a text file with both comma and quote delimiters in
Excel