[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

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

Posted on 2013-02-05
11
Medium Priority
?
2,555 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 2000 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

650 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