Adding a new file extension for Pipe Delimited files

[philipaaldridge]

Hi All,

Does anybody know a method so that pipe-delimited-separator text files
can be recognised and handle like CSV files in much the same way ( but
without disturbing CSV handling) ?

My problem is that I have a high volume of standard CSV files and pipe
delimited files. At the moment I have to you the file -> import ->
delimited | method..

Can this be achieved by adding a new file extension, say *.PCSV which
open and handle like CSV files?

Cheers in advance.
Phil

 

[NickHK]

Phil,
Try this. You can change the last argument of GetOpenFilename to true (and
handle the array) to deal with multiple files names.

Private Sub CommandButton1_Click()
Dim FileName As Variant

FileName = Application.GetOpenFilename("Pipe Separated File (*.psv), *.psv",
, , , False)

If FileName <> False Then
Workbooks.OpenText FileName, xlDelimited, TextQualifier:=xlDoubleQuote,
Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False,
Other:=True, OtherChar:="|"
End If

End Sub

NickHK

 

[philipaaldridge]

Phil,
Try this. You can change the last argument of GetOpenFilename to true (and
handle the array) to deal with multiple files names.

Private Sub CommandButton1_Click()
Dim FileName As Variant

FileName = Application.GetOpenFilename("Pipe Separated File (*.psv), *.psv",
, , , False)

If FileName <> False Then
Workbooks.OpenText FileName, xlDelimited, TextQualifier:=xlDoubleQuote,
Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False,
Other:=True, OtherChar:="|"
End If

End Sub

NickHK










- Show quoted text -

That's great. Thanks for the swift response!! My issue now is that I
can open the .psv file, but when I come to save the document, it won't
allow me to keep the pipe delimited format. Would I need to create
another macro to save it as pipe delimited in exactly the same format?

And any idea's how?

Thanks again.
Phil

 

[NickHK]

Here's one simple way. It assumes that are no "|" characters in any values.
If there are, or need other formatting (e.g. text quoted, dates delimited
etc) you can that your self.
Check that UsedRange returns what you expect in your situation. If not, use
another method to determine what to export. But you get the idea.

Private Sub CommandButton1_Click()
Dim rngRow As Range
Dim Temp As String
Dim FileNumber As Long

For Each rngRow In ActiveSheet.UsedRange.Rows
Temp = Temp & Join(Application.Transpose(Application.Transpose(rngRow)),
"|") & vbNewLine
Next
Temp = Left(Temp, Len(Temp) - Len(vbNewLine))

FileNumber = FreeFile

Open "C:\OutPipe.psv" For Output As #FileNumber
Print #FileNumber, Temp
Close #FileNumber

End Sub

NickHK