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

    workbook.open alternative (2000 sp3)

    Is there a way to open a workbook in vba code, similar to using WorkBook.Open but have it open a new instance of Excel instead of opening it in the same instance of Excel?
    I don't want to use automation because of the whole "client / Server, I control you but I can't do anything until you're finished" issue.
    If you've read this far, I'll give a bit more detail. Part of this Excel app I'm working on presents a UserForm that allows a user to attach files and then open or delete them. By attach, I mean that I copy the files into a folder that is part of the app. This allows the user to gather together files from disparate sources into one folder on their PC. The UserFrom includes functions to attach, delete (from their folder) and open the files. I use
    ActiveWorkbook.FollowHyperlink Address:=strFullFileNm, NewWindow:=True
    to open most of the files which works great, but for Excel files it works the same as WorkBook.Open and that causes several problems:
    1) The workbook is opened in the same instance of Excel but the UserForm is still displayed on top of the newly opened workbook.
    2) The workbook is opened as a window instead of maximized within the Excel window, which of course, does the same to my app. So, when the newly opened workbook is closed (after the user closes the UserForm) my app is left in a window too instead of maximized.

    The different handling of the files, and the need to deal with the userform in the middle of the operation is confusing, especially for a user.
    So far these are the only three ways I know of to open a workbook file. Maybe I'm missing some options with them or maybe there's another way, I hope.

    Any ideas would be very much appreciated.
    Thanks

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

    Re: workbook.open alternative (2000 sp3)

    Please keep in mind that starting multiple instances of Excel is a significant drain of resources.
    If the user has workbooks being opened automatically, such as the macro workbook Personal.xls, a warning will be displayed that those files are already in use.

    You could use CreateObject("Excel.Application") to start a new instance of Excel but you didn't want to use Automation (I don't understand your remark about Automation, by the way.) Another method is using Shell:

    <code>Shell """C:Program FilesMicrosoft OfficeOfficeExcel.exe"" ""F:ExcelTest.xls""", vbMaximizedFocus</code>

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

    Re: workbook.open alternative (2000 sp3)

    Hi Hans,

    You can prevent the personal.xls problem by adding the automation switch:

    Shell "crogram filesmicrosoft officeofficeExcel.exe /Automation c:dataTest.xls", vbMaximizedFocus
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

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

    Re: workbook.open alternative (2000 sp3)

    Thanks!

  5. #5
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: workbook.open alternative (2000 sp3)

    FWIW: if there's interest in conserving resources, the switch <code>vbNormalFocus</code> may be more economical than <code>vbMaximizedFocus</code>. HTH
    Gre

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

    Re: workbook.open alternative (2000 sp3)

    Thanks for the tip. Does specifying vbMaximizedFocus impose a significant burden on resources?

  7. #7
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: workbook.open alternative (2000 sp3)

    The observation is based on (long past) experience of Windows 3.1. Maxmised windows (as well as non-default colour schemes) would cause screens to "white out" during processing. Removing "maximising" has improved performance on machines/configurations I have worked with subsequently. If we are talking about WinXP machines with <512Mb RAM, it could still be a factor. It is, however, something I have never seen measurements for.

    FWIW I am inclined to infar that loading an app sends info as to what the Normal window size is. If Maximised is selected then (still) the OS code checks continuously as to how much the window should be expanded from Normal.

    HTH
    Gre

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

    Re: workbook.open alternative (2000 sp3)

    Thank you Hans, and everyone else! This is definitely the information I was looking for.

    By the way, the reason for my other comment was because of some problems I ran into working with automating Excel from a Word document. I assumed I'd have the same problems in Excel but that may not have been a good assumption. Another fear on my part was not knowing what would happen if the user tried to open more than one workbook at a time. The macro would have two threads (each with an automation session) active at the same time since the macro wouldn't end until the user closed the opened workbook. I wasn't sure I'd be able to deal with that! Your way looks like exactly the right way to go for me. As far as resources, I'll warn the users about it, thanks for the warning.
    Again, thank you all!!!!!

Posting Permissions

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