how can I export data by VBA and specify type of delimitor

  • Thread starter Thread starter Andrew Chan
  • Start date Start date
A

Andrew Chan

I want to export data from excel by VBA and specify to use ',' as delimitor.
but I can only export a file rename to .csv. but when I open it, it still a
excel file, but not a text file.
How can I do it? Million thanks.
 
You could (temporarily???) change your windows list separator:

Windows start button|Settings|control panel
regional and language Settings Options applet.
regional Options tab|customize button
List separator near the bottom of the dialog.

Or you could use a macro. You could borrow some code from one of these sites:

Chip Pearson's:
http://www.cpearson.com/excel/imptext.htm

J.E. McGimpsey's:
http://www.mcgimpsey.com/excel/textfiles.html

Earl Kiosterud's Text Write program:
www.smokeylake.com/excel
(or directly: http://www.smokeylake.com/excel/text_write_program.htm)

(You may want to try Earl's first. His may do what you want out of the box.)

And if you open Notepad first, then file|open the .csv file, you'll see that
it's a text file. Doubleclicking on the .csv file will open it in excel,
though.
 
Back
Top