如何在Excel VBA中访问Word公共变量 [英] How to access a Word public variable in Excel VBA
问题描述
我正在尝试自动化一些报表生成,其中Excel VBA正在进行所有的工作。我的雇主有一套标准化的模板,其中的所有文件都应该从中生成。我需要从Excel VBA填充这些模板之一。 Word模板广泛使用VBA。
这是(一些)我的Excel VBA代码:
生成WordApp对象的代码(GenerateReport()'(Tables,InputDataObj)
'
WordApp.Documents.Add模板:=Brev.dot
'从文档
中的Utilities.Userinfo宏获取用户信息调用WordApp.Run(Autoexec)生成公共变量
调用WordApp.Run(Utilities.UserInfo)
'更多代码
End sub
在Word VBA中 Autoexec
模块,定义和声明一个名为 user
的公共变量。来自实用程序
模块的 Userinfo
子程序填充用户
。这两个例程都没有VBA的投诉。我可以在Excel VBA中访问用户
变量,但是我收到以下错误
编译错误:在此上下文中尚未创建变量。
如何访问Word VBA变量在Excel VBA?我认为它或多或少是一样的?
编辑:用户
变量是一个用户定义的键入
只有 String
属性。复制Word VBA函数填充用户
变量是绝对可行的,只是比我需要更多的工作...
在Word模块中:
公共函数GetUserVariable()As String' //或任何数据类型
GetUserVariable = user
结束函数
在Excel模块:
myUser = WordApp.Run(GetUserVariable)
pre>
或者,您可以复制变量值 - 因为它被称为
user
我怀疑它正在返回有关文档的用户或作者的一些信息。在这种情况下,您可能会遇到以下情况之一:'//分配给应用程序的用户名
MsgBox WordApp.UserName
'//由系统定义的用户名
MsgBox Environ $(USERNAME)
'//作者的名称文件指定
MsgBox CreateObject(Shell.Application)。Namespace(C:\Users\Documents)。GetDetailsOf(MyDocument.doc,9)
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 nameduser
is defined and declared. TheUserinfo
sub from theUtilities
module populatesuser
. Both these routines are run without any complaints from VBA. I would then like to be able to access theuser
variable in my Excel VBA, but I get the following errorCompile 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 definedType
with onlyString
attributes. Copying the Word VBA functions that populate theuser
variable is absolutely doable, just more work than I though was necessary...解决方案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)
这篇关于如何在Excel VBA中访问Word公共变量的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!