Copying from Excel to Word Bookmarks

[pradipto]

Hi, I need some help regarding copying from Excel to Word.

Suppose I have a range $A$1:$E$40 in an excel document say "C:\MyExcel.xls" which has a sheet called "MySheet" and I have a corresponding Bookmark called "MyBookmark" in a word document called "C:\MyWord.doc".

Can some one help me with the VBA macro code to
(a)first remove anything that is in the bookmark
(b)copy the range to word in that bookmark as a BITMAP PICTURE with properties such that the copied size is of 80% of height & weight etc

Thanks in advance. Any help would be greatly appreciated.

Regards.

 

[fumei]

a) is easy;

ActiveDocument.Bookmarks("[i]bookmark_name[/i]).Range.Text = ""

will make the bookmark range text be nothing. Some vesions of Word will also delete the bookmark itself however. To be safe you can go to the bookmark locaction, make the range text = "", then add the bookmark again.

Aside: this is also useful to dynamically insert bookmark text for all versions of Word.

With Selection
    .GoTo what:=wdGoToBookmark, Name:="[i]bookmark_name[/i]"
[COLOR=red]' can also use a string[/color red]
    .Collapse Direction:=wdCollapseEnd
    ActiveDocument.Bookmarks("[i]bookmark_name[/i]").Range.Text = ""
[COLOR=red]' or insert an actual string
' if using a non 0 length string you need the next line
'   .MoveEnd unit:=wdCharacter, Count:=Len(string)[/color red]
    ActiveDocument.Bookmarks.Add Name:="[i]bookmark_name[/i]", Range:=Selection.Range
    .Collapse Direction:=wdCollapseEnd
End With

As for b)...it depends. Can be done, but are you doing this "live"? Are you wanting to do an actual during-execution copy and paste?

Gerry

 

[pradipto]

Hi Gerry,

Yes I am planning to do an copy and paste, unless there is a better alternative. Basically I have a template of a report that I have to send every month. I basically have some analysis done once a month whose output is dumped by Matlab into text files, then read (& nicely formatted) into Excel into a table via a macro in Excel VBA, and finally I want to paste it as a BITMAP picture (BITMAP - since I have some special characters that don't appear if I simply use the picture option to paste).

Could you help me with this part of the code too ?

Thanks,
P.