Running Word from an Excel macro

[bigfishprf]

I have recorded a macro in Word to carry out a Mail Merge. I then have an Excel macro which initiates Word and Runs the Word macro.
I would prefer to run the whole thing from within Excel so that I can make the workbook file name within the merge dynamic but when I copy the code into my Excel macro I get error "Command Failed".

I can resolve this by linking the MS Word Object Library in Excel but I have been avoiding doing this as there are a number of different Word versions in use and that creates it's own problem! The error occurs at the line highlighted in red. Any suggestions to help please?

Please Login or Register  to view this content.

[LJMetzger]

Hi bigfishprf,

Try the download files associated with post #2 in the following thread: http://www.excelforum.com/excel-gene...e-problem.html

I suggest try using my data files first, before you try to use your own data files, to verify that the software works in your environment.

Lewis

[bigfishprf]

Lewis.
Many thanks for your assistance. As I expected, it all works perfectly...... even using my data files. As I am a relative (self taught - thank God for Excel VBA for Dummies!) newcomer to VBA, I now need to try and understand your code and what changes I need to make so that it works in the way I want. i.e. My command button is actually on the worksheet I want to use within the merge and the Word component is a template rather than a docx. Should be fun!
Thanks again for your help, it gives me plenty to work on.

Paul

[LJMetzger]

Paul,

I'm glad it worked out for you.

The following may help you now and in the future. Find a place in the code that you are interested in, and set a breakpoint on that line. After the breakpoint has been triggered, single step through the code using the F8 key:
Debugger Secrets:
a. Press 'F8' to single step (goes into subroutines and functions).
b. Press SHIFT 'F8' to single step OVER subroutines and functions.
c. Press CTRL 'F8' to stop at the line where the cursor is.
d. 'Left Click' the margin to the left of a line to set (or clear) a BREAKPOINT.
e. Press CTRL 'G' to open the IMMEDIATE WINDOW. 'debug.print' statements send their
output to the IMMEDIATE WINDOW.
f. Select View > Locals to see all variables while debugging.
g. To automatically set a BREAKPOINT at a certain location put in the line:
'Debug.Assert False'
h. To conditionally set a BREAKPOINT at a certain location put in lines similar to:
if i >= 20 and xTV20 > 99.56 then
Debug.Assert False
endif
i. A variable value will be displayed by putting the cursor over the variable name.

I hope this helps.

Lewis

[bigfishprf]

Lewis, thanks for the extra tips...... all really useful

I managed to embed your code in my spreadsheet, choose the word document based on the active sheet and merge different data again based on the active sheet........... and it all still works!

Thanks again

Paul