Copying Specific data from a source document to a target document - Learning project

[mellowest]

Hello,

Let me start by saying that I do not know VBA, but want to learn. So, I gave myself what I considered to be a difficult project (for my level) in Word, and I was not wrong, because I have no idea how to do it.

The ideal macro would perform the following actions:

1. Run macro in target document.
2. Macro reads a doc file that contains the data. The location and name of the source file do not change.
2.1 there could be multiple instances of the data.
3. Macro loops through the source document copying necessary data.
4. Macro pastes the data in a specific position within the target document.

The source document has the following data (mock data). There could be several more than two records.

Cars registered to individual:
Vehicle:
Description: Black 2004 Toyota Camry - 4 Dr Wagon Sport Utility
VIN: PN8AZ78T84W256899
ST: TEXAS

Vehicle:
Description: Blue 2011 Toyota Camry - 4 Dr Wagon Sport Utility
VIN: PN8AZ78T84W256810
ST: TEXAS


Othercraft

The destination document would then show the following:

Cars:
2004 Toyota Camry - PN8AZ78T84W256899
2011 Toyota Camry - PN8AZ78T84W256810

I have been reading posts, but haven't been able to find something that I could use specifically. Any assistance would be appreciated. Thank you.

[gmayor]

You have picked a difficult place to start, but it will be a good basis to learn about ranges. You have three paragraphs of interest the format of which is repeated

Vehicle:
Description: Black 2004 Toyota Camry - 4 Dr Wagon Sport Utility
VIN: PN8AZ78T84W256899

You need to find the first common paragraph and then process the contents of the following two paragraphs and repeat. Maybe something like the following. However this will only work if the same format is repeated and each line is terminated with a paragraph break i.e. ¶


Option Explicit

Sub Macro1()
Dim oSource As Document
Dim oTarget As Document
Dim oRng As Range, oFound As Range
Dim lngCount As Long: lngCount = 0
Dim strText As String
If Documents.Count = 0 Then Documents.Add 'ensure there is a document open
Set oTarget = ActiveDocument
Set oSource = Documents.Open("C:\Path\Filename.docx") 'open the source document by name
Set oRng = oSource.Range 'set a range to the source document
With oRng.Find 'look in the range
Do While .Execute(FindText:="Vehicle:") 'for the text 'Vehicle:'
lngCount = lngCount + 1 ' start a counter
Set oFound = oRng.Paragraphs(1).Range.Next.Paragraphs(1).Range 'set a range to the next paragraph
oFound.MoveStartUntil "12" ' move the start of the range to the start of the date
oFound.MoveEndUntil "-", wdBackward 'move the end of the range back to the hyphen
oFound.End = oFound.End + 1 'add the space after the hyphen to the range
strText = oFound.Text ' add the range text to a string
'Then move on to the next paragraph
Set oFound = oRng.Paragraphs(1).Range.Next.Paragraphs(1).Range.Next.Paragraphs(1).Range
oFound.MoveStartUntil Chr(32) 'move the start to the first space
oFound.Start = oFound.Start + 1 'move the start after that space
oFound.End = oFound.End - 1 'remove the paragraph break from the range
strText = strText & oFound.Text ' add the range to the string
'if it is not the first item add a paragraph break to the end of the target document
If lngCount > 1 Then oTarget.Range.InsertParagraphAfter
oTarget.Range.InsertAfter strText 'insert the text string at the end of the target document
oRng.Collapse 0 'collapse the original range to its end
Loop 'and look for the next instance
End With
oSource.Close 0 'close the source document without saving it
lbl_Exit:
Set oSource = Nothing
Set oTarget = Nothing
Set oRng = Nothing
Set oFound = Nothing
Exit Sub
End Sub

[mellowest]

Hi Graham,

Thanks for putting this together. Unfortunately, when I run it, it doesn't return anything from the sample document, which matches the data I included above. When I run it in the actual full document, it returns incorrect data. I had a couple of questions that I think will help me better understand.

oFound.MoveStartUntil "12" --What does the 12 mean? And is Cset optional?

Set oFound = oRng.Paragraphs(1).Range.Next.Paragraphs(1).Range -- I think this looks at the paragraph and finds Watercraft. It seems to find it fine in the sample data, but in the actual report, it seems to make an error:
21700
But even when it finds it in the sample data, nothing is returned to strText = oFound.Text.

I was wondering if Watercraft could be hard coded as the range end? This text will not change.

Thank you so much for your help.

(I added the Cset to MoveEndUntil as you see in the pic, but I have ran it with and without to same result)

[gmayor]

oFound.MoveStartUntil "12" means move the start of the range

Description: Blue 2011 Toyota Camry - 4 Dr Wagon Sport Utility

to the first instance of either the number 1 or the number 2 ie as shown below

Description: Blue 2011 Toyota Camry - 4 Dr Wagon Sport Utility

The following line - oFound.MoveEndUntil "-", wdBackward
moves the end of the Range back to the hyphen

thus the new range is

Description: Blue 2011 Toyota Camry - 4 Dr Wagon Sport Utility

Neither the code nor your example mentions 'Watercraft. Without knowing how the watercraft serction of your document is formatted, it is impossible to guess how to extract the data from it nor is it know where you want to put it in relation to the other vehicles in the target document. You could however add another loop immediately before oSource.Close 0


Set oRng = oSource.Range 'Reset a range to the source document
lngCount = 0
With oRng.Find 'look in the range
Do While .Execute(FindText:="Watercraft:") 'for the text 'Watercraft:'

'Do stuff

oRng.Collapse 0 'collapse the original range to its end
Loop 'and look for the next instance
End With

[mellowest]

Hello Graham,

My apologies. Othercraft was the word I meant, rather than watercraft. My bigger apologies for not realizing why the code was failing sooner, EVEN AFTER YOU CLEARLY stated that it would fail unless each line is "terminated with a paragraph break". I honestly did not think about it until 3AM this morning, when I bolted out of bed. I changed all the soft returns to paragraph breaks and the code works perfectly. Thank you!

Additionally, I think I understand what it is doing on a very basic level. Thanks again.