I ask here to see if any expert in vb can give me a cable. In my company we have software that monitors the installation. It has infinity of data which is updated every few seconds and data is stored in the database. For security reasons they do not give us access to that database but they have created a small application (app.exe) that acts as a connector/bridge and allows inserting dynamic links in excel with each of the application's data. In VBA, what I do is add the data/variables of the application to a sheet and, keeping app.exe open, the data is updated at the same rate as the software. I, in vba do this:sht.Cells(2, 4).FormulaArray = "=DDEAPP|VAR_V!TRION_ST"
and so I insert in that cell the dynamic link. (Remember that for a value to be displayed, the app.exe application must be running on the computer. Otherwise, #ERROR is displayed)
And here comes my question: would it be possible to capture those values without having to insert them into a cell? Is there any way to do that in VBA? I have asked the IT department but they think I want direct access to the database and for security reasons they do not give that access. I don't know if there is any way or method with which to try to connect to that app.exe and request the specific data that it needs and store it, for example, in an array.
Do you think it's possible? Do you have any ideas?
Thanks.
Edited to add some more info: All formulas/variables have things in common, for example they are all made up of DDEAPP|VAR_V!. I put six of these variables so that you have something clearer about this:
AT1 =DDEAPP|VAR_V!PIC_A1
AT2 =DDEAPP|VAR_V!PIC_A2
AT3 =DDEAPP|VAR_V!PIC_A3
AT4 =DDEAPP|VAR_V!PIC_A4
AT5 =DDEAPP|VAR_V!PIC_A5
AT6 =DDEAPP|VAR_V!PIC_A6
Comment that these variables are obtained from a company application. Said application shows panels with graphs and data of each zone and has an option to "extract" that data in the form of a dynamic link. I go to the desired data and with a button that says "Dynamic link" the link is copied into memory. Then in Excel I use Paste Special > Paste Links and that variable appears in a cell.
I can't upload a screenshot because I have those urls covered but to give you an idea. With the variable AT1 as an example:
So I insert the formula with vba into the cell:
sht.Cells(1, 1).FormulaArray = "=DDEAPP|VAR_V!PIC_A1"
The return value of all variables is a number.
In the formula bar, it appears like this:{=DDEAPP|VAR_V!PIC_A1}
I have also been looking at what another user proposes, DDEInitiate and DDERequest, but I am not sure what parameters to use, since I do not have any documentation of that app.
DDE (Dynamic Data Exchange), is a technology that allows the exchange of data between applications under windows, using "connectors". It is now obsolete because it had serious security flaws, but it is still available and can be very suitable in controlled or closed environments.
As I have been able to deduce from your question, the app that connects your excel with the application that provides you with the data would be the DDE connector, since the structure of the "formula" that you put indicates this.
If so, you can use the available DDE methods to assign the returned value to a variable, and work with it however you like. To do so, following the example code that you can find in the official Microsoft documentation, you should use the following parameters:
Without access to the app and your sheet, it's impossible for me to test if it works, but you should be able to access the value you want with no problem.
You must also take into account the speed at which that data is provided by the app (about 10 ms), which is usually much faster than what excel can offer to display the data in a cell (about 120 ms), for whatever calculations you do may show inconsistencies.
I hope I have put you on the path of the solution.