passing variables from excel to word


M

Mike NG

If I want to open word from excel using automation like this, is there
any way I can pass two variables across - the only way I can think of at
the moment is to use a file in between with the values in!

Sub OpenWord()

Dim oDoc As Word.Document
Dim oWord As Word.Application


'See if word's already running
On Error Resume Next
Set oWord = GetObject(, "Word.Application")
If Err Then
'No, so start a new word session
Set oWord = New Word.Application
Err.Clear
End If

oWord.Visible = True
oWord.Activate
Set oDoc = oWord.Documents.Open(ThisWorkbook.Path & "\Envelope.doc")
 
Ad

Advertisements

M

Myrna Larson

What are these two variables supposed to be used for in Word? Are they arguments for a Word
macro? Actions to be taken in Word (in that case I think your macro can just carry them out).
 
M

Mike NG

Mike,

If you are trying to insert data into your Word document, I recommend using document variables.

You can place fields in your document such as {DOCVARIABLE Product}

In your code you can assign values to the variables:

oDoc.Variables("Product") = "Oranges"
While that sounds cool, is there any way of passing variable into the
code section of the VBA in the word document from the code of excel?

I assume that once in the word document, I can read them into the VBA
code, and then set them to null, because I take it that all DOCVARIABLES
in the word document must appear in the page-
 
M

Mike NG

What are these two variables supposed to be used for in Word? Are they arguments for a Word
macro?
Yes

It's for passing the queue name of the printer that I want that word
document to temporarily use
 
D

Doug Robbins - Word MVP

Hi Mike,

In the Word VBA code, you can use oDoc.Variables('Product") or
oDoc.Variables('Product").Value (though the value is not really necessary
because it is the default property of a Variable. Having does maybe make
the code a bit easier to understand )

The variables do not have to appear anywhere in the document if you don't
want them to, so you can leave the value in them. If you do want to display
the value of the variable in the document, you do so by use of a {
DOCVARIABLE "Product" } field.

Please post any further questions or followup to the newsgroups for the
benefit of others who may be interested. Unsolicited questions forwarded
directly to me will only be answered on a consulting basis.

Hope this helps
Doug Robbins - Word MVP
 
M

Mike NG

In the Word VBA code, you can use oDoc.Variables('Product") or
oDoc.Variables('Product").Value (though the value is not really necessary
because it is the default property of a Variable. Having does maybe make
the code a bit easier to understand )

The variables do not have to appear anywhere in the document if you don't
want them to, so you can leave the value in them. If you do want to display
the value of the variable in the document, you do so by use of a {
DOCVARIABLE "Product" } field.
OK thanks - I will give that a go some time soon - just been away for a
long weekened
 
Ad

Advertisements

M

Mike NG

In the Word VBA code, you can use oDoc.Variables('Product") or
oDoc.Variables('Product").Value (though the value is not really necessary
because it is the default property of a Variable. Having does maybe make
the code a bit easier to understand )

The variables do not have to appear anywhere in the document if you don't
want them to, so you can leave the value in them. If you do want to display
the value of the variable in the document, you do so by use of a {
DOCVARIABLE "Product" } field.
I seem to be having some sort of synchronisation problem. This is my
excel code

Sub OpenWord()

Dim oDoc As Word.Document
Dim oWord As Word.Application


'See if word's already running
On Error Resume Next
Set oWord = GetObject(, "Word.Application")
If Err Then
'No, so start a new word session
Set oWord = New Word.Application
Err.Clear
End If

oWord.Visible = True
oWord.Activate
Set oDoc = oWord.Documents.Open(ThisWorkbook.Path & "\Envelope.doc")
oDoc.Variables("Product") = "HELLO WORLD"
Set oWord = Nothing
Set oDoc = Nothing

End Sub



This is a simplified version of my word document open event

Private Sub Document_Open()

MsgBox ">>" & ThisDocument.Variables("Product") & "<<"

End Sub


However, when word opens, it tells me the object has been deleted
 
D

Doug Robbins - Word MVP

HI Mike,

How about if you include

oDoc.Save

before setting things to nothing.

Please post any further questions or followup to the newsgroups for the
benefit of others who may be interested. Unsolicited questions forwarded
directly to me will only be answered on a consulting basis.

Hope this helps
Doug Robbins - Word MVP
 
M

Mike NG

HI Mike,

How about if you include

oDoc.Save

before setting things to nothing.

Please post any further questions or followup to the newsgroups for the
benefit of others who may be interested. Unsolicited questions forwarded
directly to me will only be answered on a consulting basis.
No that didn't work. Now I've got my default printer working (see other
thread), I'm not so bothered about this now, but it has got me
intrigued.
 
Ad

Advertisements

D

Doug Robbins - Word MVP

Hi Mike,

Are ThisDocument and oDoc the same document.

If you do not save the document after creating the variable, the variable
will not be saved with the document.

Please post any further questions or followup to the newsgroups for the
benefit of others who may be interested. Unsolicited questions forwarded
directly to me will only be answered on a consulting basis.

Hope this helps
Doug Robbins - Word MVP
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top