[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2653
  • Last Modified:

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

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
chrismanncalgavin
Asked:
chrismanncalgavin
  • 6
  • 5
1 Solution
 
redmondbCommented:
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
 
chrismanncalgavinAuthor Commented:
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
 
redmondbCommented:
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
chrismanncalgavinAuthor Commented:
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
 
redmondbCommented:
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
 
chrismanncalgavinAuthor Commented:
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
 
redmondbCommented:
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
 
chrismanncalgavinAuthor Commented:
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
 
redmondbCommented:
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
 
chrismanncalgavinAuthor Commented:
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
 
redmondbCommented:
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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now