Results 1 to 9 of 9
  1. #1
    New Lounger
    Join Date
    Oct 2016
    Posts
    8
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Word document formatting

    Hi there,
    Please see attached txt file and look for following certain elements after header info.
    You will notice that for each cancelled invoice or invoice line data, there is some header data like customer name, order number, order date, customer number, salesperson and currency. This data is followed by details from that specific order.

    I want to move all this header info to the detailed tabulated info for each record using macro. In other words I would like to first move headings (which are customer name, order number, order date, customer number, salesperson and currency) for each record next to Category column and their respective data in the below lines for each Line number. You will see Line number on extreme left being the first column of tabulated data.

    Please advise if this could be handled using VBA. I have few years of experience using VBA in Excel but I am new to Word VBA and therefore your help would be much appreciated.

    Regards,
    SMI
    Attached Files Attached Files

  2. #2
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,853
    Thanks
    4
    Thanked 259 Times in 239 Posts
    Yes it can be done with VBA in Word but it would probably be easier in Excel if that is where your knowledge lies.

    The code would need to be able to deal with inconsistent data such as those records where there is no Salesperson or Currency from the header and those lines in the tables with a field that wraps to more than one line. Those inconsistencies make the task considerably more complex.

    Are you sure there is no way to customise the data source so that it suits what you require? The text file appears to be an export from a database and it would be far easier to arrange an export in the format you require.
    Andrew Lockton, Chrysalis Design, Melbourne Australia

  3. The Following User Says Thank You to Andrew Lockton For This Useful Post:

    SMI (2016-10-21)

  4. #3
    New Lounger
    Join Date
    Oct 2016
    Posts
    8
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Andrew Lockton View Post
    Yes it can be done with VBA in Word but it would probably be easier in Excel if that is where your knowledge lies.

    The code would need to be able to deal with inconsistent data such as those records where there is no Salesperson or Currency from the header and those lines in the tables with a field that wraps to more than one line. Those inconsistencies make the task considerably more complex.

    Are you sure there is no way to customise the data source so that it suits what you require? The text file appears to be an export from a database and it would be far easier to arrange an export in the format you require.
    Andrew, thanks for your response. Note that just a few days back I saw a similar post from someone in this forum and he was also advised by an expert to handle it first in Word VBA to generate a text file which could easily be imported in Excel (using fixed width feature).

    In fact I got inspiration from that post. As far as customizing data in source database is concerned, it is not possible for me. That's the reason I am trying to handle it in VBA.

    So if I request you to forget about inconsistencies in header data and guide me with a solution meant to pull those headings and their respective values and re-position them to the data table just below those header areas, it will be of great help.

    Thank you once again.

    Kind regards
    SMI

  5. #4
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,853
    Thanks
    4
    Thanked 259 Times in 239 Posts
    Try this code
    Code:
    Sub ProcessText()
      Dim sHead As String, rng As Range
      With ActiveDocument.Content.Find
        .ClearFormatting
        .Replacement.ClearFormatting
        .Text = " {2,}"
        .Replacement.Text = "^t"
        .Forward = True
        .Wrap = wdFindStop
        .Format = False
        .MatchCase = False
        .MatchWholeWord = False
        .MatchAllWordForms = False
        .MatchSoundsLike = False
        .MatchWildcards = True
        .Execute Replace:=wdReplaceAll
        
        .Text = "^p^t"
        .Replacement.Text = "^p"
        .MatchWildcards = False
        .Execute Replace:=wdReplaceAll
        
        .Text = "^b" 'remove section breaks
        .Replacement.Text = "^p"
        .Execute Replace:=wdReplaceAll
        .Text = "^m" 'remove hard page breaks
        .Replacement.Text = "^p"
        .Execute Replace:=wdReplaceAll
        
        .Text = "^p^p^p^p^p" 'remove empty paragraphs
        .Replacement.Text = "^p"
        .Execute Replace:=wdReplaceAll
        .Text = "^p^p^p"
        .Execute Replace:=wdReplaceAll
        .Text = "^p^p"
        .Execute Replace:=wdReplaceAll
        
        'bring header information in correct format
        .Text = "Customer Number: "
        .Replacement.Text = ""
        .MatchWildcards = False
        .Execute Replace:=wdReplaceAll
        
        .Text = "^pOrder Number: "
        .Replacement.Text = "^t"
        .Execute Replace:=wdReplaceAll
        
        .Text = "Salesperson: "
        .Replacement.Text = ""
        .Execute Replace:=wdReplaceAll
        
        .Text = "^pOrder Date: "
        .Replacement.Text = "^t"
        .Execute Replace:=wdReplaceAll
        
        .Text = "Currency: "
        .Replacement.Text = ""
        .Execute Replace:=wdReplaceAll
        
        'copy header info to each line
        .Text = "Customer Name: "
        Do While .Execute()
          With .Parent
            '.Select
            sHead = vbTab & Mid(.Paragraphs(1).Range.Text, 15)
            sHead = Replace(sHead, vbCr, "")
            .Move Unit:=wdParagraph, Count:=4
            Set rng = .Paragraphs(1).Range
            Do While rng.Characters(1) <> "-"
              rng.MoveEnd Unit:=wdCharacter, Count:=-1
              rng.InsertAfter sHead
              .Move Unit:=wdParagraph, Count:=1
              Set rng = .Paragraphs(1).Range
            Loop
            .Move Unit:=wdParagraph, Count:=1
          End With
        Loop
      End With
    End Sub
    Andrew Lockton, Chrysalis Design, Melbourne Australia

  6. The Following User Says Thank You to Andrew Lockton For This Useful Post:

    SMI (2016-10-21)

  7. #5
    New Lounger
    Join Date
    Oct 2016
    Posts
    8
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Hi Andrew, Thank you very much for helping me out. It seems it has solved most part of it. I have given it a try and as I said it is working fine. You will appreciate the fact that I am interested to learn the logic behind this code to improve my understanding about VBA code also and therefore I have reviewed your awesome code line by line. I must say I have understood the upper part of it before Do.... While loop. Allow me to request you to add comments in the Do.. While loop section in front of statements so that I could understand what each statement is doing. I am sure this will further improve my understanding about Word VBA code.

    Let me reiterate, only the last section that begins with Do.. While .Execute () and ends at Loop statement.

    Thanks a million Andrew.

    Regards,
    SMI

  8. #6
    New Lounger
    Join Date
    Oct 2016
    Posts
    8
    Thanks
    3
    Thanked 0 Times in 0 Posts
    I thought of putting my understanding of some part of this Do... While .Execute() loop and put my questions against each line where I need your help to understand it better. That way if I have any misunderstanding, you will be able to correct me as well.

    Please excuse me in advance if you find any stupid thing in my comments.

    Do While .Execute() ' The Execute method referring to core With..End With block
    With .Parent ' Note sure about .Parent
    '.Select ' Selecting text but not sure which text is being targeted here

    sHead = vbTab & Mid(.Paragraphs(1).Range.Text, 15) ' I know about Mid function but in this context, need some hints from you

    sHead = Replace(sHead, vbCr, "") ' Whatever is stored in sHead variable is being replaced by itself + vbCr. Note sure why we are using blank quotes at end

    .Move Unit:=wdParagraph, Count:=4 ' Moving cursor downwards 4 paragraphs I guess????

    Set rng = .Paragraphs(1).Range ' Defining a range but not sure what that 1 refers to in brackets next to word paragraph. Is it the first paragraph?

    Do While rng.Characters(1) <> "-" ' I believe code is looking for the first occurence of "-" character here.

    rng.MoveEnd Unit:=wdCharacter, Count:=-1 ' I guess it is looking for the last Word Character

    rng.InsertAfter sHead ' It is inserting the sHead variable value here, but again not confident where it is going to insert.

    .Move Unit:=wdParagraph, Count:=1 ' Just like above, moving cursor one paragraph down

    Set rng = .Paragraphs(1).Range ' Re setting the range object so that loop searches the values again

    Loop ' End loop of inner Do While Loop

    .Move Unit:=wdParagraph, Count:=1 ' Just like above, moving cursor one paragraph down

    End With ' End with for With .Parent block
    Loop ' End loop of main Loop

  9. #7
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,853
    Thanks
    4
    Thanked 259 Times in 239 Posts
    Do While .Execute() ' Do loop while the search has found something
    With .Parent ' With the text that was found
    '.Select ' Selecting text to show where we are in the document (this line was disabled once the rest of code worked)
    sHead = vbTab & Mid(.Paragraphs(1).Range.Text, 15) ' populate a string variable with a tab and the contents of the found paragraph except the first 15 characters
    sHead = Replace(sHead, vbCr, "") ' get rid of the carriage return in the string (which was in the end of the paragraph)
    .Move Unit:=wdParagraph, Count:=4 ' Move down 4 paragraphs from the found paragraph
    Set rng = .Paragraphs(1).Range ' Defining a range but not sure what that 1 refers to in brackets next to word paragraph. Is it the first paragraph? YES
    Do While rng.Characters(1) <> "-" 'Do until we hit a paragraph that begins with a dash
    rng.MoveEnd Unit:=wdCharacter, Count:=-1 ' The paragraph range includes the paragraph mark. We want to insert the sHead text in front of the paragraph mark so we need to make sure the rng doesn't include the paragraph mark
    rng.InsertAfter sHead ' It is inserting the sHead variable value here, but again not confident where it is going to insert.
    .Move Unit:=wdParagraph, Count:=1 ' Just like above, moving cursor one paragraph down
    Set rng = .Paragraphs(1).Range ' Re setting the range object so that loop searches the values again
    Loop ' End loop of inner Do While Loop Step to the next paragraph inside the current report
    .Move Unit:=wdParagraph, Count:=1 ' Just like above, moving cursor one paragraph down before we search for the next report
    End With ' End with for With .Parent block
    Loop ' End loop of main Loop Now find the next report
    Andrew Lockton, Chrysalis Design, Melbourne Australia

  10. The Following User Says Thank You to Andrew Lockton For This Useful Post:

    SMI (2016-10-21)

  11. #8
    New Lounger
    Join Date
    Oct 2016
    Posts
    8
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Andrew Lockton View Post
    Do While .Execute() ' Do loop while the search has found something
    With .Parent ' With the text that was found
    '.Select ' Selecting text to show where we are in the document (this line was disabled once the rest of code worked)
    sHead = vbTab & Mid(.Paragraphs(1).Range.Text, 15) ' populate a string variable with a tab and the contents of the found paragraph except the first 15 characters
    sHead = Replace(sHead, vbCr, "") ' get rid of the carriage return in the string (which was in the end of the paragraph)
    .Move Unit:=wdParagraph, Count:=4 ' Move down 4 paragraphs from the found paragraph
    Set rng = .Paragraphs(1).Range ' Defining a range but not sure what that 1 refers to in brackets next to word paragraph. Is it the first paragraph? YES
    Do While rng.Characters(1) <> "-" 'Do until we hit a paragraph that begins with a dash
    rng.MoveEnd Unit:=wdCharacter, Count:=-1 ' The paragraph range includes the paragraph mark. We want to insert the sHead text in front of the paragraph mark so we need to make sure the rng doesn't include the paragraph mark
    rng.InsertAfter sHead ' It is inserting the sHead variable value here, but again not confident where it is going to insert.
    .Move Unit:=wdParagraph, Count:=1 ' Just like above, moving cursor one paragraph down
    Set rng = .Paragraphs(1).Range ' Re setting the range object so that loop searches the values again
    Loop ' End loop of inner Do While Loop Step to the next paragraph inside the current report
    .Move Unit:=wdParagraph, Count:=1 ' Just like above, moving cursor one paragraph down before we search for the next report
    End With ' End with for With .Parent block
    Loop ' End loop of main Loop Now find the next report
    Thanks for your help Andrew. I really appreciate this.

  12. #9
    New Lounger
    Join Date
    Oct 2016
    Posts
    8
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Hi Andrew,

    With your kind assistance, I have been able to remove some more repetitive and unwanted text from the raw text file by adding below 2 lines in the With... End With block and both these lines are working perfectly. Of course each of thee two lines are followed by replacement text commands.


    .Text = "(PK_SOB)*(Order Dates)*([0-9]{2}[-][A-Z]{3}[-][0-9]{2})^13"

    .Text = "(Cancelled Orders Reason)*(Order Dates)*([0-9]{2}[-][A-Z]{3}[-][0-9]{2})^13"

    Thank you once again. You have made it possible to yield this much of success from a relatively much complicated task.

    Kind regards
    SMI

Tags for this Thread

Posting Permissions

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