Results 1 to 12 of 12
  1. #1
    Lounger
    Join Date
    Mar 2002
    Location
    Sunnvyale, California, USA
    Posts
    25
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel macro opening Word document (Excel/Word 2000/Win%2

    Hi,
    I have an Excel macro that opens a Word document (error checking removed) selected from a form:

    Set olWordApp = GetObject(, "Word.Application")

    ' If Word isn't running, create a new instance
    If olWordApp Is Nothing Then
    Set olWordApp = CreateObject("Word.Application")
    End If

    With olWordApp
    .Visible = True
    .Documents.Open Filename:="xyz.xls"
    End With

    This works fine, but it shifts Word to the front, covering Excel and the form. I'd like to be able to shift back to the Excel form using VBA. The best I have been able to do is to use:

    Application.Visible = False
    Application.Visible = True

    The problem with this is that it shifts Excel to the last item in the Taskbar which I don't like. Any other way to bring Excel to the top?

    Thanks,
    Eric

  2. #2
    Super Moderator
    Join Date
    Dec 2000
    Location
    New York, NY
    Posts
    2,970
    Thanks
    3
    Thanked 29 Times in 27 Posts

    Re: Excel macro opening Word document (Excel/Word 2000/Win%2

    Application.Activate?

  3. #3
    Lounger
    Join Date
    Mar 2002
    Location
    Sunnvyale, California, USA
    Posts
    25
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel macro opening Word document (Excel/Word 2000

    Hi,
    There isn't a straight Application.Activate. There is an Application.ActivateMicrosoftApp (xlXXX) but Excel isn't one of the listed applications.

    Application.ThisWorkbook.Activate
    Application.Workbooks(1).Activate

    Don't work and

    Application.ActiveWorkbook.Activate

    causes an error message.

    Any more thoughts?

    Thanks,
    Eric

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Excel macro opening Word document (Excel/Word 2000

    Have you tried

    AppActivate "Microsoft Excel" ?

    Andrew C

  5. #5
    Lounger
    Join Date
    Mar 2002
    Location
    Sunnvyale, California, USA
    Posts
    25
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel macro opening Word document (Excel/Word 2000

    Hi,
    Getting closer! If Word wasn't running and I open it using CreateObject("Word.Application") then the focus doesn't shift back to Excel. If Word is already running then the macro form stays in front and you can see the document load in the taskbar.

    I changed the code for opening Word to use the Shell function and now everything works. Word opens on top of the macro form but then the form comes to the front.

    My only concern is that I don't necessarily know the full path to Word to use with the Shell function. I'm thinking that since everybody using this is going to have Word, not having the full path to Word won't be a problem.

    Thanks for the help,
    Eric

  6. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Excel macro opening Word document (Excel/Word 2000

    If you use the API ShellExecute and pass it a file name, it will launch the correct app provided that the extension is properly associated with th erelevant application. <pre>Private 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

    Private Sub LaunchWordDoc(strFileName As String)
    ShellExecute 0, vbNullString, strFileName, vbNullString, "C:", 1
    End Sub</pre>

    The above can be invoked with

    LaunchWordDoc(TheFileName & Path you want to work with)

    That should launch word with the appropriate file loaded or if word is running load the document.

    Andrew C

  7. #7
    Lounger
    Join Date
    Mar 2002
    Location
    Sunnvyale, California, USA
    Posts
    25
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel macro opening Word document (Excel/Word 2000

    Hi,
    The API works in terms of opening the document but it has the same problem that the focus shifts to Word for both cases: launching word and opening a document when Word is already running.

    I replaced VBA Shell with ShellExecute and it works the way I want. To summarize:

    Check if instance of Word running:

    No: Use ShellExecute API to launch Word

    Yes: Use GetObject(, "Word.Application") to load the document.

    The funny thing is that in order to get the focus to work out, I need to also use GetObject after the ShellExecute when I have to load Word, otherwise the focus stays with Word.

    Thanks,
    Eric

  8. #8
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Excel macro opening Word document (Excel/Word 2000

    Blessedly simple:

    AppActivate Excel.Windows(1).Caption

    AppActivate apparently is a native VBA command that doesn't require an object. I found it by searching in the object browser for "activate."

    <pre>Sub ReActivateExcelDemo()
    Dim wrdApp As Object
    Set wrdApp = GetObject(, "Word.Application")
    wrdApp.Activate

    AppActivate Excel.Windows(1).Caption
    End Sub</pre>


  9. #9
    Lounger
    Join Date
    Mar 2002
    Location
    Sunnvyale, California, USA
    Posts
    25
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel macro opening Word document (Excel/Word 2000

    Hi,
    Hmmm I get runtime error 5, invalid procedure call or argument.

    Eric

  10. #10
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Excel macro opening Word document (Excel/Word 2000

    Oh, this is bad. If the workbooks are not maximized (they're "windowized" or minimized) then the command needs to be:

    AppActivate "Microsoft Excel"

    because the application title bar and the workbook window title diverge. Okay... Less simple, but still not too bad:
    <pre>Sub ReActivateExcelDemo()
    Dim wrdApp As Object
    Set wrdApp = GetObject(, "Word.Application")
    wrdApp.Activate
    If Excel.Windows(1).WindowState = xlMaximized Then
    AppActivate Excel.Windows(1).Caption
    Else
    AppActivate "Microsoft Excel"
    End If
    End Sub</pre>

    Incidentally, using the first window in the Windows collection is just for demo, you could choose any window you like.

  11. #11
    Lounger
    Join Date
    Mar 2002
    Location
    Sunnvyale, California, USA
    Posts
    25
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel macro opening Word document (Excel/Word 2000

    Hi,
    I wasn't able to get completely consistent performance on different PCs with your technique and I still got error messages some of the time. What I have settled on is using AppActivate "User Form Caption Name" which at least doesn't give me an error message.

    Thanks for the help,
    Eric

  12. #12
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Excel macro opening Word document (Excel/Word 2000

    I never thought to test with a userform. That could be my problem. <img src=/S/wink.gif border=0 alt=wink width=15 height=15>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •