Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 | | |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
refer some letter from one cell(pipe/mech or mech/pipe (reqd: pi) | Excel Worksheet Functions | |||
Pipe-Delimited CSv File | Excel Discussion (Misc queries) | |||
How can I convert tab delimited files to pipe delimited? | Excel Discussion (Misc queries) | |||
Save CSV file as Pipe delimited text file | Excel Discussion (Misc queries) | |||
Import Pipe Delimited File, Parse out certian Fields, create new f | New Users to Excel |