Word Crash, paste fail and colour loss during VBA Macro when Pasting Image from Excel

ThatExcelNoob

New Member
Joined
Jun 7, 2021
Messages
2
Office Version
  1. 2010
Platform
  1. Windows
This code is driving me insane. I have created a macro that is structured as below. It creates a chart based, copies it from an Excel Chart tab and pastespecials it into word. Creates a page break, clears the clipboard and does it again for a total of around 100 times. Please see the below problems, assistance on any would be amazing.

I'm using Office 2010. Word Object Library 14.0 was added to Excel references to control Word.

Problem 1: Word Crash (biggest problem, happens 90% of the time)
I have looked on many forums. A lot of people report clipboard crashing during a process like this. I can't find someone reporting that word itself crashes. Partway through, I will get "Microsoft Application Error Reporting has stopped working", then a separate error "Run-time error 462 The remote server machine does not exist" and then Word will just crash. The file gets to around 20mb by this point or 30ish loops so maybe it's just too heavy for word.

Problem 2: Pastespecial fail (happens 30% of the time)
The excel code pastes the Excel Chart object as a paste special metafile picture. It will work for also around 30 loops and then suddenly.... it can't paste special. I can manually paste it and sometimes can manually paste it as a special paste meta file picture. But the code can't?! Retry code doesn't fix this (screenshot the Excel user form graph again, paste it into a new excel chart tab, copy the excel chart tab, paste into word).

Problem 3: Pasted pictures are... black? reverse contrast? (happens 10% of the time)
This one is far more inconsistent than the other 2. It can happen at the very start of the loops, or much later. Suddenly, without error, the pasted pictures into Excel Chart look like they've reverse contrasted whilst the source (userforms) look normal. This then pastes reverse constrasted pictures into word

The Code Structure
Sorry, I cannot paste the code itself here. Just some advice to sort this out would be great :(

- Declare variable and define a word object, opening it. - save as the file [ 1st LOOP START - Loops ~ 5 times] For x in array [ 2nd LOOP Start - Loops ~ 20 times each 1st loop] For y in x - Create a chart in Excel - Copy Chart - Pastespecial (datatype: wdPasteMetafilePicture) in Word - Insert break to go to next page - Clear clipboard next y [2nd LOOP END] - Save next x [1st LOOP END] - Save - Close everything
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

ThatExcelNoob

New Member
Joined
Jun 7, 2021
Messages
2
Office Version
  1. 2010
Platform
  1. Windows
All three problems appear to be simply a processing problem. The file got too big with pictures for the computer to handle. I changed the code to create a new document every 20 pictures, closing the previous. This creates a lot of files but none of the problems have happened again. It's not really a SOLUTION to these problems but it's a work around and acceptable....
 

Forum statistics

Threads
1,136,633
Messages
5,676,896
Members
419,657
Latest member
ExcelAl1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top