Excel not calculating formula, so when I go to read the cell with the formula in it I get an error

[acalmeyn]

What I'm doing is write to a cell then reading a cell that reference the cell I wrote to. I can read cells with formula that are multiplication or division,but cells with HEX2DEC formulas will give me error.

I believe this is happening because Excel didn't recalculate the new valves in the formula cell. If you open and close the file and then “save on exit”. Next time you read the file it will work every time. I attached an example program and excel file so you can see what I mean. Just place the excel file in c:\temp\ and run the program(Labview 7.1)

[Otis]

I've been looking at this file for awhile and I think I am finally starting to hone in on what the problem is. Turns out the real issue isn't related to the reading of the file at all. The reason this error appears is because the fields that we are trying to read have error value of #NAME?. That's why the ActiveX throws an error and won't let those values be displayed.

What seems to be happening is whenever the "ActiveX Write Rows to Excel.vi" is called that it somehow seems to be disabling the Add-Ins (particularly the "Analysis ToolPak" and "Analysis ToolPak - VBA" Add-Ins, which are where the function HEX2DEC is created). The problem is located at the property node for "Range" found within the For Loop. In fact, if you don't wire any data to this function, then the whole program works just fine.

I'm still working on finding out what exactly the issue is, but I thought you might like to know that I've narrowed down the issue that we're looking at.

[acalmeyn]

Wow, nice work. I would have never figured that out. I've been mess with this on and off for 1 week. I final gave up and place this post up. I'm glad I did.

How that I know that is happening I will start looking to.Let me know if you come up with anything.


Thanks again for your help

[Otis]

acalmeyn,

I have continued looking into this, and I think the easiest thing to do is to replace the Excel Write to Row with a known working function. One such example that I know works can be found here (Write Data to the Active Cell in Excel Using ActiveX).

I went ahead and tested this file and it writes the data properly to Excel and doesn't show the peculiar property of requiring the reinitialization of the Add-Ins "Analysis ToolPak". I do know that LabVIEW provides access to the ActiveX commands; however, I cannot figure out which of the commands that is being called is causing Excel to behave in this peculiar manner.

[acalmeyn]

When I try to pull up your link I keep getting a page that will not display. These is the link it is going to

http://venus.ni.com/stage/we/niepd_web_display.DISPLAY_EPD4?p_answer=&p_guid=E0ED308E0FB65285E034080020E74861&p_node=%20&p_rank=&p_source=Internal&p_submitted=N

I'm using IE 6.0

Thank again for your help. Just knowing it possible is a load off my mind.

[Otis]

For some reason that site seems to be down for maintenance at the moment. Here is what it would have said had the link worked.

Write Data to the Active Cell in Excel Using ActiveX

This VI will open Microsoft Excel and write an array of random numbers to the active cell (the cell which is selected). This is all done using ActiveX in LabVIEW. The active cell will be the upper left hand corner of the array.

Directions for using this VI:

1. Select to either create a new Excel sheet or open an existing one by using the Create/Open Excel toggle switch.
2. Select how many rows and columns that you want to write to Excel.
3. Click on the Open/Create button. This will either open a new Excel sheet or allow you to open an existing one.
4. When Excel opens, select which cell you want to write to by clicking on it. You can then minimize Excel and go back to this VI.
5. Click on the Write to Active Cell button. This will write the random number array to whichever cell you selected in Step 4.

[acalmeyn]

I was unable to get your Vi to write data to the cell of my choosing. So I was unable to verify that it worked. I think you were able to get your vi to work is because you had excel open in the back ground.

I have found that if I open excel manually and leave it open my program will work every time. So I think you were correct in thinking that my program wasn't working because the addin (analysis tool pak) was not being called by excel before I would try to read a cell that used a DEC to HEX formula.

So my question is-- Labview has a (active X function)  property node under application called ADDins. Is there anyway to use this to call in the (analysis tool pak) before I read the cell? I have tried to figure out how to use this but have had no luck.

Thanks for you help

[acalmeyn]

I have figured out how to activate the addins. I'm also able to ask excel if the are installed and, it return with a yes. Is there something you need to do to enable them? Excel is still not using the addins in the calculations. I have tried the worksheet "calculate".

[Ankita]

Hi,

Could you elaborate on how you are activating and checking for activation of Excel Add-Ins in LabVIEW? I thought that Analysis Toolpak addins got enabled when you activated them. Can you post a VI that shows that Excel Addins have been activated but that still gives wrong results for some mathematical calculations requiring Analysis Toolpak?

Ankita

[acalmeyn]

Here is the VI's and excel file that I'm using. What you can do to test is run the main vi which will run the  (write to excel vi) then run the (read excel vi). Stop the program the error occurs in the (read excel vi). Then look at the excel sheet that is being written to and then go to tools addins disable the addin and the rein able the addins. You will then see as soon as you reenalbe the addins the dec to hex conversion will convert.