vba - Excel to CSV cells with pipe delimeter -

how replace comma delimeter pipe "|" delimeter. source:batch convert excel text-delimited files

option explicit dim ofso, myfolder dim xlcsv myfolder="c:\your\path\to\excelfiles\" set ofso = createobject("scripting.filesystemobject") xlcsv = 6 'excel csv format enum call convertallexcelfiles(myfolder) set ofso = nothing call msgbox ("done!") sub convertallexcelfiles(byval ofolder) dim targetf, ofilelist, ofile dim oexcel, owb, owsh set oexcel = createobject("excel.application") oexcel.displayalerts = false set targetf = ofso.getfolder(ofolder) set ofilelist = targetf.files each ofile in ofilelist     if (right(ofile.name, 4) = "xlsx")         set owb = oexcel.workbooks.open(ofile.path)         each owsh in owb.sheets             call owsh.saveas (ofile.path & owsh.name & ".csv", xlcsv)         next         set owsh = nothing         call owb.close         set owb = nothing     end if next call oexcel.quit set oexcel = nothing end sub 

this export pipe routine use, can slow on large data sets:

sub savecopy()     'this savecopy routine output pipe delimited file, bulk inserting oracle db     dim vfilename variant     dim rnglastcell range     dim llastrow long     dim nlastcol integer     dim lcurrrow long     dim ncurrcol integer     dim srowstring string     dim archivefolder string     archivefolder = "c:\temp\"     application.displayalerts = false     vfilename = archivefolder & "daily" & format(now(), "yyyymmddhhmmss") & ".txt"     open vfilename output #1     set rnglastcell = activesheet.range("a1").specialcells(xllastcell)     llastrow = range("a" & rows.count).end(xlup).row     nlastcol = range("xfd1").end(xltoleft).column     lcurrrow = 1 llastrow         srowstring = join(application.transpose(application.transpose(range("a" & lcurrrow).resize(1,nlastcol))),"|")         if len(srowstring) = nlastcol - 1             print #1,         else             print #1, srowstring         end if     next lcurrrow     close #1     'activewindow.close false     application.displayalerts = true end sub