Word Excel PasteSpecial Bitmap Problem

[The Ghost in the Machine]

Dear All,

I am having a very strange and odd problem with Excel and Word.
If you could kindly propose any solution ideas, I shall appreciate it very
much.

I have a reporting VBA application on the Excel. Smoothly produces all the
reports.
I have to put the reports in a Word application. The reports are not fixed
row and column sizes. I have to generate a Bitmap image of the Excel report
and
tried to Paste it in the Word Application. Very strangely, on the very first
runs no problems. It worked, I had the reports Bitmap pics on Word beatifully
as I wanted but then later something happened, may be the ghost in the
machine.
Excel VBA is copying the range and sending it to the Word application but,
the pasted bitmap is a blank object, sadly, very sadly nothing inside.
In order to complete my reports on time, I tried a backdoor tactic and
created a Word macro doing the PasteSpecial Datatype:=wdPasteBitmap and
assigned it to a keyboard shortcut macro. Bingo! It worked. However it is
taking so long to complete a full set of report with approx. 200 x 30
Cntrl-Qs and shifting between the applications is awfully open to error. I
have tried another backdoor route because I were running short on time, I
tried the Sendkeys method but unfortunately, the same ghost in the same
machine, very first few runs it worked, then generated empty objects. I
cleaned the Clipboard if incase there is a memory related problem. No
progess. I tried to pause the code before and after the PasteSpecial steps.
No progess. Then I wondered if the Excel Bitmap function is not working
properly so I tried a code to generate my own bitmap pictures, which worked
fine, but the same ghost in the same machine, blank pages were there after
the second or third runs so sadly and so misfortunately.
I tried the 2007 and 2003 vers. of Excel but sadly, very same.
I am working on a licenced XP Pro and licenced Office Pro Pentium 4 with
4GbRAM and lots of disk space. If you may need to see the code, I shall send
the code as well.

If some Guru could help fiding the ghost in the machine, I shall be more
then gratefull.

Regards,
VBANovice trying to find the Ghost in the Machine

 

[macropod]

Hi VBANovice,

I don't have an answer for the "The Ghost in the Machine" problem, but I can suggest an alternative approach.

If the reports are based on a common Word template and refer to a consistent set of ranges in your Excel file, then you might do
well to use LINK fields in your Word document to import the data. This can be done in Excel or Windows metafile formats. Using this
approach, your Excel macro need only create a document based on the template. If you need to 'fix' the reported data, you could then
unlink the fields in the document. Such a link field might be coded as:
{LINK Excel.Sheet.8 "C:\\My Documents\\Finances\\07-08 Reports.xls" "Summary!R2C1:R15C7" \a \p}
or
{LINK Excel.Sheet.8 "C:\\My Documents\\Finances\\07-08 Reports.xls" "SummaryData" \a \p}
where 'SummaryData' is a named range.

Even if the report ranges in your Excel file are not consistent, redefining the named ranges would work with the second approach,
without needing to modify anything in the Word document.

If the report ranges vary too much for the use of named ranges, you could still have your Excel code create & populate the LINK
fields in the Word file, inserting the appropriate ranges as you go.

Cheers