Christopher,
I was wrestling with the add-in concept, and found out that it was just
not
practicle. I confered with a couple of other folks here that confered with
me.
The problem is that the add-in has to have access to your RTD object through
multiple workbooks or instances of Excel. This gets a lot more
complicated,
and I don't know where to start, or when I'd be finished doing a proof of
concept (POC)
on it. But here's another alternative. Wrap the RTD in a UDF. Use a
global variable to
see if you want to do the update or not. Here's a quick sample you can
put in a sheet
to show you what I'm talking about. With a little tweeking and tuning I
think this will give
you what you want. The RTD Object I'm using here is documented in MSDN
under
Building Excel Real-Time Data Components in Visual Basic .NET. You could
uses
your own or one of the KB article samples. Here a KB article with one
too...
HOWTO: Create a RealTimeData Server for Excel 2002
http://support.microsoft.com/?id=285339
Now the code. Add this code to an Excel Workbook
Dim AllowRTD As Boolean
Private Sub Workbook_Open()
End Sub
Private Sub Workbook_Activate()
AllowRTD = False
End Sub
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
runRTD = False
End Sub
Public Function MyRTD(MyObject As String, Topic1 As String, Topic2 As
String) As Double
If AllowRTD = True Then
MyRTD = Application.WorksheetFunction.RTD(MyObject, "", Topic1,
Topic2)
End If
End Function
Public Sub TurnOffRTD()
AllowRTD = False
End Sub
Public Sub TurnOnRTD()
AllowRTD = True
Application.RTD.RestartServers
End Sub
Then in your spreadsheet go to a cell and put the call to the main UDF
=MyRTD("Stock.Quote", "MSFT", "Last")
or
=MyRTD("Stock.Quote","MSFT", "Open")
If you experiment with the TurnOffRDT and TurnOnRTD function you see that
if you
Wrap all your RTD calls by a function you can turn them all on and off at
will. You can
event put them in an XLA if you want.
Let me know if this resolves this for you...
Stephen Culhane
Microsoft Developer Support
This posting is provided "AS IS" with no warranties, and confers no rights.
--------------------
| From: (Christopher Rizzo)
| Newsgroups: microsoft.public.excel.programming
| Subject: Excel 2002 RTD - GetNewValues and RestartServers
| Date: 11 Jul 2003 12:22:01 -0700
| Organization: http://groups.google.com/
| Lines: 74
| Message-ID:
| References:
| NNTP-Posting-Host: 192.223.243.6
| Content-Type: text/plain; charset=ISO-8859-1
| Content-Transfer-Encoding: 8bit
| X-Trace: posting.google.com 1057951322 3779 127.0.0.1 (11 Jul 2003
19:22:02 GMT)
| X-Complaints-To:
| NNTP-Posting-Date: 11 Jul 2003 19:22:02 GMT
| Path:
cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!newsfeed 00.sul.t-online.de!t-onlin
e.de!news-spur1.maxwell.syr.edu!news.maxwell.syr.edu!newsfee d-east.nntpserve
r.com!nntpserver.com!chi1.webusenet.com!news.webus enet.com!pd2nf1so.cg.shawc
able.net!residential.shaw.ca!sn-xit-03!sn-xit-06!sn-xit-01!sn-xit-09!superne
ws.com!postnews1.google.com!not-for-mail
| Xref: cpmsftngxa06.phx.gbl microsoft.public.excel.programming:396859
| X-Tomcat-NG: microsoft.public.excel.programming
|
| Sounds good...I'll keep looking at it also - keep me posted.
|
| CJR
| ---------------------
| (Steve Culhane [MS]) wrote in message
...
| Christopher,
| I'm researching a way I think you can do it, and will get back to
you
| on my success or failure, ASAP. If it works I'll have sample code for
you
| too.
|
| Stephen Culhane
|
| Microsoft Developer Support
|
| This posting is provided "AS IS" with no warranties, and confers no
rights.
|
|
|
| --------------------
| | From: (Christopher Rizzo)
| | Newsgroups: microsoft.public.excel.programming
| | Subject: Excel 2002 RTD - GetNewValues and RestartServers
| | Date: 9 Jul 2003 12:09:46 -0700
| | Organization: http://groups.google.com/
| | Lines: 28
| | Message-ID:
| | References:
|
| | NNTP-Posting-Host: 192.223.243.6
| | Content-Type: text/plain; charset=ISO-8859-1
| | Content-Transfer-Encoding: 8bit
| | X-Trace: posting.google.com 1057777787 1472 127.0.0.1 (9 Jul 2003
| 19:09:47 GMT)
| | X-Complaints-To:
| | NNTP-Posting-Date: 9 Jul 2003 19:09:47 GMT
| | Path:
|
cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!newsfeed 00.sul.t-online.de!t-onlin
|
e.de!news-spur1.maxwell.syr.edu!news.maxwell.syr.edu!sn-xit-03!sn-xit-06!sn-
| xit-09!supernews.com!postnews1.google.com!not-for-mail
| | Xref: cpmsftngxa06.phx.gbl microsoft.public.excel.programming:396125
| | X-Tomcat-NG: microsoft.public.excel.programming
| |
| | Hi Stephen,
| |
| | In short, yes, you are correct. I know that I can do this with the
| | GetNewValues parameter. The wrinkle is if a user has opted not to
| | have updates immediately and then decides to update the values in an
| | already open workbook - how can I "restart" the RTD process to begin
| | pulling in new values?
| |
| | Thanks for your reply - hope this makes sense.
| |
| | Christopher J. Rizzo
| |
| |
| | -----------------------------------------------
| | (Steve Culhane [MS]) wrote in message
| ...
| | Christopher,
| | From what I've read it seams like you want to turn on and off
the
| update
| | through an add-in. And you want the user to be able to do this on
a
| | per workbook basis.
| |
| | Let me know if that's what you're looking for
| |
| |
| | Stephen Culhane
| |
| | Microsoft Developer Support
| |
| | This posting is provided "AS IS" with no warranties, and confers no
| rights.
| |
|