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

Hi,

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

Mitchell
LVL 5
MitchellVIIAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

stevbeCommented:
have you tried ShellExecute api?

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

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

SW_HIDE = 0

Leigh PurvisDatabase DeveloperCommented:
And if you're intending this with Word - why not use Automation - where you can explicitly decide it's visibility.
MitchellVIIAuthor Commented:
Purvis,

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.

Mitchell
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

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 )

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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 :)

Mitchell
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.

M
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
fSoundSearch

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

'Close Merge Document if it is open:
CLOSE_DOCUMENT:
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
                Else
                    objDoc.Close SaveChanges:=False
                End If
            Else
                objDoc.Close SaveChanges:=False
            End If
        End If
    Next
End If

'Transfer Text:
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

'MERGE TO NEW DOCUMENT:
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
        fld.Unlink
    End If
Next fld

'Remove Mailmerge.Datasource:
ActiveDocument.MailMerge.DataSource.Close

'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
ActiveDocument.Save

GoTo Exit_PerformWordMerge

Exit_PerformWordMerge:
Set objWord = Nothing
Set objDoc = Nothing
Exit Function

Err_PerformWordMerge:
If Err.Number = 429 Then
    Set objWord = CreateObject("Word.Application")
    GoTo TRANSFER_TEXT
Else
    GoTo CLOSE_DOCUMENT
End If

End Function


It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.