Results 1 to 7 of 7
  1. #1
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Word to Excel - Labels convert (2003)

    The following macro will export the file to a tab-delimited text file that can be imported into Excel or Access.
    You will have to do some post-processing since not all addresses have the same number of lines.

    Sub ConvertFrames()
    Dim f As Integer
    Dim frm As Frame
    Dim par As Paragraph
    Dim strLine As String
    Dim strPar As String
    f = FreeFile
    Open "Labels.txt" For Output As #f
    For Each frm In ActiveDocument.Frames
    strLine = ""
    For Each par In frm.Range.Paragraphs
    strPar = par.Range.Text
    strLine = strLine & vbTab & Left(strPar, Len(strPar) - 1)
    Next par
    If Not strLine = "" Then
    strLine = Mid(strLine, 2)
    Print #f, strLine
    End If
    Next frm
    Close #f
    End Sub

  2. #2
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Jacksonville,NC, USA
    Posts
    705
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Word to Excel - Labels convert (2003)

    Hans,
    OK. I am not the best at reading code explicitly but when you say post-processing do you mean I put an "Paragraph" (by pressing enter) mark at the end of each address? I have converted it into a single column of text right now with only line breaks between each field right now. Should I put an enter mark after every zip code first before running the code? Is there an easy way to find each zipcode in the single column of text and replace it with a single paragraph mark? Is there in other words a format that I could use to find the end of 5 numbers in a row and place a paragraph mark at the end of each that it finds?
    NMP <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

    If you can't convince them, confuse them. - Harry Truman <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Word to Excel - Labels convert (2003)

    The code that I posted will convert each address label into a single line, with tab characters between the parts of the address. So if you open the text file in Excel, each part of the address will be in a separate cell.
    But you will find that some addresses are spread over 4 columns and others over 5 columns, because some of the original labels had 4 lines and others had 5 lines.

  4. #4
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Jacksonville,NC, USA
    Posts
    705
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Word to Excel - Labels convert (2003)

    Thank you Hans. I did not have any luck with the code. It didn't perform any action at all. I went ahead and spent the time converting the labels to .rtf, so they'd be in one solid line, replacing all end line paragraph marks with a single tab character...then placing in a table (Word), needless to say it took all day to shift the addresses to the correct lines (highlight, drag) as they were all out of whack (would they be any other way?) <img src=/S/hairout.gif border=0 alt=hairout width=31 height=23> ...then copied and pasted to a spread sheet...then imported into access. I reckon the lady I was fixing it for can proof it and ensure everything is in the right order...if it's not, it will only be one or two occurances here or there where some had more than one address and she will need to fix that however she wants.
    NMP <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

    If you can't convince them, confuse them. - Harry Truman <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Word to Excel - Labels convert (2003)

    The code that I posted creates a plain text file. Perhaps you didn't notice the text file...

  6. #6
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Jacksonville,NC, USA
    Posts
    705
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Word to Excel - Labels convert (2003)

    Hans,
    Your absolutely right. I did not notice it. I looked inside the My Documents folder, and wa-la...there it was. I did not think to check the default save folder (MyDocs) for a text file. I was assuming it would auto-open, and when nothing happened I assumed...nothing happened. Your code would have saved me hours...alas...I cannot cry now...whats done is done. But at least next time I will know, and hopefully this post may help someone else...(keep them from not noticing the perfectly good text file) and not working all afternoon on something they missed.
    Always grateful
    NMP <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

    If you can't convince them, confuse them. - Harry Truman <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

  7. #7
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Jacksonville,NC, USA
    Posts
    705
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Word to Excel - Labels convert (2003)

    I have acquired a very large sheet of address labels. I am not sure how the person created it to begin with, but they evidently did not use field names, and if so, they are no omitted in this label document. I am attaching a single sheet of 68 pages of labels so that you can see the problem.
    What I want to do is take the 68 pages of labels and convert them to an Excel spreadsheet with appropriate column names...then import that spreadsheet into an Access database (unless it can be taken directly into a new table in an Access database without first putting it into excel).
    Can you give me a clue how I can do this without having to take each individual label and do this from scratch...copy paste...etc. which would take all day.
    NMP <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

    If you can't convince them, confuse them. - Harry Truman <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

Posting Permissions

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