Create a flat file in Excel

[GF08]

I need to create a flat file in Excel using the pipe delimiter | as element,
and carriage return line feed as line delimiter.

How do I do this?

 

[Dave Peterson]

You could change the windows list separator (in the regional settings applet in
the control panel) to the vertical bar (just temporarily).

Then save the file as a delimited file.

Or maybe you could create a formula in another cell:
=a1&"|"&b1&"|"&c1&....

And drag down.

Then copy that column and paste into notepad and save the file from notepad.

 

[Bob I]

How about SaveAs CSV and then open the file in NotePad and do a Find
Replace , for | ?

 

[GF08]

I'm not sure which of those options I should use. This file is going to be
used for an inventory file, and I imagine it is used to import the data.
Perhaps Excel isn't the proper format for it?

Right now we're creating a test file to see if this can be submitted through
a system - thus where I'm at at the moment. Of course, one of the
stipulations for this flat file is that is is supposed to be created by an
automated process, and not handmade. Not sure how I can do that without hand
making it first!

 

[Jacob Skaria]

You can try out the below macro. If you are new to macros..

--Set the Security level to low/medium in (Tools|Macro|Security).
--From workbook launch VBE using short-key Alt+F11.
--From menu 'Insert' a module and paste the below code.
--Get back to Workbook.
--Run macro from Tools|Macro|Run <selected macro()>


Sub SaveAsDelimitedFile()
Dim intFile As Integer, strData As String, rngRow As Range
Dim strFile As String

intFile = FreeFile
strFile = "c:\test.txt"

Open strFile For Output As #intFile
For Each rngRow In ActiveSheet.UsedRange.Rows
strData = Join(WorksheetFunction.Transpose( _
WorksheetFunction.Transpose(rngRow)), "|")
Print #intFile, strData
Next
Close #intFile

End Sub

If this post helps click Yes