locked
transfer data in word VBA RRS feed

  • Question

  • Hi,

    I am wondering if I could get some help with word VBA. I need to generate 20 different electronic components report documents of the same template document. The template has a front page, Table of Contents and a fixed number of Chapters and their heading title (Chapter 1 Introduction, Chapter 2 Components and so on).

    I thought the quicker way of doing this is to write VBA codes that would copy from the source document and dump the texts into the right position (e.g. to the right chapters) in the template for each of the electronic components.

    I could use docvariables in the template for each of the chapters so that when the texts is copied, it gets dumped into the chapter it belongs to by identifying the name of the docvariable.

    But I do not know how to read the texts from the source document in VBA.

    The source of the document for each of the components looks like the following

    [TITLE]Capacitor

    Capacitor is an electronic component................

    ....................................................................

    +   <---(indicating the end of the paragraph - this belongs to Chapter 1)

    Capacitors deviate from ................................

    ..............................................................

    +   <---(indicating the end of the paragraph - this belongs to Chapter 2)

    and so on

     

    I wonder if anyone who might have done this before could share some of your experience with me.

     

    Thank you in advance

     

     

    Friday, October 8, 2010 1:17 AM

Answers

  • Send one of the source documents to me at dkr[atsymbol]mvps[dot]org so that I can see how it is actually set up.

    -- Hope this helps.

    Doug Robbins - Word MVP,
    dkr[atsymbol]mvps[dot]org
    Posted via the Community Bridge

    "associates" wrote in message news:439cdb9b-8bb9-42ce-8519-4141f8dac468@communitybridge.codeplex.com...

    Thank you Doug for your reply.

    That's a great code. I could use to find and replace within a document in the future.

    However, sorry Doug if I have not explained my problem clearly enough. I guess what I am trying to do is to be able to go through the source document until it reaches the end of the document. While it is going in the loop, it copies and dump the texts from the source to the target document.

    I got the following code:

    Do Until srange.Paragraphs(1).range = "END"
    
    For i = 1 To numchapters
     srange.Start = srange.Start + Len(srange.Paragraphs(1).range)
     srange.End = srange.Start + InStr(srange, "+")
     target.Variables("Chapter" & i).Value = srange
     srange.Collapse wdCollapse.End
     srange.End = source.Range.End
    Next i
    target.Range.Fields.Update
    
    ' do other things
    
    Loop

    but this continues to loop. I got a feeling that it may be that the source is not an active document when it comes back to the do loop (I have checked in the sourcedoc document that i put "END" at the end of the document). I wonder if there is a way of making the source the active document just before it gets back to the do loop.

    Thank you so much in advance


    Doug Robbins - Word MVP dkr[atsymbol]mvps[dot]org
    • Marked as answer by Jennifer Zhan Friday, October 15, 2010 6:07 AM
    Sunday, October 10, 2010 2:31 AM

All replies

  • You could use something like the following (it may need a bit of tweaking to get srange to include just what you want in each chapter

    Dim source As Document, target As Document
    Dim i As Long, numchapters As Long
    numchapters = [insert the fixed number of chapters]
    Dim srange As Range
    Set target = ActiveDocument
    Set source = Documents.Open([insert the path\filename of the document containing the information])
    Set srange = source.Range
    For i = 1 To numchapters
        srange.Start = srange.Start + Len(srange.Paragraphs(1))
        srange.End = srange.Start + InStr(srange, "+")
        target.Variables("Chapter" & i).Value = srange
        srange.Collapse wdCollapse.End
        srange.End = source.Range.End
    Next i
    target.Range.Fields.Update


    -- Hope this helps.

    Doug Robbins - Word MVP,
    dkr[atsymbol]mvps[dot]org
    Posted via the Community Bridge

    "associates" wrote in message news:1bbc7b92-4024-4295-947e-5fb634b1a82c@communitybridge.codeplex.com...

    Hi,

    I am wondering if I could get some help with word VBA. I need to generate 20 different electronic components report documents of the same template document. The template has a front page, Table of Contents and a fixed number of Chapters and their heading title (Chapter 1 Introduction, Chapter 2 Components and so on).

    I thought the quicker way of doing this is to write VBA codes that would copy from the source document and dump the texts into the right position (e.g. to the right chapters) in the template for each of the electronic components.

    I could use docvariables in the template for each of the chapters so that when the texts is copied, it gets dumped into the chapter it belongs to by identifying the name of the docvariable.

    But I do not know how to read the texts from the source document in VBA.

    The source of the document for each of the components looks like the following

    [TITLE]Capacitor

    Capacitor is an electronic component................

    ...................................................................

    +   <---(indicating the end of the paragraph - this belongs to Chapter 1)

    Capacitors deviate from ................................

    .............................................................

    +   <---(indicating the end of the paragraph - this belongs to Chapter 2)

    and so on

    I wonder if anyone who might have done this before could share some of your experience with me.

    Thank you in advance


    Doug Robbins - Word MVP dkr[atsymbol]mvps[dot]org
    Friday, October 8, 2010 2:35 AM
  • Thank you Doug for your reply.

    I tried to understand how your code works but am confused on the code line where srange.Start = srange.Start + Len(srange.Paragraphs(1)). Then, I tried to run the code but come across an error that points to Len() with the message "variable required - can't assign to this expression".

    I think I have not yet specified the value of srange.Start, have not I?

    Another thing is with the code line: target.Variables("Chapter" & i).Value = srange, does the variables here refers to the docvariable that i have already set in the template?

    Thank you so much in advance

     

     

     

    Friday, October 8, 2010 4:41 AM
  • I had not actually tried running that code, but looking at it now, the command that is causing the error should be

        srange.Start = srange.Start + Len(srange.Paragraphs(1).Range)

    By setting

    Set srange = source.Range

    the .Start of the range srange is set to the .Start of the source.Range and the .End of the range srange is set to the .End of the source.Range


    -- Hope this helps.

    Doug Robbins - Word MVP,
    dkr[atsymbol]mvps[dot]org
    Posted via the Community Bridge

    "associates" wrote in message news:757449d2-73f5-45a1-9c3b-c60e59f5a51d@communitybridge.codeplex.com...

    Thank you Doug for your reply.

    I tried to understand how your code works but am confused on the code line where srange.Start = srange.Start + Len(srange.Paragraphs(1)). Then, I tried to run the code but come across an error that points to Len() with the message "variable required - can't assign to this expression".

    I think I have not yet specified the value of srange.Start, have not I?

    Another thing is with the code line: target.Variables("Chapter" & i).Value = srange, does the variables here refers to the docvariable that i have already set in the template?

    Thank you so much in advance


    Doug Robbins - Word MVP dkr[atsymbol]mvps[dot]org
    Friday, October 8, 2010 5:33 AM
  • Thanks a lot Doug. It works now.
    Friday, October 8, 2010 6:24 AM
  • Hi Doug,

    Sorry to bother you again.

    I realise that the source of the document has more data or texts in it as follows

    [TITLE]Capacitor

    Capacitor is an electronic component................

    ...................................................................

    +   <---(indicating the end of the paragraph - this belongs to Chapter 1)

    Capacitors deviate from ................................

    .............................................................

    +   <---(indicating the end of the paragraph - this belongs to Chapter 2)

    ------------------------------------------------------

    +

    [TITLE]Diode

    Diode is another electronic component....................

    ..................................................................

    +

    Diode...............................................................

    +

    ...........................................................

    +

    [TITLE]Inductor

    ................................................................

    +

    ..................................................................

    +

    .................................................................

    and so on

     

    I think I need some kind of EOF that would keep looping until it finds the end of the file (in a normal text file) but in word, I think it would be end of paragraph. I am not sure. I wonder how to check if this is the end of the document in VBA.

    Thank you in advance

     

     

     

    Friday, October 8, 2010 1:38 PM
  • In Word, you would use a

    With Selection.Find
        Do While .Execute([criteria]) = True
            'Commands to do whatever it is
        Loop
    End With

    construction, such as:


    Dim myrange As Range
    Dim prevline As Range
    Dim Findstr As String
    Dim Replacementstr As String
    Dim Replace
    Findstr = InputBox("Insert the text that you want to find.")
    Replacementstr = InputBox("Insert the replacement text.")
    Selection.HomeKey wdStory
    Selection.Find.ClearFormatting
    With Selection.Find
        Do While .Execute(FindText:=Findstr, Forward:=True, _
            MatchWildcards:=False, Wrap:=wdFindStop, MatchCase:=False) = True
            Set myrange = Selection.Range
            Set prevline = Selection.Range.Duplicate
            prevline.MoveStart Unit:=wdSentence, Count:=-1
            prevline.Select
            ActiveWindow.ScrollIntoView Selection.Range, True
            Replace = MsgBox("Replace the text?", vbYesNoCancel + vbQuestion)
            If Replace = vbYes Then
                myrange.Text = Replacementstr
            ElseIf Replace = vbNo Then
                myrange.Text = myrange.Text
            Else
                Exit Sub
            End If
            Selection.Collapse wdCollapseEnd
            Selection.MoveRight wdCharacter, 1
        Loop
    End With


    -- Hope this helps.

    Doug Robbins - Word MVP,
    dkr[atsymbol]mvps[dot]org
    Posted via the Community Bridge

    "associates" wrote in message news:d3bccb04-3d6d-40c6-8816-0a678a1d31d5@communitybridge.codeplex.com...

    Hi Doug,

    Sorry to bother you again.

    I realise that the source of the document has more data or texts in it as follows

    [TITLE]Capacitor

    Capacitor is an electronic component................

    ..................................................................

    +   <---(indicating the end of the paragraph - this belongs to Chapter 1)

    Capacitors deviate from ................................

    ............................................................

    +   <---(indicating the end of the paragraph - this belongs to Chapter 2)

    ------------------------------------------------------

    +

    [TITLE]Diode

    Diode is another electronic component....................

    .................................................................

    +

    Diode...............................................................

    +

    ..........................................................

    +

    [TITLE]Inductor

    ...............................................................

    +

    .................................................................

    +

    ................................................................

    and so on

    I think I need some kind of EOF that would keep looping until it finds the end of the file (in a normal text file) but in word, I think it would be end of paragraph. I am not sure. I wonder how to check if this is the end of the document in VBA.

    Thank you in advance


    Doug Robbins - Word MVP dkr[atsymbol]mvps[dot]org
    Friday, October 8, 2010 9:38 PM
  • Thank you Doug for your reply.

    That's a great code. I could use to find and replace within a document in the future.

    However, sorry Doug if I have not explained my problem clearly enough. I guess what I am trying to do is to be able to go through the source document until it reaches the end of the document. While it is going in the loop, it copies and dump the texts from the source to the target document.

    I got the following code:

     

    Do Until srange.Paragraphs(1).range = "END"
    
    For i = 1 To numchapters
      srange.Start = srange.Start + Len(srange.Paragraphs(1).range)
      srange.End = srange.Start + InStr(srange, "+")
      target.Variables("Chapter" & i).Value = srange
      srange.Collapse wdCollapse.End
      srange.End = source.Range.End
    Next i
    target.Range.Fields.Update
    
    ' do other things
    
    Loop

    but this continues to loop. I got a feeling that it may be that the source is not an active document when it comes back to the do loop (I have checked in the sourcedoc document that i put "END" at the end of the document). I wonder if there is a way of making the source the active document just before it gets back to the do loop.

    Thank you so much in advance

     

     

    Sunday, October 10, 2010 2:08 AM
  • Send one of the source documents to me at dkr[atsymbol]mvps[dot]org so that I can see how it is actually set up.

    -- Hope this helps.

    Doug Robbins - Word MVP,
    dkr[atsymbol]mvps[dot]org
    Posted via the Community Bridge

    "associates" wrote in message news:439cdb9b-8bb9-42ce-8519-4141f8dac468@communitybridge.codeplex.com...

    Thank you Doug for your reply.

    That's a great code. I could use to find and replace within a document in the future.

    However, sorry Doug if I have not explained my problem clearly enough. I guess what I am trying to do is to be able to go through the source document until it reaches the end of the document. While it is going in the loop, it copies and dump the texts from the source to the target document.

    I got the following code:

    Do Until srange.Paragraphs(1).range = "END"
    
    For i = 1 To numchapters
     srange.Start = srange.Start + Len(srange.Paragraphs(1).range)
     srange.End = srange.Start + InStr(srange, "+")
     target.Variables("Chapter" & i).Value = srange
     srange.Collapse wdCollapse.End
     srange.End = source.Range.End
    Next i
    target.Range.Fields.Update
    
    ' do other things
    
    Loop

    but this continues to loop. I got a feeling that it may be that the source is not an active document when it comes back to the do loop (I have checked in the sourcedoc document that i put "END" at the end of the document). I wonder if there is a way of making the source the active document just before it gets back to the do loop.

    Thank you so much in advance


    Doug Robbins - Word MVP dkr[atsymbol]mvps[dot]org
    • Marked as answer by Jennifer Zhan Friday, October 15, 2010 6:07 AM
    Sunday, October 10, 2010 2:31 AM