Solved

How to create a pipe delimeted file from Excel?

Posted on 2011-02-23
2
286 Views
Last Modified: 2012-05-11
How to create a pipe delimeted file from Excel?
0
Comment
Question by:kgittinger
2 Comments
 
LVL 81

Accepted Solution

by:
zorvek (Kevin Jones) earned 250 total points
ID: 34964925
The VBA routines below export a worksheet as a text file with a custom field delimiter. There are three routines:

ExportWorksheetWithCustomDelimiterQuery - This is a "wrapper" routine that can be called from VBA or as a macro. It queries the user for the export file path parameter and field delimiter parameter and then calls ExportWorksheetWithCustomDelimiter with those parameters using the currently active worksheet for the worksheet to be exported. This is the easiest way to export a worksheet without writing any additional VBA code.

ExportWorksheetWithCustomDelimiterDefault - This is a "wrapper" routine that can be called from VBA or as a macro. It calls ExportWorksheetWithCustomDelimiter with a default set of parameters. These default parameters can be changed as desired. Use this routine when the export file path and field delimiter are the same each time.

ExportWorksheetWithCustomDelimiter - This is the main routine that does the work. This routine can only be called from VBA code. The parameters to ExportWorksheetWithCustomDelimiter are explained in the routine.

Note that the resulting output contains the same values as formatted on the worksheet.

To implement this solution add the code below to any general code module.

[Begin Code Segment]

Public Sub ExportWorksheetWithCustomDelimiterQuery()

' Call the routine ExportWorksheetWithCustomDelimiter using a queried set of
' parameter values.

   Dim FileName As String
   Dim FilePath As String
   Dim Delimiter As String
   
   If InStrRev(ActiveWorkbook.Name, ".") = 0 Then
      FileName = ActiveWorkbook.Name
   Else
      FileName = Left(ActiveWorkbook.Name, InStrRev(ActiveWorkbook.Name, ".") - 1)
   End If
   FilePath = Application.GetSaveAsFilename(FileName & ".txt", "Text File (*.txt), *.txt")
   If FilePath = "False" Then Exit Sub
   Do
      Delimiter = Application.InputBox("Enter the field delimiter:", "Export Worksheet With Custom Delimiter", Type:=2)
      If Delimiter = "False" Then Exit Sub
      If Len(Delimiter) > 0 Then Exit Do
      If MsgBox("Enter a field delimiter or click Cancel.", vbOKCancel, "Export Worksheet With Custom Delimiter") = vbCancel Then Exit Sub
   Loop
   
   ExportWorksheetWithCustomDelimiter ActiveSheet, FilePath, Delimiter

End Sub


Public Sub ExportWorksheetWithCustomDelimiterDefault()

' Call the routine ExportWorksheetWithCustomDelimiter using a predefined set of
' parameter values.

   ExportWorksheetWithCustomDelimiter "Sheet1", "C:\Full\Path\To\OutputFile.txt", "|"

End Sub


Public Sub ExportWorksheetWithCustomDelimiter( _
      ByVal SourceWorksheet As Variant, _
      ByVal FilePath As String, _
      ByVal Delimiter As String, _
      Optional ByVal PrefaceText As String, _
      Optional ByVal TrailingText As String _
   )

' Exports the source worksheet as a text file with a custom field delimiter.
'
' Syntax
'
' ExportWorksheetWithCustomDelimiter(SourceWorksheet, FilePath, Delimiter)
'
' SourceWorksheet - The name of or a reference to a worksheet.
'
' FilePath - The full path to the export file.
'
' Delimiter - One or more characters to use as the field delimiter.
'
' PrefaceText - One or more characters to place in front of every record.
'
' TrailingText - One or more characters to place after every record.

   Dim DisplayAlerts As Boolean
   Dim FileNumber As Long
   Dim FileData As String
   
   If VarType(SourceWorksheet) = vbString Then SourceWorksheet = ActiveWorkbook.Sheets(SourceWorksheet).Name
   
   ' Create copy of source worksheet in new workbook
   SourceWorksheet.Copy
   
   ' Save copy as tab delimited text file and close
   DisplayAlerts = Application.DisplayAlerts
   Application.DisplayAlerts = False
   ActiveWorkbook.SaveAs Filename:=FilePath, FileFormat:=xlText
   ActiveWorkbook.Close SaveChanges:=False
   Application.DisplayAlerts = DisplayAlerts
   
   ' Read file into string variable and delete file
   FileNumber = FreeFile
   Open FilePath For Binary Access Read Write As FileNumber
   FileData = StrConv(InputB(LOF(FileNumber), FileNumber), vbUnicode)
   Close FileNumber
   Kill FilePath
   
   ' Replace all tabs with special character
   FileData = Replace(FileData, Chr(9), Delimiter)
   
   ' Add preface text is specified
   If Len(PrefaceText) > 0 Then
      FileData = PrefaceText & Replace(FileData, vbCrLf, vbCrLf & PrefaceText)
      FileData = Left(FileData, Len(FileData) - Len(PrefaceText))
   End If
   
   ' Add trailing text if specified
   If Len(TrailingText) > 0 Then
      FileData = Replace(FileData, vbCrLf, TrailingText & vbCrLf)
   End If
   
   ' Right modified text back out to same file
   Open FilePath For Binary Access Read Write As FileNumber
   Put FileNumber, , FileData
   Close FileNumber

End Sub

[End Code Segment]

Kevin
0
 
LVL 2

Assisted Solution

by:PIXADMIN
PIXADMIN earned 250 total points
ID: 34964933
1. Click "Start," then select "Settings," then "Control Panel." Click on "Regional and Language Options."

2. Click on the button labeled "Customize" and highlight the data in the field labeled "List Separator." Type the pipe delimiter "|" in the field replacing the previous list separator.

3. Click "OK" on the window titled "Customize Regional Options." Click "OK" on the window titled "Regional and Language Options."

4. Open Excel and create the content you wish to save in the pipe-delimited format.

5 Click on "File" on the Excel menu bar then select the option "Save As."

6 Input the name for the file in the text field labeled "File name" and select the value "CSV (Comma delimited)(*.csv)."

7 Click on the button labeled "Save" to save the file to your computer as a pipe-delimited format.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now