Pass Global var to MS Word 2

[scriverb]

How can I pass a global variable string from ACCESS 97 to WORD 97. Or, read an ACCESS table from MS WORD and retrieve a field value. Either will work.

Table: tblDBParameters
Field: PathToEForms
Variable: vPathToEForms

I open word with the following statement from behind a command button in ACCESS:

Dim vFormName as String
vFormName = vPathToEForms & "DDEStartUp.dot"
Dim MyAppID, ReturnValue                
MyAppID = Shell("c:\program files\microsoft office\office\winword.exe /t" & vFormName, vbMaximizedFocus) ' Open MSWord w/DDEStartUp Form
AppActivate MyAppID ' Activate Microsoft Word.

I need to have MS WORD utilize the vPathToEForms variable or the value of the field in the table tblDBParameters. So, I am asking how do I pass this Global variable vPathToEForms to WORD or have word through some VBA coding read the ACCESS table and retrieve the info.

Thanks.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[RoyVidar]

Hi!

If you try opening a table from word, then either set a reference to access and

[tt]dim acObj as Access.Application[/tt]

or with late binding

[tt]dim acObj as Object[/tt]

Then, perhaps something like this:

[tt]dim rs as object
set acObj=createobject("Access.Application)
with acObj
.opencurrentdatabase("c:\test.mdb")
set rs=.currentdb.openrecordset("table1")
if not rs.bof and not rs.eof then
do while not rs.eof
debug.print rs.fields(0), rs.fields(1)
rs.movenext
loop
end if
rs.close
end with
acObje.closecurrentdatabase
acObj.quit
set acObj=nothing[/tt]

Roy-Vidar

 

[TonyJollans]

Hi Bob,

I'm not sure about passing a variable per se, and I've not used to shelling another Office App, but if I'm understanding you correctly, you are invoking Word with a Template whose path is the variable you want so you could try using [blue]ActiveDocument.AttachedTemplate.Path[/blue] in Word.

If that doesn't suit, let me know and I'll give you some code to read the Access Table from Word.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[scriverb]

You are close. The parameters table in the front end has a field that is used to open word with this path string and template. This template has a has a large number of specialized toolbar buttons with macros that open specific templates upon demand. These templates are linked through DDE to queries in the front-end to merge data to the templates.

The path to those templates is hard coded into the macros. There is a need now for this hard coding to be made more dynamic so that the location of the word templates can be moved in specific PC locations. All of the templates are kept in this path location. I have this path stored in an ACCESS global variable vPathToEForms. It is also stored in the table and field identified above.

Now when the startup template opens I want to read the table in the ACCESS front end and retrieve this path and store it in a global variable in word. I will then modify the macros in WORD to use this path along with the template name to execute the opening of the templates.

I hope I haven't confused you yet. I don't know what the ActiveDocument.AttachedTemplate.Path does but I am about to look it up.

I am trying to use Roy's code above but having a little difficulty. When the line of code to opencurrentdatabase executes the code hangs and the ACCESS front end has the focus. It just sits there and will not continue until I close the access window. Then I get an error because the application is no longer open.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[TonyJollans]

Hi Bob,

As your Access Application is already up and running with the FE open, you don't want to Create an(other). Try using GetObject instead, and no need to Open or Close anything as it's all been done.

[blue]Dim appAccess As Access.Application
Set appAccess = GetObject(, "Access.Application")
vPathToEForms = appAccess.DLookup("vPathToEForms", "tblDBParameters", "[i][/i]Criteria If You Have Any")
Set appAccess = Nothing[/blue]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[scriverb]

Thanks Tony. Worked like a charm. I want to thank both you and Roy for you help on this. Stars for you both.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[scriverb]

One more thing. Roy-Vidar congrants on being voted Tipmaster of the week.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[RoyVidar]

Thank you very much scriverb!

I'm not really very up to date on office 97 automation, but had my above suggestion (well, except at least two typos, that I see now) working on my office 97 test setup. It wasn't very efficient, though, but that's perhaps to be expected with 166MHz and 32MB RAM and a lot of "other junk" on the HD;-))

Even tried now, having both one and two instances of the db open prior to running this from word, in the last case, it took nearly 5 seconds, but it didnt halt, so that stumped me a bit, but I'm happy you've got something working!

I must have missed that the db was alredy open, and agree fully with TonyJollans!

Think lot of things are probably easier with 2000+ versions and ADO. Just open recordsets thru ADO, specifying provider, data source, and no instance of Access is needed.

Roy-Vidar