Find text in doc, and paste chart where text is located

[jason]

I have the basics but I am obviously missing something.

find text:

myRange.Find.Execute FindText:="%PLOT_1"

paste plot:
Set WDApp = GetObject(, "Word.Application") ' Reference active
document
Set WDDoc = WDApp.ActiveDocument ' Copy chart as a picture
ActiveChart.CopyPicture Appearance:=xlScreen, Size:=xlScreen,
_
Format:=xlPicture ' Paste chart at cursor position
WDApp.Selection.PasteSpecial Link:=False,
DataType:=wdPasteMetafilePicture, _
Placement:=wdInLine, DisplayAsIcon:=False ' Clean up
Set WDDoc = Nothing
Set WDApp = Nothing


basically: i need a way to set Placement:= location(%PLOT_1)

but i am unsure how to do this.
any help would be great.
thanks!

 

[Lene Fredborg]

The text you found is at myRange but your code inserts the chart at the
selection. Use myRange when you paste. To do so, replace:

WDApp.Selection.PasteSpecial Link:=False…

with

WDApp.myRange.PasteSpecial Link:=False

(i.e. insert â€myRange†instead of â€Selectionâ€).

However, this will replace the found "%PLOT_1" with the pasted chart. If you
want to insert the chart after the found text, first collapse the range as
follows:

myRange.Collapse wdCollapseEnd

and then paste the chart.


--
Regards
Lene Fredborg - Microsoft MVP (Word)
DocTools - Denmark
www.thedoctools.com
Document automation - add-ins, macros and templates for Microsoft Word

 

[jason]

The text you found is at myRange but your code inserts the chart at the
selection. Use myRange when you paste. To do so, replace:

WDApp.Selection.PasteSpecial Link:=False…

with

WDApp.myRange.PasteSpecial Link:=False

(i.e. insert ”myRange” instead of ”Selection”).

However, this will replace the found "%PLOT_1" with the pasted chart. If you
want to insert the chart after the found text, first collapse the range as
follows:

myRange.Collapse wdCollapseEnd

and then paste the chart.

--
Regards
Lene Fredborg - Microsoft MVP (Word)
DocTools - Denmarkwww.thedoctools.com
Document automation - add-ins, macros and templates for Microsoft Word










- Show quoted text -

Lene:
I am getting the following error:

compile error:
method or data member not found



Set myRange = ActiveDocument.Content
myRange.Find.Execute FindText:="%PLOT_1"
myRange.Collapse wdCollapseEnd


' Reference existing instance of Word
Set WDApp = GetObject(, "Word.Application") ' Reference active
document
Set WDDoc = WDApp.ActiveDocument ' Copy chart as a picture
ActiveChart.CopyPicture Appearance:=xlScreen, Size:=xlScreen,
_
Format:=xlPicture ' Paste chart at cursor position
WDApp.myRange.PasteSpecial Link:=False,
DataType:=wdPasteMetafilePicture, _ 'error on this line
Placement:=wdInLine, DisplayAsIcon:=False ' Clean up
Set WDDoc = Nothing
Set WDApp = Nothing

 

[Lene Fredborg]

To me, the code you posted first looked as an extract of something larger
(e.g. no declarations of variables were included). Therefore, I only tried to
provide help on your specific question about how to paste at the location
where the found text is.

I am afraid I do not fully understand your code. The first part seems as
written for Word. The last part seems as written for Excel.

--
Regards
Lene Fredborg - Microsoft MVP (Word)
DocTools - Denmark
www.thedoctools.com
Document automation - add-ins, macros and templates for Microsoft Word

 

[jason]

Lene,
It is a code based in Word that makes use of references to excel.

basically:

1 - Main instance of operation is word (find and replace text,
automate for general case)
2 - Find certain text in word doc and replace with situation specific
text
2a - Find "%PLOT_1" and replace this with plot created in excel
(present on clipboard at time of operation).

so basically:

myRange.Find.Execute FindText:="%PLOT_1"

replace %PLOT_1 with plot.
i tried your method by replacing "selection" with "myrange" and it
gave me an error.

if you e-mail me I can send you a fuller snippet of the code.
thank you

 

[Lene Fredborg]

OK. I try to understand what you are trying to do. Back to the line of code
where you told the error occurs:

If I create a chart in Excel, copy it and then try to run the code in a Word
document (where I have first inserted â€% PLOT_1â€) the code fails here too.
The problem seems to be that you have used:
DataType:=wdPasteMetafilePicture

That data type that is not available for a chart as far as I can see. Try to
replace it with:
DataType:=wdPasteEnhancedMetafile

It works here.


An additional comment to your code:
As I understand you, you have (manually) copied the chart before running the
code (you said: (present on clipboard at time of operation)). If that is
correct, you could remove most of the code so that only the following is left:

Set myRange = ActiveDocument.Content
myRange.Find.Execute FindText:="%PLOT_1"
'Leave out the following line if the found text must be replaced by the chart
myRange.Collapse wdCollapseEnd

'Paste the already copied chart at myRange
myRange.PasteSpecial Link:=False, _
DataType:=wdPasteEnhancedMetafile, _
Placement:=wdInLine, DisplayAsIcon:=False


Since you say all the code is run from within Word, I see no reason for
including the following:

In the lines below you set references to Word and your Word document and not
to Excel:
Set WDApp = GetObject(, "Word.Application")
Set WDDoc = WDApp.ActiveDocument

I cannot see how this will make it possible to copy the chart from Excel as
the next lines in your code indicate:
ActiveChart.CopyPicture Appearance:=xlScreen, Size:=xlScreen, _
Format:=xlPicture 'Paste chart at cursor position

But maybe it makes sense in the full context of your code ...

--
Regards
Lene Fredborg - Microsoft MVP (Word)
DocTools - Denmark
www.thedoctools.com
Document automation - add-ins, macros and templates for Microsoft Word

 

[jason]

OK. I try to understand what you are trying to do. Back to the line of code
where you told the error occurs:

If I create a chart in Excel, copy it and then try to run the code in a Word
document (where I have first inserted ”% PLOT_1”) the code fails heretoo.
The problem seems to be that you have used:
DataType:=wdPasteMetafilePicture

That data type that is not available for a chart as far as I can see. Tryto
replace it with:
DataType:=wdPasteEnhancedMetafile

It works here.

An additional comment to your code:
As I understand you, you have (manually) copied the chart before running the
code (you said: (present on clipboard at time of operation)). If that is
correct, you could remove most of the code so that only the following is left:

Set myRange = ActiveDocument.Content
myRange.Find.Execute FindText:="%PLOT_1"
'Leave out the following line if the found text must be replaced by the chart
myRange.Collapse wdCollapseEnd

'Paste the already copied chart at myRange
myRange.PasteSpecial Link:=False, _
DataType:=wdPasteEnhancedMetafile, _
        Placement:=wdInLine, DisplayAsIcon:=False

Since you say all the code is run from within Word, I see no reason for
including the following:

In the lines below you set references to Word and your Word document and not
to Excel:
Set WDApp = GetObject(, "Word.Application")
Set WDDoc = WDApp.ActiveDocument

I cannot see how this will make it possible to copy the chart from Excel as
the next lines in your code indicate:
        ActiveChart.CopyPicture Appearance:=xlScreen, Size:=xlScreen, _
        Format:=xlPicture 'Paste chart at cursor position

But maybe it makes sense in the full context of your code ...

--
Regards
Lene Fredborg - Microsoft MVP (Word)
DocTools - Denmarkwww.thedoctools.com
Document automation - add-ins, macros and templates for Microsoft Word









- Show quoted text -

Lene,
Thank you first for taking such an extended period of time to help me
with my issue.
In all honesty, I have never done something like this before. The code
I am using is borrowed from an internet source (I forget which
exactly).

The code for copying and pasting the image is basically my trust in
someone else's ability. So far the code is still not working
correclty.

I have:


Dim WDApp As Word.Application
Dim WDDoc As Word.Document ' Make sure a chart is selected


Set myRange = ActiveDocument.Content
myRange.Find.Execute FindText:="%PLOT_1"
'myRange.Collapse wdCollapseEnd


' Reference existing instance of Word
Set WDApp = GetObject(, "Word.Application") ' Reference active
document
Set WDDoc = WDApp.ActiveDocument ' Copy chart as a picture
ActiveChart.CopyPicture Appearance:=xlScreen, Size:=xlScreen,
_
Format:=xlPicture ' Paste chart at cursor position
WDApp.Selection.PasteSpecial Link:=False,
DataType:=wdPasteEnhancedMetafile, _
Placement:=wdInLine, DisplayAsIcon:=False ' Clean up
Set WDDoc = Nothing
Set WDApp = Nothing


it is still pasting where my cursor is located rather than where the
desired text to be replaced is located.

 

[jason]

A larger snippet:


With Worksheets(1).ChartObjects.Add _
(Left:=2, Width:=200, Top:=285, Height:=100)
With .Chart
.ChartArea.AutoScaleFont = False
.ChartArea.Border.LineStyle = 0
.ChartArea.Interior.ColorIndex = 2
.ChartArea.Interior.PatternColorIndex = 1
.ChartArea.Interior.Pattern = xlSolid
.ChartType = xlXYScatterLines
.SetSourceData Source:=Range(Sheets(1).Cells(1, 1), Sheets
(1).Cells(UBound(TS_Data), 2))



.Parent.Name = "PRICE_TS"
.HasLegend = False
.HasTitle = True
.ChartTitle.Text = Ins & " " & GetNow
.ChartTitle.AutoScaleFont = False
.ChartTitle.Font.Size = 8
.ChartTitle.Left = 15
.ChartTitle.Top = 1
'.Legend.Position = xlTop
'.Legend.Font.Size = 10
.Axes(xlValue).TickLabels.Font.Size = 6
.Axes(xlCategory).TickLabels.Font.Size = 6
.Axes(xlCategory).TickLabels.Font.ColorIndex = 2
.Axes(xlCategory).TickLabels.Font.Background = xlTransparent

.Axes(xlValue).MinimumScale = WorksheetFunction.Min(Range
(Sheets(1).Cells(1, 2), Sheets(1).Cells(UBound(TS_Data), 2))) -
(WorksheetFunction.Average(Range(Sheets(1).Cells(1, 2), Sheets(1).Cells
(UBound(TS_Data), 2))) * 0.05)
'MinNer - 0.05
.Axes(xlValue).MaximumScale = WorksheetFunction.Max(Range
(Sheets(1).Cells(1, 2), Sheets(1).Cells(UBound(TS_Data), 2))) +
(WorksheetFunction.Average(Range(Sheets(1).Cells(1, 2), Sheets(1).Cells
(UBound(TS_Data), 2))) * 0.05)
.Axes(xlValue).TickLabels.NumberFormat = Curr & "0"
.Axes(xlCategory).TickLabels.NumberFormat = "mmm"
.Axes(xlCategory).MinimumScale = Worksheets(1).Cells(1, 1)
.Axes(xlCategory).MaximumScale = Worksheets(1).Cells(UBound
(TS_Data), 1)

.Axes(xlCategory).HasMajorGridlines = True
.Axes(xlCategory).MajorGridlines.Border.Color = RGB(205, 201,
201)
.Axes(xlCategory).MajorGridlines.Border.LineStyle = xlDot
.Axes(xlCategory).MajorUnit = 30

.Axes(xlValue).MajorUnit = ((WorksheetFunction.Max(Range(Sheets
(1).Cells(1, 2), Sheets(1).Cells(UBound(TS_Data), 2))) +
(WorksheetFunction.Average(Range(Sheets(1).Cells(1, 2), Sheets(1).Cells
(UBound(TS_Data), 2))) * 0.05)) - (WorksheetFunction.Min(Range(Sheets
(1).Cells(1, 2), Sheets(1).Cells(UBound(TS_Data), 2))) -
(WorksheetFunction.Average(Range(Sheets(1).Cells(1, 2), Sheets(1).Cells
(UBound(TS_Data), 2))) * 0.05))) / 4
.Axes(xlValue).HasMajorGridlines = True
.Axes(xlValue).MajorGridlines.Border.Color = RGB(205, 201,
201)
.Axes(xlValue).MajorGridlines.Border.LineStyle = xlDot



.SeriesCollection(1).Border.Color = RGB(0, 84, 165)
.SeriesCollection(1).Border.Weight = xlThin
.SeriesCollection(1).Border.LineStyle = xlContinuous
.SeriesCollection(1).MarkerBackgroundColorIndex = xlAutomatic
.SeriesCollection(1).MarkerForegroundColorIndex = xlAutomatic
.SeriesCollection(1).MarkerStyle = xlNone
.SeriesCollection(1).Smooth = False
.SeriesCollection(1).MarkerSize = 5
.SeriesCollection(1).Shadow = False



.PlotArea.Border.ColorIndex = 16
.PlotArea.Border.Weight = xlThin
.PlotArea.Border.LineStyle = xlContinuous

.PlotArea.Interior.ColorIndex = 2
.PlotArea.Interior.PatternColorIndex = 1
.PlotArea.Interior.Pattern = xlSolid
End With
End With

ActiveSheet.ChartObjects("PRICE_TS").Activate
ActiveChart.PlotArea.Select
With ActiveChart.PlotArea.Border
.ColorIndex = 57
.Weight = xlThin
.LineStyle = xlContinuous
End With
With ActiveChart.PlotArea.Interior
.ColorIndex = 2
.PatternColorIndex = 1
.Pattern = xlSolid
End With

ActiveChart.PlotArea.Top = 5
ActiveChart.PlotArea.Left = 0
ActiveChart.PlotArea.Height = 100
ActiveChart.PlotArea.Width = 200

Dim WDApp As Word.Application
Dim WDDoc As Word.Document ' Make sure a chart is selected


Set myRange = ActiveDocument.Content
myRange.Find.Execute FindText:="%PLOT_1"
myRange.Collapse wdCollapseEnd


' Reference existing instance of Word
Set WDApp = GetObject(, "Word.Application") ' Reference active
document
Set WDDoc = WDApp.ActiveDocument ' Copy chart as a picture
ActiveChart.CopyPicture Appearance:=xlScreen, Size:=xlScreen,
_
Format:=xlPicture ' Paste chart at cursor position
WDApp.Selection.PasteSpecial Link:=False,
DataType:=wdPasteEnhancedMetafile, _
Placement:=wdInLine, DisplayAsIcon:=False ' Clean up
Set WDDoc = Nothing
Set WDApp = Nothing

 

[Lene Fredborg]

I am a bit more confused. Previously - after I said that your code looked as
a mixture of code to be run from Word and from Excel - you said “It is a code
based in Word that makes use of references to excelâ€. But the code you posted
now is definitely for Excel - so it seems as if it is the reverse … ;-)

I tried to put your code in Excel but since declarations of variables etc.
were missing, I skipped the first part and tried running the “Word part†only
(after having created a chart and copied it).

If your code works for you with the exception that the chart is pasted in
the wrong position, you should be able to make it paste correctly if you make
the following change:

After the code line:
myrange.Find.Execute FindText:="%PLOT_1"

insert the following line:
myrange.Select

This is not elegant but this will select the found text - and when the chart
is pasted at the selection, it will be in the correct place. I don’t know how
your code is going to be used and since I still find it confusing, I will
protect you from more attempts on tidying up the code - but hopefully, this
will help you move on.

--
Regards
Lene Fredborg - Microsoft MVP (Word)
DocTools - Denmark
www.thedoctools.com
Document automation - add-ins, macros and templates for Microsoft Word

 

[jason]

Lene,
This works perfectly. Thank you very much for taking the time to help
solve my issue!
I understand your concern with its robustness, but it works all the
same.
Thank you
Jason

 

[Lene Fredborg]

You are welcome. I am glad it works for you now.

--
Regards
Lene Fredborg - Microsoft MVP (Word)
DocTools - Denmark
www.thedoctools.com
Document automation - add-ins, macros and templates for Microsoft Word