Help exporting to delimited file

[KGee]

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]

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

[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]

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!