open a new instance of Excel

[Djblois]

I want to be able to open a new instance of Excel.

How do I do this with VBA and how do I do it with a keyboard shortcut?

[Bob Phillips]

[vba]

Set xlApp = CreateObject("Excel.Application")
[/vba]

Put it in a macro and assign a shortcut with

[vba]

Application.OnKey "^+!","myMacro"
[/vba]

[Djblois]

I tried this code to get a particular workbook to open in Excel in a new instance but it didn't work. What am I doing wrong?

[VBA] blnIsOpen = True
On Error Resume Next
Set wbRef = Workbooks(sWorkbook)

If Err Then
Set xlApp = CreateObject("Excel.Application")
Set wbRef = xlApp.Workbooks.Open(sWorkbookToOpen, , blnReadOnly, , , , True, , , , , , False)
blnIsOpen = False
End If
If blnOpenRef = True Then
wbRef.Activate
Else
wsWorking.Activate
End If
On Error GoTo 0[/VBA]

The workbook just never opened - I did not get an error or anything it just didn't open.

[GTO]

Greetings Daniel,

A bit of guessing, as I don't know where blnOpenRef comes from etc, but are you sure its not opening? It created a new instance for me, but as visibility has not been set to true, the app would remain hidden.

You might wish to try it like below. Rather than ignoring errors for several statements, we could limit it to just the Set wbRef... line. This way, we're not flying by any errors in opening/activating the wb or activating a sheet.

[vba]
Sub exa2()
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 = "Fake.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 Sub
[/vba]

Hope that helps,

Mark

[Djblois]

Ok, you are correct it is opening but none of the formulas that I have coded to work in the first instance of Excel will reference the workbook in the new instance of excel. How do I get it to reference a different instance of excel. Do I have to use the xlApp in every situation that I reference the workbook?

[Bob Phillips]

What exactly do you want to do with this new instance Daniel?

[Djblois]

Here is an example of one thing I want to do with it. rngAddto will be in the first instance of Excel and wbRef will be the workbook opening in the new instance of Excel.

[VBA]Sub comAddWhse(ByVal rngAddto As Range, ByVal intColumnDiff As Integer)

'Adds Warehouse from Warehouse Number from BusinessReportingReference.xls
With rngAddto
.FormulaR1C1 = _
"=if(ISNA(VLOOKUP(RC[" & intColumnDiff & "],'[" & wbRef.Name & "]Whses'!C1:C2,2,FALSE)),"""",VLOOKUP(RC[" & intColumnDiff & "],'[" & wbRef.Name & "]Whses'!C1:C2,2,FALSE))"
.Value = rngAddto.Value 'Changes Formula into Values
End With
End Sub[/VBA]

[mdmackillop]

To satisfy my curiosity, why do you particularly need the second instance?

[Djblois]

Sorry mdmackillop I thought I responded to you already - now that I came back here I see that I did not.

The reason I need to open a new instance of Excel is because we use our DBMA to create Excel Reports and it takes a long time. While that report is running, if someone opens another Excel file it will crash the running report. So theoretically opening a new instance of Excel may prevent this issue. (it is hard to test to see if it fixes the issue because it does not always crash the new report)

[timsta13]

Greetings Daniel,

A bit of guessing, as I don't know where blnOpenRef comes from etc, but are you sure its not opening? It created a new instance for me, but as visibility has not been set to true, the app would remain hidden.

You might wish to try it like below. Rather than ignoring errors for several statements, we could limit it to just the Set wbRef... line. This way, we're not flying by any errors in opening/activating the wb or activating a sheet.

[vba]
Sub exa2()
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 = "Fake.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 Sub
[/vba]

Hope that helps,

Mark

This worked perfectly for me, just modified it slightly to feed the workbook path into the sub. Using it in a "control centre" type application giving easy access to the desired workbooks, and enabling more than one task to be accessed at once.

Cheers