[RESOLVED] Help With Multiple Read-Only Instances of Excel

[maxzara]

Hi al,

I am suffering with trying to figure out how to control multiple instances of Excel. I have an Excel Workbook (Basic Calculation Generator), which I would like to use to assess basic scenarios. You have several projects and costs have to be minimised.

I have written code to open several instances of this project as below;

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 = "Project.xlsm"
sWorkbookToOpen = strPath & sWorkbook
blnReadOnly = 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

End If
If blnOpenRef = True Then
wbRef.Activate
Else
' wsWorking.Activate
End If

Now I would like to control each instance that I have opened. As they are opened read-only and all have the same name, the stuff I have found online doesn't particularly prove to be very helpful.

Also I would like to control each instance with different parameters. I.e. I would like to be able to put in data into project 1/project 2 which are different and then copy them from the "Project.xlsm" file into another file after getting the results from the Generator.

What is the easiest way to go about this?

Many thanks and I look forward to your advice.

Also, what would be the easiest way of transferring data between these instances. Shall I create an object class where the data is pooled into a list or is there another way to do this?