Pass variable from Access VBA to Word VBA 2

[zandsc1]

Hi All -

I have a word document that runs a routine when I open it that prompts you for a string and then updates some fields with your input. I would like to be able to input the string in my Access database and then in the background open up the word document, pass it the string, have it update, have it print, and have it close without saving.

Opening the document isn't a problem, and I can figure out how to print it out and close it without saving with little difficulty, but I'd like to use my existing Word routine and just be able to have Access pass the string to Word instead of prompting the user for it.

Thanks in advance for any help.

 

[kjv1611]

There may be a more direct way, but I'm sure you can use the clipboard or SendKeys, assuming the Word Doc opens where you can actually see/select it while the code is running.

 

[kjv1611]

Oh, and I meant to say that if the actions running in Word are VBA based as well, why not just move them over to Access, so you have all the code in one place. Then sending the value from one procedure to another will be as simple as using a global variable.

 

[zandsc1]

thanks for the responses kjv, I'll have to look into using clipboard or SendKeys. The reason I don't want to move the code into Access is that I want the word documents to still function on their own (i.e. if I open up a word document without the database open the vba code still runs). Both events will likely happen frequently enough that I want to be ready for either at any time.

 

[MajP]

Sendkeys and clipboard???

How to run code in another application without a reference:

Inside the word Document

Public Sub runTest(strMsg As String)
  MsgBox strMsg
End Sub

Inside Access

Sub OpenWordDoc()

  Dim wdApp As Word.Application
  Dim wdDoc As Word.Document

On Error Resume Next
 Set wdApp = GetObject(, "Word.Application")
If Err.Number <> 0 Then 'Word isn't already running
 Set wdApp = CreateObject("Word.Application")
End If
On Error GoTo 0
 Set wdDoc = wdApp.Documents.Open("C:\testDoc")
 wdApp.Visible = True

[b] wdApp.Run "runTest", "Hello World" [/b]
End Sub

Access opens word, calls the method and passes the parameter.

 

[kjv1611]

The .Run... part was a portion I had not thought about or tried before. Thanks, MajP. The others would indeed work, but yes, they are not the optimal way to do things. The statement was that it CAN be done that way, not should be.

Anyhow, the wdApp.Run method does appear to be the way to go. I'll have to remember that for future reference!

 

[MajP]

The others would indeed work.

Out of curiosity, could you post an example of using send keys? I do not think I would know how to do that.

 

[kjv1611]

I have used it a few times, but generally do look for ways to NOT use SendKeys.

However, in some cases, using that dirty method does do wonders. For instance, once in a recording situation, I was saving information to an Access database for various bits of info on the recording, much of which was auto and semi-auto input into the database. Then at the end of recording a file (with Creative MediaSource Player), I'd hit a button in Access that would automatically update the file info in Creative MediaSource by filling in the fields it asked for.

So in a case where you've got a Word document open, if a form were asking for a value, you could use sendkeys to send the appropriate string.

HOWEVER, here's where it isn't as good a method. In my specific example (that we used multiple times every week for a few years), at one point when we switched to using a P4D CPU based computer - only real difference - the SendKeys was more erratic, b/c SOMETHING was grabbing focus in the middle of the sendkeys operation. It probably was software related, but I never took the time to fully dicifer the situation.

Because of my situation, I went looking for any possible better methods, and found a SendKeys API by Dev Ashish that works better than the built-in. So if you wanted to use SendKeys more than sending a stray keystroke here and there, then it'd definitely be worth looking at. His API works the same or basically the same as the built-in function.

Here is Dev Ashish's API, which to use all you have to do is copy/paste the code to a new VBA Module:

http://www.mvps.org/access/api/api0046.htm

But at the very basics of it, you could do something like this....

First, use the Shell command to send focus to a particular window/application/document/file... then you would use the SendKeys command with whatever you wanted to send.

Oh, and here is a tek-tips FAQ on the subject, though it doesn't seem to go very deep.. it doesn't specify the application where you want to send the keystrokes, which I personally think is a necessary piece - otherwise, focus could be sent to the wrong application, and that could be VERY bad:
faq707-5037

For understanding the Shell commands (not that it's absolutely necessary for a basic SendKeys command), take a look here:

http://www.mvps.org/dmcritchie/excel/shell.htm

Well, I started to just make something up, but actually I think I can go and find some code I've used at work in the past - I try to keep everything at least somewhere close by, so I can always refer to it for other projects, and this I've done MANY times!

At least for one or two instances, I think I've communicated between different systems by using SendKeys at work, but let me see if I can find an example, and I'll just post that...

 

[kjv1611]

Alright, here are a couple of short examples from one database at work where I wanted to send a couple commands to a mainframe system:

Private Sub cmdEndSession_Click()
    Dim MyAppID, ReturnValue
    AppActivate "NewPlacements_Export"
    SendKeys "A"
    AppActivate "The Network has disconnected"
    SendKeys "A"
End Sub

The reason for the AppActivate having to use a different name on 2 different occasion (If I remember correctly - was about 5 years ago) was because the Window Title Text would change based on what was going on in that particular application.

Here's a more generic one, using the Alt + F4 command to kill off a specific application:

Private Sub lblKillMyApp_Click()
    On Error Resume Next
    
    lblKillMyApp.SpecialEffect = 2
    Me.TimerInterval = 50
    Dim MyAppID, ReturnValue
    AppActivate "MyApp"
    SendKeys "%{F4}"
    SendKeys "Y"
End Sub

So, in that particular instance (last example), I'm telling Windows to turn off a particular application, and then when that application was closing, it asked was I sure I wanted to close the application... so I'd send the Y character to simulate the Yes button.. It worked perfectly every time... I think I ran that one for about a year... from 2004 to 2005, so that's 6 years ago that I started with it, I guess.

The one I did for my church was more complicated, b/c sent much more information, and most of the strings sent via SendKeys were sent from variable values.

All my examples listed and used, personally, have been from within Access, but I believe it'll work the same, regardless of which Office application is used.

 

[kjv1611]

Oh, one other thing I forgot to metion. For the simple examples above, I didn't use the Shell commands for setting the application focus, but I did with the slightly more complex one for church. If I remember later this weekend, if anyone wants to see it, I can pull that code up at home, and post at least some of it for reference as well.

 

[MajP]

I understand the sendkeys part to send keystrokes to the application, but still do not know how you would use that to pass a variable to code within the application. That is the part, that I am not sure can be done.

 

[kjv1611]

Here's why/how:

I have a word document that runs a routine when I open it that prompts you for a string and then updates some fields with your input. I would like to be able to input the string in my Access database and then in the background open up the word document, pass it the string, have it update, have it print, and have it close without saving.

You SendKeys the string to the prompt.

 

[zandsc1]

MajP -

I used the code you wrote for running the routine from Access after opening the word document. I keep running into "Object doesn't support this property or method" on the wdapp.run line. Mine is changed to read

wdApp.Run "GetLotNumber", LotNumber

Where GetLotNumber is the name of the routine in the word doc and lotnumber is a string variable containing the data I want to pass.

Any thoughts?

 

[MajP]

Not sure.
Is the Word procedure public and in a standard module?

This works fine on my machine with a Word procedure called getLotNumber and passing in a variable. Can you post your code.

Public Sub runWord()
Dim wdApp As Word.Application
Dim wdDoc As Word.Document
Dim lotNumber As Variant
On Error Resume Next
Set wdApp = GetObject(, "Word.Application")
If Err.Number <> 0 Then 'Word isn't already running
Set wdApp = CreateObject("Word.Application")
End If
On Error GoTo 0
Set wdDoc = wdApp.Documents.Open("C:\testDoc.docm")
wdApp.Visible = True

lotNumber = 1234
wdApp.Run "getLotNumber", lotNumber
End Sub

Also test calling the procedure from inside word. Ensure that works first.

 

[zandsc1]

Fixed. Had the procedure stored in 'ThisDocument' object instead of module.

Thanks.

 

[MajP]

You can do that, but must specifically reference the ThisDocument Object.

wdApp.Run "ThisDocument.getLotNumber", lotNumber

 

[zandsc1]

Ahhh ok that makes sense.