transfer data in word VBA

All replies

• 

  

  0

  Sign in to vote

  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
• 

  

  0

  Sign in to vote

  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
• 

  

  0

  Sign in to vote

  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
• 

  

  0

  Sign in to vote

  Thanks a lot Doug. It works now.

  Friday, October 8, 2010 6:24 AM
• 

  

  0

  Sign in to vote

  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
• 

  

  0

  Sign in to vote

  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
• 

  

  0

  Sign in to vote

  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
• 

  

  0

  Sign in to vote

  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