Using Shell with vbHide isn't hiding Application for some reason...


I am opening a document in Word from Access using the "Shell" Method.  Before anyone suggests that I just use GetObject() instead, in this case, that won't work as my Document has a Form Button on it and for some reason GetObject() causes a Missing Object Error if you have a Form Button on your document (yeah, weird I know).

Anyway, Shell seems to get around this.

Ok,  I am using the vbHide Constant with Shell because I don't want the Document to Maximize while my code is running some processes on it.  However, even though I have specified "vbHide" the Document is still popping up on my screen.

Anyone know what I am doing wrong?

Here is the code:

Shell "winword C:\ExecutiveDecision\" & vDocType & "\Templates\" & vDocType & vDocTypeSub & ".doc", vbHide

Who is Participating?
Leigh PurvisDatabase DeveloperCommented:
Well, I know for a fact you can automate Word - have seen several questions from you on mailmerges.

Dim objWord as Object

Set objWord = CreateObject("Word.Application")
objWord.Visible = False  '<-- this should be redundant - the automation object should be invisible anyway unless specified
.Documents.Open "C:\ExecutiveDecision\" & vDocType & "\Templates\" & vDocType & vDocTypeSub & ".doc"

Then whatever you want to do with it.
(Can't actually imagine what that would be with an invisible application mind you... :-S )
have you tried ShellExecute api?

you will need to add a constant for displaying the window in Hide mode


Leigh PurvisDatabase DeveloperCommented:
And if you're intending this with Word - why not use Automation - where you can explicitly decide it's visibility.
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

MitchellVIIAuthor Commented:

There you go again giving me credit for more intelligence than I possess :)

Can you give me an example of exactly what you mean?  Remember, I'm a headhunter that tries his best to write some code for his business, not a developer, lol.

MitchellVIIAuthor Commented:
I'm just doing some merging and field unlinking, etc and just prefer not to see 3 forms pop open and close until the final form appears.

I'm still wondering why vbHide doesnt work?

I'll try ur code out :)

MitchellVIIAuthor Commented:
Thanks Purvis,

You are on my short-list of Word Heroes :)
MitchellVIIAuthor Commented:
In closing, I wonder why using GetObject() to activate the Word Document causes an "Object Not Found" error only if the document has form buttons on it?

Just seems odd.

Leigh PurvisDatabase DeveloperCommented:
CreateObject (as used in the example) should just launch Word.
If you then subsequently open the document - you *might* have problems associated with it.  But I wouldn't have thought so.

GetObject direct to the document might be more likely to fail - hard to say.

The problem with the general scenario you have is that if you encounter any errors - it needs to be well error handled (i.e. make sure you destroy the Word instance).
Otherwise you'll end up with dozens of invisible Word application instances running.
Only readily viewable in Task Manager.
MitchellVIIAuthor Commented:
Here's the final code for your entertainment.  It does the following:

1. Makes sure no Merge Documents with my target .txt file as a .datasource are open.  If they are, it prompts me to Save them and then closes them.
2. It opens Word invisibly (thanks to you) and opens my MailMerge Document which contains MERGEFIELDS and FORMTEXT fields.
3. Rather than doing a Mail Merge, which would destroy my FORMTEXT fields (bad M$!), I do a selective unlinking of just the MERGEFIELDS, remove the .datasource and do a .SaveAs, then closing the original Merge Document, unchanged.
4. Last step, I am left with my groovy form that has been saved and protected with all MERGEFIELDS unlinked and FORMTEXT fields intact.  My new Form pops up on my screen ready to be reviewed and then placed online for my user to complete.

The only thing missing is really good error handling like you said that keeps me from building up instances of Word everywhere - I'm not sure how to do that exactly :)

Oh well, here it is: (I'm actually proud of this because it gets around the problems with mailmerge and form fields)

Public Function PerformWordMerge(vForm As Form, vDocType As String, vDocTypeSub As String)
On Error GoTo Err_PerformWordMerge

Dim objWord, objDoc As Object
Dim rng As Word.Range
Dim fld As Word.Field

'Play Sound

'Set Word Application Object:
Set objWord = GetObject(, "Word.Application")

'Close Merge Document if it is open:
If Not (objWord Is Nothing) Then
    For Each objDoc In objWord.Application.Documents
        If objDoc.MailMerge.DataSource.Name = "C:\ExecutiveDecision\Export\" & vDocType & vDocTypeSub & ".txt" Then
            objDoc.Application.Visible = True
            objDoc.Application.WindowState = wdWindowStateMaximize
            If Not objDoc.Saved Then
                If MsgBox("Do you wish to save " & objDoc.Name & " before closing?", vbYesNo, "Save Document?") = vbYes Then
                    objDoc.Close SaveChanges:=True
                    objDoc.Close SaveChanges:=False
                End If
                objDoc.Close SaveChanges:=False
            End If
        End If
End If

'Transfer Text:
With vForm
    DoCmd.TransferText acExportDelim, , "LUMerge" & vDocType & vDocTypeSub, "C:\ExecutiveDecision\Export\" & vDocType & vDocTypeSub & ".txt", -1
    ![ID].SetFocus 'This is something I do for housekeeping and isn't really necessary to the code...
End With

objWord.Visible = False
objWord.Documents.Open "C:\ExecutiveDecision\" & vDocType & "\Templates\" & vDocType & vDocTypeSub & ".doc"

'Unlink Merge Fields:
For Each fld In ActiveDocument.Fields
    If fld.Type = wdFieldMergeField Then
    End If
Next fld

'Remove Mailmerge.Datasource:

'SaveAs to New Document:
ActiveDocument.SaveAs Filename:="C:\ExecutiveDecision\" & vDocType & "\OnHand\" & DLookup("Filename" & vDocType, "SummaryContact", "[ID]=" & vForm![RecipientID]) & ".doc"

ActiveDocument.Saved = True
ActiveDocument.Close SaveChanges:=wdDoNotSaveChanges

'Open New Document:
fSoundPopUp 'Just plays a sound when the final form pops up...
objWord.Visible = True
objWord.Documents.Open "C:\ExecutiveDecision\" & vDocType & "\OnHand\" & DLookup("Filename" & vDocType, "SummaryContact", "[ID]=" & vForm![RecipientID]) & ".doc"""
ActiveDocument.Protect Type:=wdAllowOnlyFormFields, NoReset:=True

GoTo Exit_PerformWordMerge

Set objWord = Nothing
Set objDoc = Nothing
Exit Function

If Err.Number = 429 Then
    Set objWord = CreateObject("Word.Application")
End If

End Function

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.