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

Posted on 2006-03-22
Last Modified: 2008-02-01

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

Question by:MitchellVII
    LVL 39

    Expert Comment

    have you tried ShellExecute api?

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

    SW_HIDE = 0

    LVL 44

    Expert Comment

    by:Leigh Purvis
    And if you're intending this with Word - why not use Automation - where you can explicitly decide it's visibility.
    LVL 5

    Author Comment


    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.

    LVL 44

    Accepted Solution

    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 )
    LVL 5

    Author Comment

    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 :)

    LVL 5

    Author Comment

    Thanks Purvis,

    You are on my short-list of Word Heroes :)
    LVL 5

    Author Comment

    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.

    LVL 44

    Expert Comment

    by:Leigh Purvis
    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.
    LVL 5

    Author Comment

    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


    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    Join & Write a Comment

    Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
    Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
    What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
    In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now