VBA macro (pipe delimited) code help

[Barb]

Hi,

I'm not very good with code, so I found the code below somewhere off
the net that exports a worksheet to a pipe delimited .txt file.
Everything works as expected except for exporting currency. It leaves
off the zeros and the zeros are needed.

Example: $45.00 to | 45. | or $45.80 to | 45.8 |

Is it possible to export with the zeros included?
Like: $45.00 to | 45.00 | or $45.80 to | 45.80 |

Thanks for any help,
Barb

*****Pipe Delimited Macro*****

Sub PipeDelimited()

' Exports to PipeDel.txt file

Dim SrcRg As Range

Dim CurrRow As Range

Dim CurrCell As Range

Dim CurrTextStr As String

Dim ListSep As String

Dim DataTextStr As String

ListSep = "|"

Set SrcRg = ActiveSheet.UsedRange

Open "C:\windows\desktop\PipeDel.txt" For Output As #1

For Each CurrRow In SrcRg.Rows

CurrTextStr = ""

For Each CurrCell In CurrRow.Cells

CurrTextStr = CurrTextStr & CurrCell.Value & ListSep

Next

While Right(CurrTextStr, 1) = ListSep

CurrTextStr = Left(CurrTextStr, Len(CurrTextStr) - 1)

Wend

'Added next line to put | at end of each line

CurrTextStr = CurrTextStr & ListSep

Print #1, CurrTextStr

Next

Close #1

End Sub

 

[J.E. McGimpsey]

one way:

Change

CurrTextStr = CurrTextStr & CurrCell.Value & ListSep

to

CurrTextStr = CurrTextStr & Format(CurrCell.Value,"00.00") _
& ListSep

 

[J.E. McGimpsey]

Note that using .Text will include the "$" in the text file, which
may be OK but wasn't in the original specification.

 

[Barb]

Perfect!! That was exactly what I was looking for. Thank you kindly
J.E. for your help and keeping it simple for me.

Barb

| one way:
|
| Change
|
| CurrTextStr = CurrTextStr & CurrCell.Value & ListSep
|
| to
|
| CurrTextStr = CurrTextStr & Format(CurrCell.Value,"00.00") _
| & ListSep
|
|
| In article <[email protected]>,
|
| > Hi,
| >
| > I'm not very good with code, so I found the code below somewhere
off
| > the net that exports a worksheet to a pipe delimited .txt file.
| > Everything works as expected except for exporting currency. It
leaves
| > off the zeros and the zeros are needed.
| >
| > Example: $45.00 to | 45. | or $45.80 to | 45.8 |
| >
| > Is it possible to export with the zeros included?
| > Like: $45.00 to | 45.00 | or $45.80 to | 45.80 |
| >
| > Thanks for any help,
| > Barb
| >
| > *****Pipe Delimited Macro*****
| >
| > Sub PipeDelimited()
| >
| > ' Exports to PipeDel.txt file
| >
| > Dim SrcRg As Range
| >
| > Dim CurrRow As Range
| >
| > Dim CurrCell As Range
| >
| > Dim CurrTextStr As String
| >
| > Dim ListSep As String
| >
| > Dim DataTextStr As String
| >
| > ListSep = "|"
| >
| > Set SrcRg = ActiveSheet.UsedRange
| >
| > Open "C:\windows\desktop\PipeDel.txt" For Output As #1
| >
| > For Each CurrRow In SrcRg.Rows
| >
| > CurrTextStr = ""
| >
| > For Each CurrCell In CurrRow.Cells
| >
| > CurrTextStr = CurrTextStr & CurrCell.Value & ListSep
| >
| > Next
| >
| > While Right(CurrTextStr, 1) = ListSep
| >
| > CurrTextStr = Left(CurrTextStr, Len(CurrTextStr) - 1)
| >
| > Wend
| >
| > 'Added next line to put | at end of each line
| >
| > CurrTextStr = CurrTextStr & ListSep
| >
| > Print #1, CurrTextStr
| >
| > Next
| >
| > Close #1
| >
| > End Sub
| >
| >

 

[James S]

That is true.

I had formatted the cells to show decimal places but not
the "$" sign, so when using ".Text" if the cell
contained "45" it would show as "45.00". I agree that your
code:
CurrTextStr = _
CurrTextStr & Format(CurrCell.Value,"00.00") & ListSep
is probably better for this case.

Thanks.

 

[Barb]

Where do I add (.Text) in the line? I noticed that when I first ran
your original code it changed non-currency numbers also by adding
".00" at the end.

Example: 12345 to | 12345.00 |

So I added a $ sign to "$00.00" hoping that it would only change
currency numbers. This worked great on a Win98 - Office XP but when
it was ran on a WinXP - Office XP it added the "$" and ".00" to all
the non-currency numbers too. I apologize for not mentioning this
initially on my first post, I didn't think it would be an issue
between the two operating systems. Is there any way to work around
this?

Thank you,
Barb

| Note that using .Text will include the "$" in the text file, which
| may be OK but wasn't in the original specification.
|
| In article <[email protected]>,
|
| > Hi Barb,
| >
| > Another way you could do it would be to
| >
| > CHANGE
| > CurrTextStr = CurrTextStr & CurrCell.Value & ListSep
| >
| > TO
| > CurrTextStr = CurrTextStr & CurrCell.Text & ListSep
| >
| > Hope that helps as well.