ShellExecute question (1 Viewer)

MrMitch · Nov 15, 2007

Hi all,

I'm finally down to my last hangup on this database that I'm working on. I just wanted to thank everyone here that helps out, the discussions here have helped me more then I could have imagined as I knew absolutley nothing about access or vba.

Ok, now on to question at hand. Right now I have a module that basically opens a MS Word doc, and prints it from a button on a form. However I would like it to drop something from a textbox called "txtWorkOrderID" on that form at a bookmark I have set on the Word doc. The bookmark is called "WO". Here is the code I am working with now.

Option Compare Database

Option Explicit

Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" (ByVal hWnd As Long, ByVal lpOperation As String, ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long

Public Function Test()
On Error GoTo Err_Test

'Open file
ShellExecute 0, "Open", "file:///G:\Bulletin Board\UP Billing Flags\UP 1.2 - Health and Safety Plan.doc", "", "", 1

'Print file
ShellExecute 0, "Print", "file:///G:\Bulletin Board\UP Billing Flags\UP 1.2 - Health and Safety Plan.doc", "", "", 0

Exit_Test:
Exit Function

Err_Test:
MsgBox Err.Number & " - " & Err.Description
Resume Exit_Test

End Function

MrMitch · Nov 16, 2007

Just thought I would bump this up and see if anyone could lend a hand. Thanks.

GolferGuy · Nov 16, 2007

You need someway to comunicate with Word from Access or someway to pass a parameter to Word. It looks like the string you are using to open and then print this Word document has a parameter in it called lpParameters. If that is the Command parameter that Word uses, then in VBA within Word you can retrieve that using the Command() function. At that point, have your VBA in Word find the "WO" bookmark and insert the string passed through the parameter there.

MrMitch · Nov 20, 2007

Ugh..that seems pretty tricky for me having no real knowledge of VBA, other that what I have borrowed from here. =)

Is there a simpler way of doing what I explained?

GolferGuy · Nov 20, 2007

Sorry Mitch, but you are right, that is "a bit tricky." Actually, it's more than just a bit tricky. The first time I ever tried something like this it took at least a couple of days. There are quite a few things to learn, understand, and then a lot of trial and error. Now it would only take a few hours, like two to four hours. My suggestion is to weigh the worth of having this be automatic as you are requesting, vs. the cost of getting a consultant to code it for you. It's beyond the time I have to give.

MrMitch · Nov 20, 2007

I hear you. Thanks for the pointers. I'll see what I can do!

boblarson · Nov 20, 2007

You would need this (set a reference to Microsoft Word first though):

Code:

Dim appWd As Word.Application
Dim wdDoc As Word.Document

Set appWd = New Word.Application
appWd.Visible = True
Set wdDoc = appWd.Documents.Open("G:\Bulletin Board\UP Billing Flags\UP 1.2 - Health and Safety Plan.doc")
wdDoc.Bookmarks("WO").Select
wdDoc.ActiveWindow.Selection = Me.txtWorkOrderID
wdDoc.Save
wdDoc.Close
appWd.Quit
Set wdDoc = Nothing
Set appWd = Nothing

MrMitch · Nov 20, 2007

Thanks a ton Bob. Only thing I'm getting when I try to run the code is "invalid use of Me keyword". Any idea there?

boblarson · Nov 20, 2007

Where did you put this code? I assumed it would be on the form that had txtWorkOrderID on it. If it isn't then you would need to fully qualify it:

Forms!YourFormNameHere.txtWorkOrderID

MrMitch · Nov 21, 2007

Ok, I'm using this code, so far it works great. I added something to print as well, but I don't want it to save any changes and somewhere it is telling it to do that.

Dim appWd As Word.Application
Dim wdDoc As Word.Document

Set appWd = New Word.Application
appWd.Visible = True
Set wdDoc = appWd.Documents.Open("G:\Bulletin Board\UP Billing Flags\UP 1.2 - Health and Safety Plan.doc")
wdDoc.Bookmarks("WO").Select
wdDoc.ActiveWindow.Selection = Forms!frmGWO1.txtWorkOrderID

ShellExecute 0, "Print", "file:///G:\Bulletin Board\UP Billing Flags\UP 1.2 - Health and Safety Plan.doc", "", "", 0

appWd.Quit

Set wdDoc = Nothing
Set appWd = Nothing

End Function

boblarson · Nov 21, 2007

Put this before the appWd.Quit:

wdDoc.Close False

that should close it without saving the changes.

MrMitch · Nov 21, 2007

Is there something here:

ShellExecute 0, "Print", "file:///G:\Bulletin Board\UP Billing Flags\UP 1.2 - Health and Safety Plan.doc", "", "", 0

That is telling it to save and close?

I got a "The object invoked has disconnected from its clients" Automation error at

wdDoc.Close False

MrMitch · Nov 21, 2007

Sweet, I fixed it by just using this instead.

Function UP2()

Dim appWd As Word.Application
Dim wdDoc As Word.Document

Set appWd = New Word.Application
appWd.Visible = True
Set wdDoc = appWd.Documents.Open("G:\Bulletin Board\UP Billing Flags\UP 1.2 - Health and Safety Plan.doc")
wdDoc.Bookmarks("WO").Select
wdDoc.ActiveWindow.Selection = Forms!frmGWO1.txtWorkOrderID
wdDoc.PrintOut
wdDoc.Close False
appWd.Quit

End Function

Thanks for all your help again Bob. Much appricated

boblarson · Nov 21, 2007

Glad I could help. I would have stepped in sooner but was very busy at work when you posted the original post.

shwetha · Dec 18, 2007

can u give me the difference between Shell and ShellExecute...

Is it correct...that In shell command have to specify .exe file first and next the file which is to be opened or executed

And in ShellExecute command,it ditermines first the file extension and then opens that file calling that particular application

ShellExecute question (1 Viewer)

MrMitch

Registered User.

MrMitch

Registered User.

GolferGuy

Registered User.

MrMitch

Registered User.

GolferGuy

Registered User.

MrMitch

Registered User.

boblarson

Smeghead

MrMitch

Registered User.

boblarson

Smeghead

MrMitch

Registered User.

boblarson

Smeghead

MrMitch

Registered User.

MrMitch

Registered User.

boblarson

Smeghead

shwetha

New member

Similar threads

Users who are viewing this thread