How to access a Word public variable in Excel VBA

Question

I'm trying to automate some report generation where Excel VBA is doing all the work. My employer has a standardized set of templates of which all documents are supposed to be generated from. I need to populate one of these templates from Excel VBA. The Word templates utilize VBA extensively.

This is (some of) my Excel VBA code:

Sub GenerateReport() ' (Tables, InputDataObj)
  ' code generating the WordApp object (works!)

  WordApp.Documents.Add Template:="Brev.dot"

  ' Getting user information from Utilities.Userinfo macro in Document
  Call WordApp.Run("Autoexec") ' generating a public variable
  Call WordApp.Run("Utilities.UserInfo")
  ' more code
End sub

In the Word VBA Autoexec module, a public variable named user is defined and declared. The Userinfo sub from the Utilities module populates user. Both these routines are run without any complaints from VBA. I would then like to be able to access the user variable in my Excel VBA, but I get the following error

Compile Error: Variable not yet created in this context.

How can I access the Word VBA variable in Excel VBA? I thought it more or less was the same?

EDIT: the user variable is a user defined Type with only String attributes. Copying the Word VBA functions that populate the user variable is absolutely doable, just more work than I though was necessary...

Turn `UserInfo` into a function and give it a return value that can be assigned to a variable in Excel — SierraOscar, Jan 25 '16 at 13:37
@MacroMan `UserInfo` is built into the templates and is not something I'm able to tamper with... — Holene, Jan 25 '16 at 13:41
What is the `user` variable? Is it just the user's computer id or something? Perhaps you can replicate the exact same thing in Excel VBA using `ENVIRON`? Or just build the same Word function into Excel (if you can see the code in Word). A bit of a work-around, but a possible solution, nonetheless. — Scott Holtzman, Jan 25 '16 at 14:19
@ScottHoltzman yeah, it's a possibility to replicate the functions from Word VBA. I just thought it would/should be easy to access a public variable in one or another VBA instance. — Holene, Jan 25 '16 at 14:27
@Holene it is easy **if you can modify the code of the app that sets the global variable** (or set it up to do so in the beginning), but in this case, you seem to not be able to do that. — Scott Holtzman, Jan 25 '16 at 14:36
"UserInfo is built into the templates and is not something I'm able to tamper with... " In that case, there's no way for you to access the information being generated within Word. However "The code needs to work on other computers as well, and the Templates are set for all users at work" need not block you. There are ways to approach this that won't affect others using the code, such as writing to a document VARIABLE (this is string storage within the document that no one can see and it won't affect anything). — Cindy Meister, Jan 25 '16 at 16:19

SierraOscar · Answer 1 · 2016-01-25T14:41:13.830

4

In a Word module:

Public Function GetUserVariable() As String '// or whatever data type
    GetUserVariable = user
End Function

In an Excel module:

myUser = WordApp.Run("GetUserVariable")

Alternatively, you could be able to replicate the variables value - as it's called user I suspect it is returning some information about a user, or author, of a document. In which case one of the following might be what you're after:

'// Username assigned to the application
MsgBox WordApp.UserName

'// Username defined by the system
MsgBox Environ$("USERNAME")

'// Name of the author of the file specified
MsgBox CreateObject("Shell.Application").Namespace("C:\Users\Documents").GetDetailsOf("MyDocument.doc", 9)

edited Jan 25 '16 at 14:41

answered Jan 25 '16 at 13:46

SierraOscar

17,507
6
40
68

Could this be done through Excel VBA only? Something like Excel VBA adds the module to the Word document, executing and returning the `user` variable? – Holene Jan 25 '16 at 13:49
1

not unless you have access to the Protected VBProject object model - which is disabled by default for security reasons. – SierraOscar Jan 25 '16 at 13:51
I suspect these templates were made several years ago, mid 2000's maybe. How can I confirm that it won't work? – Holene Jan 25 '16 at 13:59
1

Try and run this from Excel: `Set wApp = GetObject(, "Word.Application"): Set wDoc = wApp.Documents(1): Set vPrj = wDoc.VBProject` and you will more than likely get an error saying programmatic access is not trusted – SierraOscar Jan 25 '16 at 14:07
As mentioned in the comments - perhaps if you tell us what the variable `user` is returning we can provide an alternative solution that can be used in Excel? – SierraOscar Jan 25 '16 at 14:25

score 3 · Answer 2 · edited May 23 '17 at 11:45

Another option - if you could only add a line of code to the Utilities.UserInfo sub (after setting your public variable):

ActiveDocument.Variables("var_user") = user

Then you could access it easily afterwards in Excel:

Sub GenerateReport() ' (Tables, InputDataObj)
  ' code generating the WordApp object (works!)

  'I am assuming your WordApp object is public, as you don't declare it.
  'Capture the new document object    
  Dim newdoc as Object
  set newdoc = WordApp.Documents.Add(Template:="Brev.dot")

  ' Getting user information from Utilities.Userinfo macro in Document
  Call WordApp.Run("Autoexec") ' generating a public variable
  Call WordApp.Run("Utilities.UserInfo")

  'Get and show the value of "user"
  Dim user as String
  user = newdoc.Variables("var_user")
  msgbox, user
End Sub

This is assuming that useris a string.

EDIT: As it is a requirement to work only on the Excel VBA, I would definely try the approach suggested by Scott and MacroMan - replicating the same functionality of the Word macros in Excel - if possible.

I assume that you've already ruled out the possibility of using an edited copy of the original template, set in a public folder...

For the sake of completness, there is another possibility: actually it is possible to inject VBA code in a Word document without the VBProject Object Model, by "brute force". If you rename a Word document as a .zip file and open it, you will notice a \word\vbaProject.bin file in it. This file contains the VBA project for the document and, in principle, one could add or change VBA code by modifying or replacing it.

I did some tests transplanting code from one document to another by simply copying the vbaProject.bin file, and the concept works. If you are interested in learning more about this file, this topic could be of use.

Notice, however, that to do what you want with such a technique would be somewhat complex (it would involve, for starters, updating zip files from your Excel VBA), and would require a lot of experimentation to mitigate the risk of accidentally corrupting your files. Definetly not recommended if you are looking for an easy and simple solution - but it is possible.

Notice that with this technique, if the document is saved, the value of the document variable "var_user" is saved with it and can be acessed afterwards without re-running the macros (by just opening the document and checking its Document.Variables collection). — dnep, Jan 25 '16 at 15:07
Also notice that Word has a hard time setting DocVariables to an empty value; you'd have to use a special character sequence to indicate them if they can occur. — Carl Colijn, Jan 25 '16 at 15:11
It would absolutely be lovely to use this method, but I can't touch the Word VBA. The code needs to work on other computers as well, and the Templates are set for all users at work... — Holene, Jan 25 '16 at 15:12
But is the Word VBA password protected or can you at least read it? — dnep, Jan 25 '16 at 15:39
I can read it. This is very interesting! I'll try copying the VBA function that populates `user` (and it's dependencies) into my Excel VBA first, but this could absolutely be a way around! Thanks for your thoughts! — Holene, Jan 26 '16 at 10:11

How to access a Word public variable in Excel VBA

2 Answers2

Linked