Efficient Data Transfer Between Two Open Sessons of Excel

Efficient Data Transfer Between Two Open Sessons of Excel

I have two sessions of Excel open… Workbooks A in session 1 and B in session 2. Workbook B needs to run by itself since it is using DDE to bring data in from a third party and can not be encumbered by any resource strain, e.g. calculations. Once data has been properly ported into Workbook A, I need to immediately, within a few milliseconds, port the data to Workbook B where some intensive number crunching takes place. Form a resource perspective, this arrangement reaches a good balance.
The challenge is to find the most efficient way of making this transfer of data by crossing over the border from one open session of Excel to another.
I am using Excel 2010, Windows 7 Pro, 64bit. Some trial and error work was done with the following with no luck probably because I am not that famaliar with the protocols:
1) DAO
2) ADO
3) DDE
I am looking for some out-of-the-box solution here that is simple and effective.

If you want something simple and effective, please provide helpers with a posted example according to those same criteria....

Attached are two files demonstrating the task.

Bump No Response

Bump No Response

I think the simplest way is to copy the range that contains the DDE-link, and use pastespecial in your Workbook B, paste link.

So the result in workbook B will be like

PHP Code: 

=[__Query1.xls]Blad1!C4 


Now workbook B will be a realtime copy of workbook A.(in my example __Query1.xls)

snb,

Thank you for your excellent suggestion. Along with some help from Chip Pearson we came up with the following code:

' START CODE
''''''''''''''''''''''''''''''''''''
Dim ThisInstance As Excel.Application
Dim XLInstance2 As Excel.Application
Sub Test()

Set ThisInstance = Application
If XLInstance2 Is Nothing Then
Set XLInstance2 = New Excel.Application
End If
With XLInstance2
.Visible = True
.Workbooks.Open Filename:="C:\Test\Book1.xlsm"
End With

ThisInstance.Workbooks("Book1.xlsm").Worksheets("Sheet1").Range("A1").Value = _
XLInstance2 .Application.ActiveWorkbook.Sheets("Sheet1").Cells(1, 1).Value

End Sub
''''''''''''''''''''''''''''''''''''
' END CODE
''''''''''''''''''''''''''''''''''''

In trying your code it appears that both solutions work. I'm in the process of trying to figure out which is faster. What would be your opinion on this? I really like your suggestion.

I prefer my first suggestion, because, when having implemented it in a file and having saved the file, you won't need anymore VBA. Opening the file will update the data with the 'sourcefile'.
Besides it only uses 1 instance of Excel

On top of that from a coding point of view the CP suggestion doesn't seem to me to be the most efficient one.
Using that kind of solution I'd prefer the oneliner:

Please Login or Register  to view this content.

To implement my first suggestion using VBA:

Please Login or Register  to view this content.

snb, Once again, great input.

In my original post it was pointed out that 2 sessions of Excel were needed to mitigate the resource demands required by a delicate incoming 3rd party feed as any burden placed on the feed causes it to pause or become unstable. So, and I could be wrong here, one Excel session is required to manage/protect the feed while the other session plucks the data from the first session without affecting its quality, consistency or precision.
The second session of my software is also using every bit of the 12 processors I have available to do some daunting, multithreaded number crunching. Both sessions of Excel are part of a concurrent, realtime solution to provide clients with on-time decision making.
I am impressed by your recent suggestions, particularly since they support a one session scenario. If you feel that this can be done in one session, please let me know. Otherwise, I am still working on your earlier contribution employing the query syntax to see how this fits.
Thank you for your help.

Based on what you told I think it can be done.

What I did to simulate your situation:

- I made a '__source.xls' containing a webquery, that is being updated every minute.
- I made a '__destination.xls' completely linked with the range in '__source.xls' where the querytable writes it's results
- I saved the combination of both workbooks as a 'workspace': '__beurs.xlw'

Everytime I open '__beurs.xlw', both workbooks are being opened simultaneously, the file '__source.xls' will be updating the webquery, the file '__destination.xls' will be adapted at the same time.
So I don't see any need to open 2 instances of Excel.

snb,

An incredible out-of-the-box solution. How did you ever come up with this?
Simply brilliant.

sdruley

PS, I tried to click on the reputation icon but I forgot that I already did it earlier.

At least you will be able now to help Chip Pearson.