How to copy pictures in Excel and paste them under a word bookmark

[Bon]

Hello all


I have to implement a Word VBA which can copy pictures from MS Excel
2000 and then paste them under a Word bookmark.


I know how to copy a picture in MS Excel Worksheet to the clipboard and

then paste it under a Word bookmark. My code is as below:


Set xlWorkbook = xlApp.Workbooks.Open(FileName:=WorkbookToWorkOn)
Set xlWorksheet = xlWorkbook.Worksheets("Report1")
xlWorksheet.Range("A1:O50").Copy


ActiveDocument.Bookmarks("Report").Range.PasteSpecial link:=False,
DataType:=wdPasteMetafilePicture


But, how can I copy pictures in Excel and paste them under a Word
bookmark. I also have some conditions on determing which pictures have
to copy and paste them in workd.


Please give me some advices


Thanks
Bon

 

[Cindy M -WordMVP-]

Hi Bon,

I have to implement a Word VBA which can copy pictures from MS Excel
2000 and then paste them under a Word bookmark.
I know how to copy a picture in MS Excel Worksheet to the clipboard and
then paste it under a Word bookmark. My code is as below:

Set xlWorkbook = xlApp.Workbooks.Open(FileName:=WorkbookToWorkOn)
Set xlWorksheet = xlWorkbook.Worksheets("Report1")
xlWorksheet.Range("A1:O50").Copy
ActiveDocument.Bookmarks("Report").Range.PasteSpecial link:=False,
DataType:=wdPasteMetafilePicture

But, how can I copy pictures in Excel and paste them under a Word
bookmark. I also have some conditions on determing which pictures have
to copy and paste them in workd.

I don't understand the question. You state at the beginning you can do
this, and show us the code. What is it you don't know how to do?

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 8 2004)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or
reply in the newsgroup and not by e-mail

 

[Bon]

Sorry, I should state my question clearly.

I don't know how to paste several copied excel sheets in the following
format.
Appendix A <- auto numbering
Paste Picture A
Appendix B
Paste Picture B

My code is as follow
Sub ProcedureA()
....
Set xlObjWorksheet = xlWorkbook.Worksheets("ObjSheet")

If xlObjWorksheet.Range("D25").Value = "Yes" Then
Set xlWorksheet = xlWorkbook.Worksheets("Sheet1")
xlWorksheet.Range("A1:H34").Copy
PasteReportUnderBookmark
End If

If xlObjWorksheet.Range("D29") = "Yes" Then
Set xlWorksheet = xlWorkbook.Worksheets("Sheet2")
xlWorksheet.Range("A1:O33").Copy
PasteReportUnderBookmark
End If
End Sub

Private Sub PasteReportUnderBookmark()
Dim wdRangeAppendix As Word.Range
Set wdRangeAppendix = ActiveDocument.Bookmarks("appendix").Range
With wdRangeAppendix
.Collapse Direction:=wdCollapseStart
.ListFormat.ApplyListTemplate
ListTemplate:=ListGalleries(wdNumberGallery).ListTemplates(4)
.Move Unit:=wdParagraph
.InsertAfter vbCrLf
.PasteSpecial link:=False, DataType:=wdPasteMetafilePicture
.Collapse Direction:=wdCollapseStart
.InsertBreak
End With
ActiveDocument.Bookmarks.Add ("appendix"), wdRangeAppendix
End Sub

The above code I used, the auto numbering is always put at the end of
the pictures and a blank sheet will be generated at the end. Could you
give me some advices, please?

Thanks

Cheers
Bon