CODEWITHSUNDEEP
excelvbamemoryout-of-memoryinstance
St. Jimmy
St. Jimmy

Reputation: 75

Call an excel macro in specific instance of excel in VBA

I'm receiving out of resources errors in 32-bit Excel 365 (64-bit remedies the problem but our IT won't allow it for the number of users that would use this book, so I'm stuck with 32-bit). I have plenty of RAM so I'm trying to split the two workbooks into different instances to allow more memory allocation (if this is a bad strategy I'd be happy to know why/alternatives!).

My set-up has two workbooks, Workbook A opens Workbook B and Workbook B runs the heavy lifting code and then transfers the data back to Workbook A in value format.

Excel Instance 1: Workbook A

Excel Instance 2: Workbook B

I want WkbA (open in Excel Instance 1) to call a macro on WrkB open in Excel Instance 2. I wrote the code below to try this.

Dim WkbB As Object
Set WkbB = New Excel.Application
With WkbB
    .Visible = True
    .Workbooks.Open "P:\Root\WorkbookB.xlsm", True, False
End With

Application.Run "'WkbB.xlsm'!Main"

At the 'End With' line, the Code opens WkbA and WrkB in two separate Excel Instances as I want

Excel Instance 1: WrkA

Excel Instance 2: WrkB

But at the Application.Run line excel opens WrkB again in instance 1

Excel Instance 1: WrkA & WrkB

Excel Instance 2: WrkB

Instead of calling the Main subroutine in Instance 2 WorkbookB.

Upvotes: 0

Views: 360

Answers (1)

user11509084
user11509084

Reputation:

Change

Application.Run "'WkbB.xlsm'!Main"

to 

WrkB.Run "'WorkbookB.xlsm'!Main"

Upvotes: 2

Related Questions