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
<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> On 12 Jun, 09:30, "NickHK" <TungChe...@Invalid.com> wrote:
> > 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
> >
> > <philipaaldri...@googlemail.com> wrote in message
> >
> > news:(E-Mail Removed)...
> >
> >
> >
> > > 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- Hide quoted text -
> >
> > - 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
>
|
Similar Threads
