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!!

On Jul 28, 1:42*am, ryguy7272 wrote:
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!!


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!!

On Jul 28, 6:22*am, ryguy7272 wrote:
On Jul 28, 1:42*am, ryguy7272 wrote:



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!!

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!!


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

On Jul 28, 6:22*am, ryguy7272 wrote:
On Jul 28, 1:42*am, ryguy7272 wrote:



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!!

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!!


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