ThatExcelNoob
New Member
- Joined
- Jun 7, 2021
- Messages
- 2
- Office Version
- 2010
- Platform
- 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
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