Communicating between two excel applications

I spent about a day researching how to communicate between two excel applications before I gave up. I was able to get to the point of being able to get hwd id for each of them, but I was not able to activate using this value. The problem I had wasn't worth anymore effort into it.

Here's the code I used to get the hwd id.


Let me know if you find some way to activate the second application, I would be interested.

HTH
Cal

I think DDE is a possible soultion.
I have an example for DDE between word and Excel.

You have to initiate a DDE session by calling
1) DDEInitiate
2) execute commands using DDEEXECUTE function
3)transfer data using DDEPOKE function.

Code example:
Dim pokerange as object
Dim chan as integer

Set pokerange = range("sheet1!a1")

'Initiate channel to word application

Chan = DDEInitiate("winword", "filename.doc")

DDEExecute Chan , "[InsertPara]"

' Transfer the data from sheet1!a1 to word document filename.doc
'StartOfDoc is the predefined bookmark in word document.

DDEPOKE Chan, "\Startofdoc", Pokerange

DDETerminate Chan

End Sub

How to adopt this idea of transferring from excel -> word To Excel to excel?

Another idea for your question is

Can you use

Application.Workbooks("book7.xls").worksheets("Sheet1").activate

or
Application.run "Book7.xls!modulename"

Let us churn out some ideas, then the end of the tunnel will be visible.

Neetha

The major problem I ran into was being able to identify an existing instance of excel that was open already and referencing it from the current open excel instance. The GetObject() seems to choke when two copies are open. I had a process that opened two instance of excel and I needed to somehow reference both from a macro running in one instance, hense the need to ID the second one. If you are running a macro in one instance and using that macro to open the second instance, you should have your references already setup? Or is your process like mine where both are started seperately?

Here's code to open a new instance of excel and add a workbook.

I've never tried the DDE method, but hopefully this code will help you out.

Cal

PS-Application. will reference the initial copy of excel's application object
xlapp. will reference the second copy of the excel application object.
You should now be able to pass data back and forth without any issues.

Bothe the xls are opened separately.
App1 has the list of cusips (vector of inputs) , which are to be entered into a cell in App2 one by one.
App2 is the app that does all the calculations and generate the output.

A macro in App2 does the calculations and it takes 1 min for it generate the output.

In App1 I have a for loop to process the array of inputs.

App1 the code is like this.

For i = 1 To n
App2.sheet1.cells(5,5).value = App1.sheet1.cells(i,1).value
Application.run ("App2.xls!Macro1") ' This macro computes the result
sleep 60000 (milliseconds) ' until the App2 computes the result.
App1.sheet2.cells(i,1).value = App2.sheet1.cells(10,10).value

Next

This kind of code should work, but it is not.
What is the bug in it?

Neetha

How is it not working? Is it erroring, or just never getting a response?

It works for the first cusip only.
For i = 2 onwards, the code gets executed, but the reulsts on the App2 are not changing.

I thought the sleep function will put only App1 to sleep. May be it is putting app2 to sleep also.

Is there a way I can issue a shell command to create a sub process that loads the App2 and executes it?

The sameway you can fork a sub process and load a different program and execute in C language?

Neetha

You should be able to call a specific Excel sheet like this using shell.

"C:\program Files\Microsoft Office\Office11\Excel.exe ""C:\....\Sheet2.xls"""

You will need to update the path's of course.

Cal

No that is not what I meant.
I want to make the App2 (which calculates the Result) to run in the background when the App1 goes to sleep.

That is not happening now.

In visual basic 6.0, there is a way you can issue a system command to run another process in the background independently of the parent process.

Is there a similar one in VBA?

I want to run App2 from App1, put App1 to sleep and wait for a minute.
When App1 wakes up after 1 minute, App2 would have calculated the result. Copy the result from App2 to App1.

Thanks for the discussion.

Neetha

What about something like this?

Private Sub CommandButton1_Click()
Shell ("C:\Program Files\Microsoft Office\OFFICE11\Excel.exe ""C:\test.xls""")
For Count = 1 To 100000
Next Count
WaitForResponse

MsgBox "Process Completed"
End Sub
Sub WaitForResponse()
Dim blnKPINotFinished As Boolean
Dim iErrCount As Integer

On Error GoTo ErrHandleWFR
iErrCount = 0
blnKPINotFinished = True
Do Until blnKPINotFinished = False
AppActivate "Microsoft Excel - Test.xls"
DoEvents
Loop
Exit Sub
ErrHandleWFR:
If iErrCount > 1000 Then
blnKPINotFinished = False
Resume Next
Else
iErrCount = iErrCount + 1
Resume
End If
End Sub

You would need to close the app2 after the wait time and reopen it again for process to work? Not sure if that will help.

Cal