[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?

[opus]

Welcome to VBForums

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.

Instead of the name of those sheets ,you could use the index they did get when opened.

[westconn1]

Set xlApp = CreateObject("Excel.Application")

each instance of excel should be set to a different object variable, or possibly a collection of excel instances, then each can have key, to reference it by, same probably applies to the workbook objects

the collection or other variables should be declared at module level, so they remain in scope between procedures

you can certainly use a class to do this, but i am not sure you really need to

[maxzara]

each instance of excel should be set to a different object variable, or possibly a collection of excel instances, then each can have key, to reference it by, same probably applies to the workbook objects

the collection or other variables should be declared at module level, so they remain in scope between procedures

you can certainly use a class to do this, but i am not sure you really need to

Thank you very much, that's exactly what I have done since. Should I create a new topic for handling the data processing issue?
I have a series of data i.e. a list of prices (likely to be 120 records long), which need to be transferred from each individual instance of xlApp to the original. What is the best way of transferring these across? (Considering basic Copy/Paste does not work)

[westconn1]

Considering basic Copy/Paste does not work)

it doesn't?
you can assign values from one range to another across instances
you can assign values from ranges into a 2d array
then from the 2d array into another range
i am surprised that copy and paste can not be made to work across instances, but if not the widows clipboard object could be used