Help exporting to delimited file

KGee · Nov 15, 2010

I'm trying to create a pipe delimted file using a text qualifier of double-quotes. I'm trying to use code from the post below but I don't want to read a range of cells. There are 22 variables which I need to export to a file. Some of the variables come from cells in the workbook filled in by the user and the remaining are calculated automatically.

I suppose I could create a sheet and write the variables to the new sheet and read them from there, but I was wondering if there is a way to generate the output file directly from the variables.

http://www.mrexcel.com/forum/showthread.php?t=48756

I need the output file to look like so:
"var1"|"var2"|...|"var21"|"var22"

bertie · Nov 15, 2010

Your problem breaks down into three parts:
Assign values to variables,
Build up an output string,
Output the string to a file.

So keeping it simple, try this:
NB Change the output Folder

Code:

[COLOR=darkblue]Const[/COLOR] [COLOR=red]sDelim = "|"[/COLOR]
 
[COLOR=darkblue]Sub[/COLOR] OutputPipe()
 
   [COLOR=darkblue]Dim[/COLOR] sFile [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
   [COLOR=darkblue]Dim[/COLOR] sText [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
   [COLOR=darkblue]Dim[/COLOR] var1
   [COLOR=darkblue]Dim[/COLOR] var2
   [COLOR=darkblue]Dim[/COLOR] var22
 
   [COLOR=green]'==========================[/COLOR]
   [COLOR=green]'assign values to variables[/COLOR]
   [COLOR=green]'==========================[/COLOR]
   var1 = "var1"
   var2 = "var2"
   var22 = "var22"
 
   [COLOR=green]'===================[/COLOR]
   [COLOR=green]'build output string[/COLOR]
   [COLOR=green]'===================[/COLOR]
   sText = sText & var1 & sDelim
   sText = sText & var2 & sDelim
   [COLOR=green]'[/COLOR]
   '
   sText = sText & var22 & Chr(13)  [COLOR=green]'carriage return[/COLOR]
 
   [COLOR=green]'==============[/COLOR]
   [COLOR=green]'output to file[/COLOR]
   [COLOR=green]'==============[/COLOR]
   sFile = "[COLOR=red]c:\temp\PipeDelimited.txt[/COLOR]"
   [COLOR=darkblue]Open[/COLOR] sFile [COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Append[/COLOR] [COLOR=darkblue]As[/COLOR] #1
   [COLOR=darkblue]Print[/COLOR] #1, sText
   [COLOR=darkblue]Close[/COLOR] #1
 
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

KGee · Nov 18, 2010

Hi Bertie,

I just had a chance to test this out. I needed to include double-quotes around each string so I used Chr(34) where needed. I also had to change the Append option to Output as I only need the one list and they need to be written each time the macro is run.

Thank you so much for your help!

Help exporting to delimited file

KGee

Well-known Member

Excel Facts

bertie

Well-known Member

KGee

Well-known Member

Similar threads

Forum statistics

Share this page

Help exporting to delimited file

KGee

Well-known Member

Excel Facts

bertie

Well-known Member

KGee

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock