How to access a Word public variable in Excel VBA

9
votes

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...

2
vbaexcelms-word
Turn UserInfo into a function and give it a return value that can be assigned to a variable in Excel – SierraOscar
@MacroMan UserInfo is built into the templates and is not something I'm able to tamper with... – Holene
You could create your own UDF though to return the variable – SierraOscar
@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
"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

2 Answers

4
votes

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)
3
votes

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.