Results 1 to 14 of 14
  1. #1
    3 Star Lounger
    Join Date
    Oct 2004
    Location
    USA
    Posts
    223
    Thanks
    1
    Thanked 0 Times in 0 Posts

    GetObject with multiple instances (Office 2000 sp3)

    I'm not sure this is the right forum for this question so please let me know if another forum would be better. I searched the forums but couldn't find an answer to my questions.
    I'm trying to automate Word from Excel. I have a workbook with code that opens the Word doc. I want the Word doc to get information from the Excel workbook so I use GetObject to open the Excel from Word so I can work with the activeworkbook. All works well unless there is more than one instance of Excel and the workbook that invokes Word is not in the first instance of Excel created. I've added code to check the name of the active workbook and it shows that I am always opening the active workbook of the first Excel instance created. Multiple GetObjects seem to have no effect.
    Is there a way to open the other instances of Excel? I can't find any reference about GetObject that provides for this. Is there an Excel.Applications collection?
    Also, is it possible to pass the workbook path and file name or other information to the Word doc when I open it from Excel?
    Any information you can provide will be most appreciated.
    Thanks

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: GetObject with multiple instances (Office 2000 sp3)

    Why are there multiple instances of Excel running? Perhaps you should first investigate that, and try to avoid it.

  3. #3
    3 Star Lounger
    Join Date
    Oct 2004
    Location
    USA
    Posts
    223
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: GetObject with multiple instances (Office 2000 sp3)

    The work flow of the user requires them to open different workbooks throughout the day. Sometimes they do that from File Open of the current instance, or double click the file name in Windows Explorer which also uses the current instance. At other times they click the Excel icon they have in their task bar which starts a new instance of Excel. I don't know why, but they do. I've tried to explain about "Window, Arrange" anticipating that they may feel it is necessary to view more than one workbook at a time but that hasn't solved the problem. I can control my work flow but not the users'! Both Word and Excel allow multiple instances but I don't really know of any advantage this provides.
    Thanks

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: GetObject with multiple instances (Office 2000 sp3)

    Sounds as if code in Excel is opening a file in Word, which in turn has code that needs to go babk to Excel?
    If so, why not put all code in Excel and manage things from there?
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  5. #5
    3 Star Lounger
    Join Date
    Oct 2004
    Location
    USA
    Posts
    223
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: GetObject with multiple instances (Office 2000 sp3)

    There is significant amounts of data in the workbook that I need to access from the Word doc after it is opened. I don't know of a way to pass that information to the Word doc so "looking back" at the workbook seemed like the best solution. I am using the clipboard to pass the name and path of the workbook so I can check if the Word doc gains access to the right one but the clipboard isn't going to cut it for all the other data I need.
    Thanks

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: GetObject with multiple instances (Office 2000 sp3)

    You initiate the whole thing from Excel, if I understand your description correctly. You should be able, as Jan Karel Pieterse suggests, to fill the Word document from the Excel workbook that opened it, instead of trying to reach back from Word to Excel.

  7. #7
    3 Star Lounger
    Join Date
    Oct 2004
    Location
    USA
    Posts
    223
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: GetObject with multiple instances (Office 2000 sp3)

    I was starting to think about that after I replied to Jan Karel Pieterse's post. It is definitely a good suggestion. Now that you are suggesting the same thing I'm going to spend my time moving the code and figuring out what has to change.
    Thank you both!

  8. #8
    3 Star Lounger
    Join Date
    Oct 2004
    Location
    USA
    Posts
    223
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: GetObject with multiple instances (Office 2000 sp3)

    In the Word document I have a macro that I will need to run from Excel. Is there a way to do that or do I need to duplicate the code in the Excel macro?
    Thanks

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: GetObject with multiple instances (Office 2000 sp3)

    Since you start Word from Excel, I assume that you create a Word.Application object, say objWord. You can run a macro in the open document as follows:

    objWord.Run "NameOfTheMacro"

  10. #10
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: GetObject with multiple instances (Office 2000 sp3)

    You can do either.

    If in Excel, you need to precede anything "Wordish" with the Word Application object:

    Dim oWdApp as Word.Application 'Assumes you have set a reference to Word in Tools refs)
    Set oWdApp=New Word.Application
    With oWdApp
    .Documents.Open "FIleName"
    'whatever your macro did goes here, precede every word related object with a dot
    .ActiveDocument.Close SaveChanges:=True
    .Quit
    End With
    Set oWdApp=Nothing
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  11. #11
    3 Star Lounger
    Join Date
    Oct 2004
    Location
    USA
    Posts
    223
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: GetObject with multiple instances (Office 2000 sp3)

    Whew, thanks! That saves a lot of work! I really appreciated the help.

  12. #12
    3 Star Lounger
    Join Date
    Oct 2004
    Location
    USA
    Posts
    223
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: GetObject with multiple instances (Office 2000 sp3)

    I finally got a chance to finish the rest of the code. It works like a charm! Thanks for your help, I really appreciate it.
    Thanks

  13. #13
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: GetObject with multiple instances (Office 2000 sp3)

    If you did all that, then we really need to change your username from Bumbler to Buster. <img src=/S/salute.gif border=0 alt=salute width=15 height=20>
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  14. #14
    3 Star Lounger
    Join Date
    Oct 2004
    Location
    USA
    Posts
    223
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: GetObject with multiple instances (Office 2000 sp3)

    You just made my week! Thanks for the compliment. The hardest part was figuring out how to wait for, and check, that the Word doc closed so I could restore the Excel window to the desktop and close the Word app if I was the one to open it. Fun stuff.

    Thanks

Posting Permissions

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