Sending To Excel

[Benz]

Hi, I'm having trouble.... hopefully one of you out there can help. I have
a toolbar with a textbox in a word doc (Test.doc) I want to send whatever is
typed in that textbox to the first empty row in col B / Sheet 2 - in an
excisting Excel doc (Notes.xls).

I appreciate any help.

Thank you,

Ben Z.

 

[Shauna Kelly]

Hi Ben Z

I'm not clear whether you want this code to run from Excel or from Word.

There is information about doing both at the following:
Control Excel from Word
http://www.word.mvps.org/FAQs/InterDev/ControlXLFromWord.htm

Control Word from Excel
http://www.word.mvps.org/FAQs/InterDev/ControlWordFromXL.htm

If you need more information, post back and let us know where and how you
are invoking the code, and what version of Word and Excel you're using.

Hope this helps.

Shauna Kelly. Microsoft MVP.
http://www.shaunakelly.com/word

 

[Benz]

Hi Shauna,

Thank you for the link. Everytime I try that code I keep gettin an error
that says "Compile Error : User Type Not Defined. " .... any ideas why?

I'm launching the code from Word 2003 to send info to Excel 2003.

~Ben Z.

 

[Benz]

Nevermind I realize I didn't reference the right Object. My only question
now is - do I use VBA-code like I would in WORD or EXCEL?

 

[Shauna Kelly]

Hi Benz

So you're running code in Word, and you're controlling Excel from Word.

Because you're running code in Word, the code already knows all about Word,
and it has a built-in reference to the Word application. But code running in
Word won't automatically know you're talking about Excel, so you have to be
explicit.

If you were just working in Excel, then this would be OK:
Dim rng As Range
Set rng = ActiveSheet.Range("A1")
rng.Value = "hello world"

Excel would interpret this and understand that Range is an Excel range, and
Excel knows that an Excel range has a property Value and Excel knows what to
make of .Range("A1"). And, Excel knows what an ActiveSheet is.

If you run that code in Word, then Word will assume the Range is a Word
range, and it knows that a Range doesn't have a .Value property. Word
doesn't know what "A1" might mean. And Word doesn't have a clue what an
ActiveSheet is.

Using the code you found at
http://www.word.mvps.org/FAQs/InterDev/ControlXLFromWord.htm you will have
created a reference to the Excel Application. You use that reference to tell
the code when you want to refer to Excel.

So if you want to do the same thing from code running in Word then you need
something like:

Dim oXL as Excel.Application
Dim rng as Excel.Range

set oXL = new Excel.Application
set rng = oXL.ActiveSheet.Range("A1")
rng.Value = "hello world"


It's OK to mix up object types from Word and Excel in the same bit of code,
like this:

Dim oXL as Excel.Application
Dim rngXL as Excel.Range
Dim rngWD as Word.Range

set oXL = new Excel.Application
set rngXL = oXL.ActiveSheet.Range("A1")
rngXL .Value = "hello world"

set rngWD = Word.ActiveDocument.Paragraphs(1).Range
rngWD.text = "hello world"


Two suggestions:
(1) name your variables in some kind of consistent way so you can tell at a
glance which is referring to a Word object and which is referring to an
Excel object

(2) Wherever possible, preface everything you can with either Word or oXL,
so:
Word.ActiveDocument.......
oXL.ActiveSheet.....

It's not strictly necessary to preface things with Word. when running in
Word, but it can't hurt and it helps make the code easier to write, read and
debug.

Hope this helps.

Shauna Kelly. Microsoft MVP.
http://www.shaunakelly.com/word

 

[Benz]

Hi Shauna,

Thank you so much for explaing that to me! I feel like I finally have a
grasp over what I'm doing ... I have it all working now.

Have a great weekend!

Ben Z.

 

[Shauna Kelly]

Hi Ben

I'm glad it helped

Shauna Kelly. Microsoft MVP.
http://www.shaunakelly.com/word