VBA macro (pipe delimited) code help
 

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

VBA macro (pipe delimited) code help
 

one way:

Change

CurrTextStr = CurrTextStr & CurrCell.Value & ListSep

to

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


In article ,
"Barb" wrote:

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

VBA macro (pipe delimited) code help
 

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

In article ,
"James S" wrote:

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.

VBA macro (pipe delimited) code help
 

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

Barb

"J.E. McGimpsey" wrote in message
...
| one way:
|
| Change
|
| CurrTextStr = CurrTextStr & CurrCell.Value & ListSep
|
| to
|
| CurrTextStr = CurrTextStr & Format(CurrCell.Value,"00.00") _
| & ListSep
|
|
| In article ,
| "Barb" wrote:
|
| 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
|
|

VBA macro (pipe delimited) code help
 

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.

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

In article ,
"James S" wrote:

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.

VBA macro (pipe delimited) code help
 

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

"J.E. McGimpsey" wrote in message
...
| Note that using .Text will include the "$" in the text file, which
| may be OK but wasn't in the original specification.
|
| In article ,
| "James S" wrote:
|
| 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.