Open a New Instance of Excel

R

ryguy7272

I am trying to open a new instance of Excel using VBA. Here is my
Workbook_Open():
Private Sub Workbook_Open()

'begin new instance
Dim blnIsOpen As Boolean
Dim blnReadOnly As Boolean
Dim blnOpenRef As Boolean
Dim wbRef As Workbook
Dim xlApp As Excel.Application
Dim wsWorking As Worksheet
Dim strPath As String
Dim sWorkbookToOpen As String
Dim sWorkbook As String

strPath = ThisWorkbook.Path & "/"
sWorkbook = "MainFile.xls"
sWorkbookToOpen = strPath & sWorkbook

blnIsOpen = True
On Error Resume Next
Set wbRef = Workbooks(sWorkbook)
On Error GoTo 0

If wbRef Is Nothing Then
Set xlApp = CreateObject("Excel.Application")
Set wbRef = xlApp.Workbooks.Open(sWorkbookToOpen, ,
blnReadOnly)
xlApp.Visible = True
blnIsOpen = False
End If
If blnOpenRef = True Then
wbRef.Activate
Else
' wsWorking.Activate
End If

'end new instance...

The code looks good, but it does NOT work. Right now, I’m popping
open a file named MainFile.xls and if there are no workbooks open,
when the code runs (inside of MainFile.xls), I can open a new instance
of Excel and do what I need to do in the new instance, and then use
Application.Quit for my MainFile.xls, and completely close it. If I
have an Excel file open (let’s call it File_A) and run my VBA,
MainFile.xls opens in the same instance of the File_A. I can’t use
Application.Quit to close MainFile.xls because that also closes
File_A.

Any ideas on how I can get the Workbook_Open() event to open an new
instance of Excel and then load my MainFile.xls into that new
instance?

Thanks so much!!
 
R

ryguy7272

I am trying to open a new instance of Excel using VBA.  Here is my
Workbook_Open():
Private Sub Workbook_Open()

'begin new instance
    Dim blnIsOpen As Boolean
    Dim blnReadOnly As Boolean
    Dim blnOpenRef As Boolean
    Dim wbRef As Workbook
    Dim xlApp As Excel.Application
    Dim wsWorking As Worksheet
    Dim strPath As String
    Dim sWorkbookToOpen As String
    Dim sWorkbook As String

    strPath = ThisWorkbook.Path & "/"
    sWorkbook = "MainFile.xls"
    sWorkbookToOpen = strPath & sWorkbook

    blnIsOpen = True
    On Error Resume Next
    Set wbRef = Workbooks(sWorkbook)
    On Error GoTo 0

    If wbRef Is Nothing Then
        Set xlApp = CreateObject("Excel.Application")
        Set wbRef = xlApp.Workbooks.Open(sWorkbookToOpen, ,
blnReadOnly)
        xlApp.Visible = True
        blnIsOpen = False
    End If
    If blnOpenRef = True Then
        wbRef.Activate
    Else
         '        wsWorking.Activate
    End If

'end new instance...

The code looks good, but it does NOT work.  Right now, I’m popping
open a file named MainFile.xls and if there are no workbooks open,
when the code runs (inside of MainFile.xls), I can open a new instance
of Excel and do what I need to do in the new instance, and then use
Application.Quit for my MainFile.xls, and completely close it.  If I
have an Excel file open (let’s call it File_A) and run my VBA,
MainFile.xls opens in the same instance of the File_A.  I can’t use
Application.Quit to close MainFile.xls because that also closes
File_A.

Any ideas on how I can get the Workbook_Open() event to open an new
instance of Excel and then load my MainFile.xls into that new
instance?

Thanks so much!!
Click to expand...


I made a few changes this AM; now I have this in a file called
‘MainFile.xls’

Private Sub Workbook_Open()

'begin new instance
Dim blnIsOpen As Boolean
Dim blnReadOnly As Boolean
Dim blnOpenRef As Boolean
Dim wbRef As Workbook
Dim xlApp As Excel.Application
Dim wsWorking As Worksheet
Dim strPath As String
Dim sWorkbookToOpen As String
Dim sWorkbook As String

strPath = ThisWorkbook.Path & "/"
sWorkbook = "MainFile.xls"
sWorkbookToOpen = strPath & sWorkbook

blnIsOpen = True
On Error Resume Next
Set wbRef = Workbooks("MainFile.xls")
On Error GoTo 0

If wbRef Is Nothing Then
Set xlApp = CreateObject("Excel.Application")
Set wbRef = xlApp.Workbooks.Open(sWorkbookToOpen, ,
blnReadOnly)
xlApp.Visible = True
blnIsOpen = False
End If
If blnOpenRef = True Then
wbRef.Activate
Else
' wsWorking.Activate
End If
End If
'end new instance...


username = getusercompletename
'usersoeid = ReturnUserName
Call GetQueryString

If wrkclose = True Then
ThisWorkbook.EnableAutoRecover = False
ThisWorkbook.Saved = True
ThisWorkbook.Close

End If
End Sub

‘Function GetQueryString’ is fired when a variable is passed to the
function from a URL (I’m running SharePoint too). All of the rest of
the code WORKS FINE, when there are NO files open, simply because I
use VBA to open a specific file (whatever variable is passed from the
URL).

VBA fires, opens a new instance of Excel, and then ‘MainFile.xls’,
which has the code to open a new Excel file, closes itself with this:
If ThisWorkbook.name = "MainFile.xls" Then
If Application.Workbooks.Count = 1 Then
Application.Quit
End If
End If

However, the code DOES NOT WORK FINE then there is ONE file open,
simply because ‘MainFile.xls’ opens in the SAME INSTANCE OF the Excel
where the new file is opened. When that happens, Application.Quit
totally kills everything, but I really want to keep the file that was
just opened, open. Does that make sense? With NO Excel files open,
my code runs fine. With ONE file open, my code DOES NOT WORK.

Do you have any recommendations or suggestions as to how to get
‘MainFile.xls’ to open within a NEW INSTANCE of Excel? I really think
that will fix all my problems.

Thanks so much!!
 
R

ryguy7272

I made a few changes this AM; now I have this in a file called
‘MainFile.xls’

Private Sub Workbook_Open()

'begin new instance
    Dim blnIsOpen As Boolean
    Dim blnReadOnly As Boolean
    Dim blnOpenRef As Boolean
    Dim wbRef As Workbook
    Dim xlApp As Excel.Application
    Dim wsWorking As Worksheet
    Dim strPath As String
    Dim sWorkbookToOpen As String
    Dim sWorkbook As String

    strPath = ThisWorkbook.Path & "/"
    sWorkbook = "MainFile.xls"
    sWorkbookToOpen = strPath & sWorkbook

    blnIsOpen = True
    On Error Resume Next
    Set wbRef = Workbooks("MainFile.xls")
    On Error GoTo 0

    If wbRef Is Nothing Then
        Set xlApp = CreateObject("Excel.Application")
        Set wbRef = xlApp.Workbooks.Open(sWorkbookToOpen, ,
blnReadOnly)
        xlApp.Visible = True
        blnIsOpen = False
    End If
    If blnOpenRef = True Then
        wbRef.Activate
    Else
         '        wsWorking.Activate
    End If
End If
'end new instance...

username = getusercompletename
'usersoeid = ReturnUserName
Call GetQueryString

If wrkclose = True Then
   ThisWorkbook.EnableAutoRecover = False
   ThisWorkbook.Saved = True
   ThisWorkbook.Close

End If
End Sub

‘Function GetQueryString’ is fired when a variable is passed to the
function from a URL (I’m running SharePoint too).  All of the rest of
the code WORKS FINE, when there are NO files open, simply because I
use VBA to open a specific file (whatever variable is passed from the
URL).

VBA fires, opens a new instance of Excel, and then ‘MainFile.xls’,
which has the code to open a new Excel file, closes itself with this:
If ThisWorkbook.name = "MainFile.xls" Then
If Application.Workbooks.Count = 1 Then
        Application.Quit
End If
End If

However, the code DOES NOT WORK FINE then there is ONE file open,
simply because ‘MainFile.xls’ opens in the SAME INSTANCE OF the Excel
where the new file is opened.  When that happens, Application.Quit
totally kills everything, but I really want to keep the file that was
just opened, open.  Does that make sense?  With NO Excel files open,
my code runs fine.  With ONE file open, my code DOES NOT WORK.

Do you have any recommendations or suggestions as to how to get
‘MainFile.xls’ to open within a NEW INSTANCE of Excel?  I really think
that will fix all my problems.

Thanks so much!!
Click to expand...


Got it working!! I had some help from a colleague in my office.
Thanks Mandeep!!
 
R

ryguy7272

I made a few changes this AM; now I have this in a file called
‘MainFile.xls’

Private Sub Workbook_Open()

'begin new instance
    Dim blnIsOpen As Boolean
    Dim blnReadOnly As Boolean
    Dim blnOpenRef As Boolean
    Dim wbRef As Workbook
    Dim xlApp As Excel.Application
    Dim wsWorking As Worksheet
    Dim strPath As String
    Dim sWorkbookToOpen As String
    Dim sWorkbook As String

    strPath = ThisWorkbook.Path & "/"
    sWorkbook = "MainFile.xls"
    sWorkbookToOpen = strPath & sWorkbook

    blnIsOpen = True
    On Error Resume Next
    Set wbRef = Workbooks("MainFile.xls")
    On Error GoTo 0

    If wbRef Is Nothing Then
        Set xlApp = CreateObject("Excel.Application")
        Set wbRef = xlApp.Workbooks.Open(sWorkbookToOpen, ,
blnReadOnly)
        xlApp.Visible = True
        blnIsOpen = False
    End If
    If blnOpenRef = True Then
        wbRef.Activate
    Else
         '        wsWorking.Activate
    End If
End If
'end new instance...

username = getusercompletename
'usersoeid = ReturnUserName
Call GetQueryString

If wrkclose = True Then
   ThisWorkbook.EnableAutoRecover = False
   ThisWorkbook.Saved = True
   ThisWorkbook.Close

End If
End Sub

‘Function GetQueryString’ is fired when a variable is passed to the
function from a URL (I’m running SharePoint too).  All of the rest of
the code WORKS FINE, when there are NO files open, simply because I
use VBA to open a specific file (whatever variable is passed from the
URL).

VBA fires, opens a new instance of Excel, and then ‘MainFile.xls’,
which has the code to open a new Excel file, closes itself with this:
If ThisWorkbook.name = "MainFile.xls" Then
If Application.Workbooks.Count = 1 Then
        Application.Quit
End If
End If

However, the code DOES NOT WORK FINE then there is ONE file open,
simply because ‘MainFile.xls’ opens in the SAME INSTANCE OF the Excel
where the new file is opened.  When that happens, Application.Quit
totally kills everything, but I really want to keep the file that was
just opened, open.  Does that make sense?  With NO Excel files open,
my code runs fine.  With ONE file open, my code DOES NOT WORK.

Do you have any recommendations or suggestions as to how to get
‘MainFile.xls’ to open within a NEW INSTANCE of Excel?  I really think
that will fix all my problems.

Thanks so much!!
Click to expand...


Got it working!! I had some help from a colleague in my office.
Thanks Mandeep!!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Working outlook via vba code with xlsx - error 0
How can I force a workbook to close? 6
Word macro that can call cells out of Excel for a find and replace 0
Closing PDF file from Excel VBA (Adobe Reader, Adobe Acrobat, Browser) 0
Help needed! Extracting Heading 3 and Contents from a Word Doc to Excel 0
Copy sheet from workbook in new instance of Excel 3
How to reference a file in a different instance of Excel? 4
Excel instance isolation problem 0

Top