Passing String parameter from Access to Word Macro

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Chris - 26 Oct 2005 19:05 GMT

Here is what I have:

I created an Access database that is used to import text file data from
Novell server configuration text file. In the first step of the process I've
set the "Path" that I want all macros to use using a global string
parameter(Global strString As String): strString = "I:\Configs".

What I would like to do is pass the "Path" from the Access call to the Word
Macro:

'This command runs the referenced Macro in the Word Aplication
ObjWord.Run MacroName:="Normal.NewMacros.Stepa_CreateServerTextFiles"

Does anyone know how this could be done????

Reply to this Message

Helmut Weber - 26 Oct 2005 21:59 GMT

Hi Chris,

probably like this:

Access:

Public Sub PassToWord()
' reference to word library set
' tools references
' word already running
Dim oWrd As Word.Application
Set oWrd = GetObject(, "Word.application")
oWrd.Run "GetFromAccess", "Test"
End Sub

Word:

Sub GetFromAccess(s As String)
MsgBox s
End Sub

Tested with Excel, but shouldn't matter.

Signature

Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

Win XP, Office 2003
"red.sys" & Chr$(64) & "t-online.de"

Reply to this Message

Helmut Weber - 26 Oct 2005 22:03 GMT

...

once again I was too fast.

This is the crucial point:

Not:
"Normal.NewMacros.Stepa_CreateServerTextFiles"
instead:
"Stepa_CreateServerTextFiles"

"Project.module.sub" doesn't work as documented
when using arguments.
Use an applicationwide unique name for the sub
or the function to be run.

Signature

Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

Win XP, Office 2003
"red.sys" & Chr$(64) & "t-online.de"

Reply to this Message

Chris - 26 Oct 2005 22:25 GMT

Helmut,

I must not have made my self clear on what I wanted. The Access program
opens Word and starts the referenced macro by using:

'This command runs the referenced Macro in the Word Aplication
ObjWord.Run MacroName:="Normal.NewMacros.Stepa_CreateServerTextFiles"

This works as desired. The macro starts and completes as desired. However,
within the macro there is a statement that sets the application source path:

strString = "I:\Configs"

What I want to do is remove the hard coded "Path" ("I:\Configs") and have
Word look for an open instance of the Access database (with a specified name)
and if open grab the application path set in a specific table and use that
information to set the strString parameter's value.

Reply to this Message

Helmut Weber - 26 Oct 2005 22:46 GMT

Hi Chris,

> ... and have Word look for an open instance of the Access database
> (with a specified name) and if open grab the application path
> set in a specific table and use that information to set
> the strString parameter's value.

hm...

If Access is running, then Access should no,
what database is open or if there is a database at all.

Public Sub PassToWord()
' reference to word library set
' tools references
' word already running
Dim oWrd As Word.Application
Set oWrd = GetObject(, "Word.application")
oWrd.Run "GetFromAccess"
End Sub

Sub GetFromAccess(Optional s As String)
Dim sPth As String
sPth = "c:\configs"
If s <> "" Then sPth = s
MsgBox sPth
End Sub

Signature

Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

Win XP, Office 2003
"red.sys" & Chr$(64) & "t-online.de"

Reply to this Message

Chris - 26 Oct 2005 23:19 GMT

Duh, I finally got what you were saying. It's been a long day. Thanks for
the help.

Here is what I did. In Access I wrote:

'This process creates an incident of the Word Application
Dim ObjWord As Word.Application
Set ObjWord = New Word.Application
ObjWord.Visible = True
AppActivate "Microsoft Word"

Set ObjWord = GetObject(, "Word.application")
ObjWord.Run "GetFromAccess", rst![Database Location]

rst.Close
Set rst = Nothing

'This command runs the referenced Macro in the Word Aplication
ObjWord.Run MacroName:="Normal.NewMacros.Stepa_CreateServerTextFiles"

In Word here is what I wrote (Note: The strString field is a global
parameter):

Sub GetFromAccess(appLocation As String)

'This process is used to obtain the "Database" path form the access
'database. This removes the need for user intervention within the
'Word application. This replaced the "strString = 'I:\Configs"' line
'within the "Stepa_CreateServerTextFiles" Macro.
strString = appLocation

End Sub

Reply to this Message