text file page breaks
MZ Tools makes life easier for the Excel VBA coder
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: text file page breaks

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    Seattle Area, WA
    Posts
    212
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    I have a macro that uploads a .prn file into my workbook, and then formulas run off of that uploaded data. My problem is sometimes the text file puts in page breaks which appear as small rectangles after the number (at the end of a line). With those there, my formulas do not work. Is there anyway to make these go away either with a command in the macro during the upload process, or by running a macro to "search and destroy" these pesky little boxes....(PS the boxes are always there in the file, but sometimes that fall in areas that don't bother me, like after a label, so they are not always in the same place)

    Any ideas??

    _________________
    Russell

    [ This Message was edited by: ermccarthy on 2002-02-21 11:06 ]

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    It might be easiest to replace these characters using a system editor such as EditPlus or BBEdit. Using Excel's CODE function can you tell us what the ASCII code is for these characters?

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Location
    Seattle Area, WA
    Posts
    212
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    ok......so I copied the page break into a cell and type in the next cell: =Code(A1) this returned a value of 12.

    Therefore how do I tell it to search the entire area looking for this character and delete it, using this value??

    Russell

  4. #4
    MrExcel MVP Russell Hauf's Avatar
    Join Date
    Feb 2002
    Location
    Portland, OR Area - USA
    Posts
    1,605
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Are you using Excel 2000? If so, try this (you must have the range that may contain page breaks highlighted):

    Code:
    Sub RemovePageBreaks()
        Dim cl As Range
    
        For Each cl In Selection
            If InStr(cl.Text, Chr(12)) Then
                cl = Replace(cl.Text, Chr(12), "")
            End If
        Next cl
    End Sub
    If you're not using XL 2K, let me know. Otherwise, hope this helps!

    -Russell

  5. #5
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    On 2002-02-21 11:05, ermccarthy wrote:
    I have a macro that uploads a .prn file into my workbook, and then formulas run off of that uploaded data. My problem is sometimes the text file puts in page breaks which appear as small rectangles after the number (at the end of a line). With those there, my formulas do not work. Is there anyway to make these go away either with a command in the macro during the upload process, or by running a macro to "search and destroy" these pesky little boxes....(PS the boxes are always there in the file, but sometimes that fall in areas that don't bother me, like after a label, so they are not always in the same place)

    Any ideas??

    _________________
    Russell

    [ This Message was edited by: ermccarthy on 2002-02-21 11:06 ]
    Or, simply...

    Sub Macro1()
    Selection.Replace What:=Chr(12), Replacement:="", LookAt:=xlPart, SearchOrder _
    :=xlByRows, MatchCase:=False
    End Sub

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com