Data Portal Design Requires Advanced Coding

[sdruley]

This short snippet of code was designed to create a data portal between two open instances of Excel. The purpose of doing so is two fold:
1) to transfer the data realtime
2) to allow for two simultaneous executions of code

The code below develops two errors when it gets to the COPY FROM....COPY TO code line:

1) Microsoft is waiting for another application to complete an OLE action
2) Copy method of class failed

Any ideas on why I am getting these errors would be appreciated. My main purpose is to be able to make data available, in one session of excel, to another session so that only the newest data is carried over and appended to the database.

[vba]Sub Two_Instance_Data_Portal()
Dim xlStuff As Excel.Application
Dim xlObj As Object
Dim ows As Worksheet
Dim dws As Worksheet
Dim stSourceWkBook As String
Dim stSourceSheet As String
Dim stSourceRange As String
Dim stTO_WkBook As String
Dim stTO_Sheet As String
Dim stTO_Range As String[/vba][vba]
' Copy From Objects [ows = origination worksheet in instance 1]
stSourceWkBook = "c:\TTND\IQLinkData30.xlsm"
stSourceSheet = "LINKDATA"
stSourceRange = "Chicago"
Set xlObj = GetObject(stSourceWkBook)
Set ows = xlObj.Sheets(stSourceSheet)
' Copy to Objects [dws = destination worksheet in instance 2]
stTO_WkBook = "c:\TTND\Picasso31.xlsm" ' cross instance reference must be index [2]
stTO_Sheet = "NEWMIRROR" ' cross instance reference must be index [8]
stTO_Range = "Receiver"
Windows("Picasso31.xlsm").Activate
Set xlStuff = Excel.Application
Set dws = xlStuff.Workbooks(2).Sheets(8)
' COPY FROM .............................COPY TO.................
ows.Range(stSourceRange).Copy dws.Range(stTO_Range)
Set xlStuff = Nothing
Set xlObj = Nothing
Set ws = Nothing
End Sub[/vba]

[p45cal]

Trying here to reproduce your setup, but need some help:
You have two instances of Excel.Application;
1. Where is the code you've shown us located? (Instance|workbook name|module)
2. What workbooks are open before the code runs and in which instances are they?

I see you've cross posted to excelforum.com, would you be good enough to include a link here to that cross post and any others (just so that people answering aren't wasting their time duplicating others' work).

[sdruley]

p45cal,

Indebted to you for your response to this difficult subject matter...

Workbook A is receiving data though a DDE link from Chicago Merchantile Exchange. It has its own code running to collect this data and resides in Instance 1 of Excel.
Workbook B (in instance 2) has the above code running (hopefully) to create a real-time image of the database set up in Workbook A. Workbook B is heavily involved in number crunching.
All workbooks in the app are open at all times.

Workbook A specifics:

Instance: 1
Name: IQLinkData30.xlsm
Sheet: LINKDATA
Range: Chicago

Workbook B Specifics:

Instance: 2
Name: Picasso31.xlsm
Sheet: NEWMIRROR
Range: Receiver

Cross Post at Excel Help Forum under user sdruley. Link not available due to limited number of posts.

The purpose behind having two instances of Excel is to better facilitate the running of two simultaneous programs, each gobbling up resources.

My system:

HPE-190t
Clock 3.33 GHz
12 Processors
64 bit
24 GB mem

[p45cal]

You seem to go round the houses to establish dws as a sheet in the workbook where the code resides. You can use
Set dws = ThisWorkbook.Sheets(stTO_Sheet)

I think the copying error might occur because there may be two clipboards, one in each instance of excel, so try changing tha line to:
dws.Range(stTO_Range).Value = ows.Range(stSourceRange).Value
which doesn't involve the clipboard.

[sdruley]

p45cal,

Your feedback, which solved my long-standing challenge to connect two instances of Excel with a data portal, is simply brilliant. It not only works but it pushes data over the "instance" fence at the speed of light.
I currently have this same challenge submitted to Microsoft professionals under an incident number and they haven't solved it yet for me. One thing I have learned from these type of miracle solutions is that they always seem to be shorter and more simplified in their structure.
Thank you, thank you and thank you...

The new code is as follows:


[VBA]'Rem Microsoft ActiveX Data Objects 6.0 Library
'Rem Joint effort between Kirk Beller, Stephen Druley and the experts at VBAExpress
' to establish a data portal from one instance of Excel to another
Sub Two_Instance_Data_Portal()
Dim xlObj As Object
Dim ows As Worksheet
Dim stSourceWkBook As String
Dim stSourceSheet As String
Dim stSourceRange As String
Dim stTO_Range As Range
' Copy From Objects [ows = origination worksheet in instance 1]
stSourceWkBook = "c:\TTND\IQLinkData30.xlsm"
stSourceSheet = "LINKDATA"
stSourceRange = "Chicago"
Set xlObj = GetObject(stSourceWkBook)
Set ows = xlObj.Sheets(stSourceSheet)
' Copy to Objects [instance 2]
Set stTO_Range = Application.Range("Receiver")
' COPY TO ............................. FROM .................
stTO_Range.Value = ows.Range(stSourceRange).Value
End Sub[/VBA]