?
Solved

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

Posted on 2006-03-22
9
Medium Priority
?
682 Views
Last Modified: 2008-02-01
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
0
Comment
Question by:MitchellVII
  • 5
  • 3
9 Comments
 
LVL 39

Expert Comment

by:stevbe
ID: 16261310
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

0
 
LVL 44

Expert Comment

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

Author Comment

by:MitchellVII
ID: 16263033
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
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 44

Accepted Solution

by:
Leigh Purvis earned 2000 total points
ID: 16263092
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 )
0
 
LVL 5

Author Comment

by:MitchellVII
ID: 16264682
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
0
 
LVL 5

Author Comment

by:MitchellVII
ID: 16268193
Thanks Purvis,

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

Author Comment

by:MitchellVII
ID: 16268199
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
0
 
LVL 44

Expert Comment

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

Author Comment

by:MitchellVII
ID: 16269555
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


0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

850 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