Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 9 of 9

Thread: Open a new instance of Excel

  1. #1
    Join Date
    11th June 2004
    Location
    Canada
    Posts
    153

    Open a new instance of Excel

    Can anyone explain to me how to open a new instance of excel using VBA code?

    What I am trying to achieve is, IF a user tries to open a new workbook in the existing instance of Excel, the application will open a new instance of Excel, and open the workbook at that location. Up to now, I have the following:

    VB:
    Public WithEvents App As Application 
     
    Private Sub App_NewWorkbook(ByVal Wb As Workbook) 
         'Code should go here
    End Sub 
    
    
    The above code is the event handler for when a new workbook is opened. I need to somehow stop the workbook from opening in my current workbook, and open it in a new instance of excel instead.

    Any suggestions will help.
    Thanks,

  2. #2
    Join Date
    11th June 2004
    Location
    Canada
    Posts
    153
    Okay, I've managed to create a new instance of Excel, and to create a workbook in that instance. I still haven't figured out how to load my custom workbook in that new instance of excel.

    I have the pathname to the workbook.

    Any help will be appreciated.


    Here is my code:

    VB:
    Dim objExcel As Excel.Application 
    Set objExcel = CreateObject("Excel.Application") 
     
    objExcel.Workbooks.Add 
     
    objExcel.Visible = True 
    
    
    Last edited by Dave Hawley; April 30th, 2012 at 20:01.

  3. #3
    Join Date
    1st April 2003
    Location
    Tennessee
    Posts
    88
    Jong,
    I can't get the event to fire to trigger the App_NewWorkbook event handler.

    What I did get to work was a bit clumsy, and would require that this code be put in the Workbook_Deactivate event handler of every wb that you want as a 'standalone' Excel instance.

    Any workbook containing this code will force a new workbook to be opened in a new Excel instance. If the user tries to create a new workbook, it will be created in a new instance as well.

    VB:
    Private Sub Workbook_Deactivate() 
         'Create variables to contain the wb name and path
        Dim Wb As String 
        Dim wbPath As String 
         'Create variable to instantiate a new Excel instance
        Dim xl As New Excel.Application 
         
         'hide stuff from user
        Application.ScreenUpdating = False 
         
         'capture the new workbook's name & path
        Wb = ActiveWorkbook.Name 
        wbPath = Workbooks(Wb).Path 
         
         'Close the new workbook before it becomes visible
        Workbooks(Wb).Close False 
         
         'Open the workbook in the new instance of Excel and set visible
         'If the workbook doesn't exist (a new workbook) then
         'create a new one in the new Excel instance
         
        On Error Resume Next 
        xl.Workbooks.Open wbPath & "\" & Wb 
        If Err.Number > 0 Then 
            Err.Clear 
            xl.Workbooks.Add 
        End If 
        xl.Visible = True 
         
         'Discard the object variable and unhide from user
        Set xl = Nothing 
        Application.ScreenUpdating = True 
         
    End Sub 
    
    
    Hope this helps
    Steve
    Steve

  4. #4
    Join Date
    11th June 2004
    Location
    Canada
    Posts
    153
    This is a great help steve


    Although, there is still a problem.
    When the macros of the new workbook get activated, they are also copied into the old workbook.

    Any suggestions as to how to get the macros to only open in their respective workbook?

  5. #5
    Join Date
    11th June 2004
    Location
    Canada
    Posts
    153
    There is another problem with that above code. Whenever you try to close the active workbook, its opens another copy of itself. When you try to close the copy, it does the same. It just keeps spawning copies.

  6. #6
    Join Date
    1st April 2003
    Location
    Tennessee
    Posts
    88
    What is your macro security setting? If it prompts you to enable/disable macros when opening a new workbook then try selecting disable. If it doesn't prompt you, then there isn't much you can do that I'm aware of other than to set your macro security to Medium.

    To eliminate the behavior described in your last post, modify the code as follows:

    change
    VB:
    Wb = ActiveWorkbook.Name 
    wbPath = Workbooks(Wb).Path 
    
    
    to
    VB:
    Wb = ActiveWorkbook.Name 
    If Wb = ThisWorkbook.Name Then Goto SubEnd 
    wbPath = Workbooks(Wb).Path 
    
    
    and add a SubEnd: label just before the Set xl=Nothing statement
    Last edited by Dave Hawley; April 30th, 2012 at 20:00.
    Steve

  7. #7
    Join Date
    11th June 2004
    Location
    Canada
    Posts
    153
    The problem for macros is related to the users who will be using my software.

    Let's face it, not all users are good computer users. Now when the user wants to open a new workbook, he is going to click "File>OPEN" and then it will prompt to Enable macros, which they will probably do, and then all hell breaks loose

    So, there is my problem.

    I've also thought about not allowing a new workbook to open, but that would just cause total chaos.

    I'm trying to come up with a solution, if anyone else has other suggestions, im open ears.

  8. #8
    Join Date
    30th April 2012
    Posts
    1

    Re: Open a new instance of Excel

    Hi People,

    Sorry for the almighty thread revival lol. I was trying to find a solution to the above problem and through searching I came across this very thread. Although the answers given didn't help me directly the problem was the closest to mine so I thought I would come back with the solution I have found.

    I guess in a way it's a bit of a hack/cheat but most of the best solutions are.

    I'll start off with the problem I had and the reason I needed to open any other files in a new instance of excel. I have a small excel/vba program that I use in work. It has a userform front end and the actual excel application remains hidden at all times (application.visible = false). If I try to open an excel file by double clicking on it, it won't load as the form is shown modally, if I were to not show it modally and I opened the file I still wouldn't be able to access it until the userform was unloaded/closed so this wasn't an ideal solution either.

    This is where the searching began and I came across the above suggestions. In the end up the only thing I could get to work was by turning off DDE. The trouble is that the machine I use needs administrative access to change the default program arguments (If you search DDE in vista/windows 7 you will know what I mean). If I turned DDE off via excel itself it would do almost what I asked, with the exception of not being able to open the file (due to the open arguments being wrong, the ones that I cannot change). Through lots of messing around I found that if I disabled DDE then opened my program this would allow me to double click the file to open up a new instance of excel (it would fail to open the file), however, if I then enabled DDE then opened the said file it would open fine. The reason for this is that the program is loaded in one instance of excel with DDE turned off and the other instance of excel, and any others opened up, will have it turned on. The DDE option is only saved when excel is closed. Soooo.... the solution.

    Turn DDE on via excel, close the excel program to save the setting. In the excel program you have with the userforms simply have the following code when the workbook is opened "application.IgnoreRemoteRequests = true". This will turn DDE off FOR THIS INSTANCE OF EXCEL ONLY, this is because the settings won't be saved for other instances until this instance is closed. Simply add the following code "application.IgnoreRemoteRequests = false" to any code that closes the application (ie anywhere in your program before "Application.quit" and this will reset the DDE back to it's current state so that it saves as normal.

    This allows the following to happen...

    Before opening anything DDE is on
    Open ExcelProgram1
    DDE for ExcelProgram1 is turned off, DDE remains on for any new instances of excel (as it won't save till the excel instance with changes is closed)
    Double click on any file and it will open in a new instance of excel with DDE remaining on. This means that any other files opened will open in this instance, as long as the code doesn't change.
    Close ExcelProgram1
    Before closing DDE is turned back on before the "Application.quit" code, meaning that there are no changes to the default DDE status.

    One small thing to add. When I open up my program I have the shortcut set to open a new instance of excel before opening the program. This is to make sure that my program doesn't interfere with any open instances of Excel. It would be a good idea to do the same, just for the program you need to keep open, if you want to use the above method.

    Hope someone can find some use of this even if it did take almost 8 years lol.

    Scott

    Excel Video Tutorials / Excel Dashboards Reports


  9. #9
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,708

    Re: Open a new instance of Excel

    Hi Scott

    Very thoughtful of you, thanks....Oh and welcome to ozgrid

Thread Information

Users Browsing this Thread

There are currently 2 users browsing this thread. (0 members and 2 guests)

Possible Answers

  1. On Open, New Instance
    By Penfold in forum EXCEL HELP
    Replies: 2
    Last Post: January 9th, 2008, 05:48
  2. Replies: 21
    Last Post: September 13th, 2007, 04:27
  3. Force New Instance On Open
    By ecawilkinson in forum EXCEL HELP
    Replies: 1
    Last Post: June 29th, 2007, 19:01
  4. Force Workbook Open New Instance
    By postman2000 in forum EXCEL HELP
    Replies: 3
    Last Post: December 14th, 2006, 23:13
  5. Open new instance of application
    By breakingstein in forum EXCEL HELP
    Replies: 2
    Last Post: July 6th, 2005, 17:40

Bookmarks

Posting Permissions

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