Call an Excel macro from Word with data

[JackGombola]

I'm using Excel 2003 and Word 2003.

The text at the end of this post was related to an earlier question I asked
of this group that you answered fully. Thanks.

Now, I need to call that Excel macro from Word.
I will receive Word documents that contain player stats including last name
and team name. I will select last name and copy it to the clipboard and then
copy the player's team name to the clipboard.

What I would like to do then is to create a Word macro to:
1. Retrieve the last two items placed in the clipboard and pass that data
to an Excel workbook containing sheet 1 as (Lastname,Tname) . These two
items will replace the InputBox statement in the Excel macro.
2. Return to Word so the user can select and copy the next player and run
the Word macro again.

The solution below used Fname and Lname separed by commas. I really only
need Lname.
Thanks,
Jack

____________________________________________________________________
Hello All: I have an Excel 2003 spreadsheet that looks like this:
A B C
1 Lastname Firstname Col1 Col2 Col3 … Col111
2 Lname 1 Fname1 FZN

 

[Joel]

Why ae yo having the user copy and select items in the word document. Isn't
there a better way to fully automate the macro?

Is the names in the wrod document in a table? Can another column be added
in the tabble where you can place a checkbox? Can you copy the entire list
and paste it into excel in a temporary worksheet and then do the selection in
excel? I like this method the best.

 

[JackGombola]

Joel: Thanks for your message. I agree that there are probably better ways
to accomplish the objectives. Actually, I'm very interested in re-awakening
some of my programming abilities that have been asleep for many years. My
request is more for me that for anything else.

I have never written a macro in Word and am very interested in how one MS
Office application calls and passes data to another. I also like the
solution you suggested but it's really more for my experience with coding VBA
than getting something done efficiently.

Thanks,
Jack

 

[Joel]

You don't need to use the clipboard to pass data between microsoft office
applications. When office is installed the DLL are installed on your PC and
each office application has the ability to access all other office
application using the DLL's. So excel can open a word document in VBA in the
same manner as Word VBA can open a word document.

The VBA language is slightly different from one VBA application to another.
Word documents are probably the hardest to work with from other office
application and somethings can only be done using word VBA.

To move data between office application you simple have to get the object
from one application and then write to the other application directly. For
example you can go to a word document and get the 2nd senetence in the 3rd
paragraph and put in in a variable MySentenance. Then write the string
MySentenance to an excel workbook in sheet "Sheet2" in cell B7. The hardest
part of the code is to get the correct text in the word document. tghe few
times I needed to get items in Word I usually have to get help from the Word
experts. I can do just about anything in excel VBA.

What you may need to do is to put a portion of your VBA macro in a wor
document. For example do the following from Excel VBA


1) Open a word document with macro code
2) Call the macro in the Words document passing a parameter of a 2nd word
document with the Players Stats.
3) The word macro will put the player info into the clipboard and return to
excel
4) Excel will copy the clipboard into the spreadhseet.

It may even be better to create a temporary text file with the word data and
then have excel open the text file a put the results from the text file onto
a spreadsheet.

I have just found from my experience that it is extremely difficulkt from
excel to read and write word documents directly.

1