Solved

Calling a value from an RTD sever into vba not excel cell

Posted on 2013-02-05
11
2,174 Views
Last Modified: 2013-03-05
Hi,

I need to call a value from a RTD server which outputs data once a second and I need to pick up this data in vba then do lots of calculations on it and in some cases output data back to the same device which generates the RTD server.

The problem I am having is actualy with the first bit of just geting the data into vba i can type = RTD("name of server",,"channel name") in to a cell and i get the value which updates and works perfectly. but pulling this in to vba and then in to an array to do loads of calcuations when this is being pulled once per second it makes no sense and really slows down much of my program

I really want to put this straight in to vba so i have tried numerous options based around the following:

variable = Application.WorksheetFunction.RTD("name of server","","channel id")

I have tried several different versions of this inlcuding null string in stead of the "" and many different alternatives which are very similar. None seem to work all give me error messages

Unable to get the RTD Property of the worksheetfunction Class

I have looked through the forums on experts-exchange and found redmondb 's response,

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_27079041.html

 to a similar question used the same code as he had and got the same response the only changes i made was that i inputed the name of my rtd server and channel number (or topic)

I have attached the file that contains the basic code that there seems to be a problem with.

Thanks in advance
Orch-Test-2-AUTO-ENABLED.xlsm
0
Comment
Question by:chrismanncalgavin
  • 6
  • 5
11 Comments
 
LVL 26

Expert Comment

by:redmondb
ID: 38856274
Hi,  chrismanncalgavin.

(1) Try specifying the file type as well as the server's file name.
(2) Is your server returning a Date? It might be safest to define xRTD as a Variant.

Edit:
(3) Does it work in a cell? If so, please give us the formula.

Regards,
Brian.
0
 
LVL 8

Author Comment

by:chrismanncalgavin
ID: 38867847
Hi Brian,

1 - I am not quite sure what you mean by this I have tried these versions of that key line:

AVariable = WorksheetFunction.RTD("orchestrator.rtdserver", "", "DS1")
or
AVariable = WorksheetFunction.RTD("orchestrator.rtdserver", vbNullString, "DS1")
Or
AVariable = Application.RTD("orchestrator.rtdserver", "", "DS1")
Or
AVariable = Excel.Application.WorksheetFunction.RTD("orchestrator.rtdserver", "", "DS1")

None seem to work, but I don't think i have full understood this point.

2 - running before i tried xrtd as a variant I got nothing back from the server not even a date just the error message :

Unable to get the RTD Property of the worksheetfunction Class

After changing this I still don't get any output just this different error message:
Object does not support this property or method 438

3 - Yes this does work in the cell the formulae is:

= RTD("orchestrator.rtdserver",,"DS1")

this is where the last element, the DS1 is the identifier for the peice of data which i want to recieve. this gives me correct values.

I have spent ages tring different methods and looking up forums so any help is much appriceated.

Many thanks
0
 
LVL 26

Accepted Solution

by:
redmondb earned 500 total points
ID: 38869513
chrismanncalgavin,

(1) I am not quite sure what you mean by this
I was referring to the Server (second) parameter. What kind of a file is MSRtdserver? For example, if it's a Dll...
xRTD = WorksheetFunction.RTD("orchestraor.rtdserver", "C:\ORCHESTRATOR\BIN\MSRtdserver.DLL", "DS3")

(Excel's RTD Help - "The server argument is required in Visual Basic for Applications (VBA) (Visual Basic for Applications (VBA), even though it can be omitted within a worksheet.")

Regards,
Brian.
0
 
LVL 8

Author Comment

by:chrismanncalgavin
ID: 38892658
Hi Brian,

I am very sorry for this long delay but I have been trying to get your suggestion to work.

So I have been trying to get suppliers of the piece of software which links into this to tell me which file I need to referance because when I searched there program Files I could not find a, correct, dll file as you suggested.

After a long an arduose descusion with the supplier they have now said that this is not a feature which they actualy support and therefore it is exactly what we should do but we can't with there system.

So this is definatly the correct solution and thank you for all your help, will award points, but unfortunaltly a bittersweet victory.

Many Thanks,
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38893694
Thanks. chrismanncalgavin.

A few points...
(1) While it's nice to get immediate responses, a week is nothing to get upset over!

(2) I'm sorry to hear that the required functionality is not available. However, if you're willing to wait an average of 0.5 seconds (with a max. wait of 1 second) then we can almost certainly still do what you want. I've provided solutions for a number of people who were gettng RTD feeds for which there was no VBA support. By using a Calculation Event macro we can get code to run when an update comes through.
So, rather than requesting an update, the code would instead simply wait for the next update and process that.

Regards,
Brian.
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 8

Author Comment

by:chrismanncalgavin
ID: 38894217
Hi Brian,

Thanks for getting back. It was a little while ago now but i have run this kind of code for a time and unfortunaltly found that the computer that I was running this on was not coping very well, it would take me minutes to move between sheets.

I had set up the code with application.EnableEvents = False at the start and then turned back on after every exit point however the data i am getting in comes in every second, not a huge rate. and I included if statment which ensured only specific cells being changed actualy ran the full program.

however I have around 26 channels of data incoming and each data point was being collected in an array and then this array was averaged, saved and cleared every 10 seconds, 10 data points.

However this program will, if i get it running, be active for say at just the trial stages possibly a week at a time constantly taking data day and night.

with each data point I get in some are saved and some are proceesed and then saved every line of course has to be timestamped and dated.

The result was this commical slowing down of Excel, now I am no expert but although it sounds a lot of data this probably isn't huge for Excel but really i have no idea so it may be that it is due to the computer being not really ancient but certainly old.

So the next thing I tried was to use a timer to control the input of the data but this has a problem because the timer was always running and at some stage teh user will have to interface with excell to change variables and this crashes out the program.

The more I have been looking into it the more I realise that I am starting to need multi threading, so currently I am looking into using VB, as the manufacturer when they got back to me sent some syntax over which seems, to indicate that I could link this in with VB.

However If you have any suggestions on how this could opperate or even why th old program was so slow they would be much appriciated.
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38894448
chrismanncalgavin,

You're right, that does like far too little data to bring Excel to its knees. So something else is going on - please post the code you actually want to run.

the more I realise that I am starting to need multi threading
The server is single-threading its cell updates so it's down to how long it takes the server to process each update - if it's taking more than 1/26th of a second then you're dead. If it's taking 1/260th of a second then no problem.

the manufacturer when they got back to me sent some syntax over which seems, to indicate that I could link this in with VB.
If so then I would expect that VBA can handle it! Want to post that documentation here?

Thanks,
Brian.
0
 
LVL 8

Author Comment

by:chrismanncalgavin
ID: 38918253
Hi Brian

Sorry it has been a while.
So it continuous, after a slightly longer discussion with the supplier it has basically been cleared up that the reason why we were having problems writing the rtd data straight to vb and vba was in part because as the mentioned before they say they don’t support vba but also because they actually provide function with vb but only if you pay a small fee. The same amount as the entire starting software seems ridiculous to me but I won’t start on that.

So we decided that we wanted to use vb if we could, however as I have already mentioned we couldn’t do this with rtd unfortunately but we have settled for the less auspicious method of DDE and have finally just got that method to talk to vb. Would have liked to be able to use RTD but don’t want to pay the same again in software for the privilege

Thank you for everything I am sure it won’t be long before am posting again with another problem

Many thanks Brian
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38931695
Glad to be of some help, chrismanncalgavin.

Charging you the same again to use VB/VBA seems like a rip-off - assuming there's an annual component to their charge, I'd have a serious look at alternatives at the end of the year.

Finally, what about using a second instance of Excel exclusively for the feeds?

Regards,
Brian.
0
 
LVL 8

Author Comment

by:chrismanncalgavin
ID: 38950505
Brian,

Yeah, I had considered that but went down the vb route simply becasue I wasn't sure that you get two seperate instances of the vba code. Had no Idea whether this was or was not the case as when ever I open the vba window in excel I can always see all of the workbooks code so didn't really know whether they would defininatly operate seperatly or wheter everything excel runs in the background thorugh the same thread of vba.

Also because I was told that it could get a little messy if someone else had to use it.

Do they operate completely seperatly?

Many Thanks,
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38956227
chrismanncalgavin.

when ever I open the vba window in excel I can always see all of the workbooks code
This could be because...
(a) You have a single instance of Excel, but have set it to show all windows on the Taskbar.
(b) You have multiple instances of Excel, but have (deliberately or otherwise) opened one or more files in each instance.
While Excel is open, double-clicking a spreadsheet opens it in the same instance. To get a second one, you need to double-click on an Excel shortcut.

Also because I was told that it could get a little messy if someone else had to use it.
Do you mean that there may be a training issue or that, horrors!, it's a shared workbook?

Do they operate completely separately?
Almost! Many users have a file (e.g. Personal.xlsb) that opens automatically when they start Excel. If so, you'll get a message when you start the second instance ("Personal.xlsb is locked for editing") with options to open it read-only, to temporarily bypass it but be notified when the file becomes read-write or simply to bypass it completely.
(To see the separation, right-click on a sheet's tab and select "Move or Copy...". Click on the "To book:" drop-down - none of the files in the other instance will be shown.)

Regards,
Brian.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now