Worksheet gets update from DDE server

[hmmm...]

I am dealing with an application that gets an update from a 3rd party DDE
server. This occurs when the workbook is opened (uses workbook_open()).

The VB code in workbook_open issues a call to the DDE server. It takes a
few seconds (varies) for the results to come back. Colums B and C are
updated with approximately 1000 rows of data.

The worksheet has a chart. Besides issuing the call to the DDE server, the
VB code in workbook_open() is used to tweak the chart. However, the VB code
must wait until the results come back from the DDE server. That is where I
am having trouble. If I use MsgBox to pause execution, the DDE results come
back, I click OK, and everything works fine. If I instead use
"Application.Wait" to pause for several seconds, it appears that the DDE
updates are blocked, and so the B and C cell values are not populated with
the data that are required by the VB chart code. If I create a "do loop",
testing for the presence of the DDE data, nothing happens, so it looks like
that blocks the DDE updates too.

Does anyone know of a way of pausing a VB script, that will not block DDE
updates to a worksheet? Thanks!

 

[Peter Huang [MSFT]]

Hi,

You may try to DoEvents in the "do loop".

Regards,
Peter Huang
Microsoft Online Partner Support
Get Secure! www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
--------------------

From: "hmmm..." <[email protected]>
Newsgroups: microsoft.public.excel.misc,microsoft.public.excel.programming
Subject: Worksheet gets update from DDE server
Lines: 22
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 5.50.4807.1700
X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
Message-ID: <[email protected]>
Date: Wed, 20 Aug 2003 16:51:10 GMT
NNTP-Posting-Host: 209.178.190.87
X-Complaints-To: (e-mail address removed)
X-Trace: newsread4.news.pas.earthlink.net 1061398270 209.178.190.87 (Wed, 20 Aug 2003 09:51:10 PDT)
NNTP-Posting-Date: Wed, 20 Aug 2003 09:51:10 PDT
Organization: EarthLink Inc. -- http://www.EarthLink.net
Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!newsfeed00.sul.t-online.de!t-onlin
e.de!nntp-relay.ihug.net!ihug.co.nz!logbridge.uoregon.edu!newshub.sdsu.edu!e
lnk-nf2-pas!newsfeed.earthlink.net!stamper.news.pas.earthlink.net!newsread4.
news.pas.earthlink.net.POSTED!not-for-mail
Xref: cpmsftngxa06.phx.gbl microsoft.public.excel.programming:408482 microsoft.public.excel.misc:266331
X-Tomcat-NG: microsoft.public.excel.programming

I am dealing with an application that gets an update from a 3rd party DDE
server. This occurs when the workbook is opened (uses workbook_open()).

The VB code in workbook_open issues a call to the DDE server. It takes a
few seconds (varies) for the results to come back. Colums B and C are
updated with approximately 1000 rows of data.

The worksheet has a chart. Besides issuing the call to the DDE server, the
VB code in workbook_open() is used to tweak the chart. However, the VB code
must wait until the results come back from the DDE server. That is where I
am having trouble. If I use MsgBox to pause execution, the DDE results come
back, I click OK, and everything works fine. If I instead use
"Application.Wait" to pause for several seconds, it appears that the DDE
updates are blocked, and so the B and C cell values are not populated with
the data that are required by the VB chart code. If I create a "do loop",
testing for the presence of the DDE data, nothing happens, so it looks like
that blocks the DDE updates too.

Does anyone know of a way of pausing a VB script, that will not block DDE
updates to a worksheet? Thanks!

Regards,
Peter Huang
Microsoft Online Partner Support
Get Secure! www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.

 

[Peter Huang [MSFT]]

Hi,

Here is a sample.
Dim WithEvents wd As Word.Application
Dim condition As Boolean
Private Sub Command1_Click()
condition = False
Dim exapp As Excel.Application
Set exapp = New Excel.Application
Set wd = New Word.Application
wd.Documents.Open "c:\test.doc"
wd.Visible = True
channelNumber = exapp.DDEInitiate( _
App:="WinWord", _
topic:="C:\test.doc")
exapp.DDEExecute channelNumber, "[FILEPRINT]"
Do
DoEvents
Debug.Print "Wait for DDE Call return"
Loop Until condition = True
exapp.DDETerminate channelNumber
End Sub

Private Sub wd_DocumentBeforePrint(ByVal Doc As Word.Document, Cancel As
Boolean)
condition = True
End Sub

Regards,
Peter Huang
Microsoft Online Partner Support
Get Secure! www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
--------------------

X-Tomcat-ID: 204477300
References: <[email protected]>
MIME-Version: 1.0
Content-Type: text/plain
Content-Transfer-Encoding: 7bit
From: (e-mail address removed) (Peter Huang [MSFT])
Organization: Microsoft
Date: Thu, 21 Aug 2003 07:50:22 GMT
Subject: RE: Worksheet gets update from DDE server
X-Tomcat-NG: microsoft.public.excel.programming
Message-ID: <[email protected]>
Newsgroups: microsoft.public.excel.programming
Lines: 71
Path: cpmsftngxa06.phx.gbl
Xref: cpmsftngxa06.phx.gbl microsoft.public.excel.programming:408717
NNTP-Posting-Host: TOMCATIMPORT2 10.201.218.182

Hi,

You may try to DoEvents in the "do loop".

Regards,
Peter Huang
Microsoft Online Partner Support
Get Secure! www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
--------------------

From: "hmmm..." <[email protected]>
Newsgroups: microsoft.public.excel.misc,microsoft.public.excel.programming
Subject: Worksheet gets update from DDE server
Lines: 22
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 5.50.4807.1700
X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
Message-ID: <[email protected]>
Date: Wed, 20 Aug 2003 16:51:10 GMT
NNTP-Posting-Host: 209.178.190.87
X-Complaints-To: (e-mail address removed)
X-Trace: newsread4.news.pas.earthlink.net 1061398270 209.178.190.87 (Wed, 20 Aug 2003 09:51:10 PDT)
NNTP-Posting-Date: Wed, 20 Aug 2003 09:51:10 PDT
Organization: EarthLink Inc. -- http://www.EarthLink.net
Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!newsfeed00.sul.t-online.de!t-onli

n

e.de!nntp-relay.ihug.net!ihug.co.nz!logbridge.uoregon.edu!newshub.sdsu.edu!

e
lnk-nf2-pas!newsfeed.earthlink.net!stamper.news.pas.earthlink.net!newsread4