Open NEw instance of Excel through MAcro

[taufiq_786]

Is there a way to specify in VB to open a workbook in a new instance of Excel?

I.E. if I have a workbook open already, and I navigate to an excel file and double click it will open that file in the same instance of excel. But, if I have an excel file open and I go to Start Menu, Excel, then I have a totally seperate instance of Excel.

The reason I need to do this is I have a task that requires to seperate macros to run concurrently, and the only way I know of to accomplish this is in two seperate instances of excel. But I would like a way to programmatically accomplish this, instead of having to manually open two instances and fire two macros..

Essentially what I want to do is have a macro in WB 1 that when fired will open WB 2 in its own instance, then continue through its own code. WB 2 will have a Workbook.Open event that will subsequently fire its own macro, effectively starting both macros to run concurrently with a single button press.

Has anyone done this or can you point me in the right direction to open a 2nd instance of Excel through VB?

 

[Andrew Poulsom]

Example:

Sub Test()
    Dim xlApp As Application
    Set xlApp = CreateObject("Excel.Application")
    xlApp.Workbooks.Add
    xlApp.Visible = True
End Sub

You can Open a workbook instead of Adding one.

 

[taufiq_786]

Hi Andrew,

Thanks for the prompt reply.
with this code i am able to open the new instance but the file which i need to open thru the macro open's in the old instance only.

 

[RoryA]

You need to use xlapp.workbooks.open and not just Workbooks.Open

 

[Andrew Poulsom]

Did you open it like this?

xlApp.Workbooks.Open "PathToWorkbook"

 

[taufiq_786]

yes andrew i tried the same thing and now its opening.

but when i want to to go back to the old instance of excel the macro is not recongnizing that instance.

what can be done for that.

really appreciate your help on this.

 

[Andrew Poulsom]

Sorry, I don't know what you mean.

 

[taufiq_786]

hey andrew,

i have a file which have more than 3 hundred thousand records.
so i want to write a macro which will divide the file in 3 workbooks so on each i can run another macro simultaneously so my time is saved.

so the code u gave opens the new instance but the macro does not paste the copied records after division in that instance.

hope i was able to explain u properly

 

[Andrew Poulsom]

Better post your macro. It's sounding a bit complicated.

 

[taufiq_786]

Dim spath2 As String
Dim spath3 As String
Dim xlApp As Application
Dim xlAppGI As Application

spath1 = "I:\GoPricing\Tofiq\global_imagine.csv"
spath2 = "I:\GoPricing\Tofiq\Milenium-Cleanup2.xlsm"
spath3 = "I:\GoPricing\Tofiq\Milenium-Cleanup3.xlsm"



Set xlAppGI = CreateObject("Excel.Application")
xlAppGI.Workbooks.Add
xlAppGI.Visible = True
xlAppGI.Workbooks.Open (spath1)





Application.CutCopyMode = False
Range("A2").Select
xlAppGI.Workbooks
Rows("2:75000").Select
Selection.Copy
Windows("Milenium-Cleanup1.xlsm").Activate
Range("A2").Select
ActiveSheet.Paste



Set xlApp = CreateObject("Excel.Application")
xlApp.Workbooks.Add
xlApp.Visible = True
xlApp.Workbooks.Open (spath2)


'Workbooks.Open (spath2)

xlAppGI.Workbooks.Open ("global_imagine.csv")
Rows("75001:150000").Select
Application.CutCopyMode = False
Selection.Copy
xlApp.Workbooks("Milenium-Cleanup2.xlsm").Activate
Range("A2").Select
ActiveSheet.Paste


Set xlApp = CreateObject("Excel.Application")
xlApp.Workbooks.Add
xlApp.Visible = True
xlApp.Workbooks.Open (spath3)

xlAppGI.Workbooks.Open ("global_imagine.csv")
Rows("150001:225000").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Milenium-Cleanup3.xlsm").Activate
Range("A2").Select
ActiveSheet.Paste
Application.CutCopyMode = False

End Sub
by using this code i want to divide the data in 3 workbooks so i can run the cleanup macro simualtaneously on each workbook at the same time.

clean up macro is

Sub deleterow()

Columns("Q:BR").Select
Selection.Delete Shift:=xlToLeft

a = 2
b = ActiveSheet.UsedRange.Rows.Count + 1


Do While a < b
Range("K" & a).Select
If ActiveCell.Value = "" Or ActiveCell.Value = 0 Then
ActiveCell.EntireRow.Delete
b = b - 1
Else
Range("P" & a).Select
If ActiveCell.Value = "" Or ActiveCell.Value = 0 Then
ActiveCell.EntireRow.Delete
b = b - 1
Else
Range("Q" & a).Select
If ActiveCell.Value = "" Or ActiveCell.Value = 0 Then
ActiveCell.EntireRow.Delete
b = b - 1
Else

ActiveCell.Offset(1, 0).Select
a = a + 1
End If
End If
End If
Loop
Exit Sub

End Sub