Create, Read from Excel Template

[MeCoOp]

I'm trying to add to an existing program that uses the Microsoft Tool-kit vi's to export data to an Excel template where various other calculations are carried out.  Now I would like to read fromt the same spreadsheet to do more calculations.  I'm having trouble linking the path of the template just created to the sub vi that extracts the data from excel.  Any suggestions?

Thanks.

Bobby

[smercurio_fc]

All of the subVIs are missing, so it's no clear what's doing what. Which is the VI that is supposed to extract the data?

A few side-points:

• It's highly unusual to hide the border of a tab control. As it is, at first I thought those big bold red words were just labels. I was surprised to find those were the tab pages. I would question the user interface "usability" of doing this.
• You do not need any of the local variables that you have for Test1, Test2, etc. You just need the controls outside the case and use wires.
• There seems to be repetition in the case that is apparently used to run tests, but a different subVI is used, T12, T22, etc. Are these subVI doing the same thing, but have different names?
  

[MeCoOp]

Thanks for taking a look at my vi.  I usually have a shaded background for my VI's, not to say that changes the way the tab control is viewed by the user but I've yet to have a complaint.  I see how your point 2 is definately a better way to code.  The subVI's, Test 12, Test22 do the same thing in essence but point to different cells in Excel.  I'm not sure how to package all the Vi's in a zip so I'll just attach the most pertinent ones that should help illustrate the problem I'm having.

Message Edited by MeCoOp on 05-06-2008 01:16 PM

[smercurio_fc]

I still don't understand where the exact problem is that you're having. Is it that the "GetXL_CellValues_2D_Dbl" VI isn't working, or that the path that the VI receives is wrong? What is the path that the VI is receiving?

If your Test 12, Test22 do the same thing in essence but point to different cells in Excel, then a better solution is to have a single VI with a numeric input to indicate the test number so you can specify the cells based on the test number. This way you have one VI instead of 6.

[MeCoOp]

Yes, the "GetXL_CellValues_2D_Dbl" VI doesn't get the correct path.  I get '<Not A Path>.xls'  in the file directory field.

[smercurio_fc]

Well, you're trying to use the Close File function on a report. To close out a report you should use the Dispose Report VI. It seems that you just need the reference to the sheet. That's essentially what you're doing in the "GetXL_CellValues..." VI. If you open the Dispose Report VI you will see that it calls the Generate Report Get Data to Modify VI. You can use this VI to get the reference to the sheet. Thus, your GetXL_CellValues... VI just needs a "report in" control that you feed to the Generate Report Get Data to Modify VI and you can get the sheet from that. Make sure you dispose of the report after you're done, rather than at the point where you're doing it now.

[MeCoOp]

Ok, with some work I was able to get that to work so thank you very much for your assistance.  Can I ask how you knew to open the Dispose Report VI to get to the Generate Report Get Data to Modify VI?  Just trying to understand your thought process so maybe next time I can figure it out on my own. 

Also, now that I have the program reading from Excel I run into another hurdler.  Each row of data can have a maximum of 7 values.  If the value is below a certain threshold Excel automatically puts a dash in the cell, this of course causes problems in LabVIEW.  Is there a work around for this since I can not predict where the dash may appear?

[smercurio_fc]

---

Can I ask how you knew to open the Dispose Report VI to get to the Generate Report Get Data to Modify VI?

---

I always look inside of VIs to see what's going on under the hood. In this case, it was mostly a guess. I figured that the Dispose Report would have to be closing Excel, and to do that it needed the ActiveX references.

---

If the value is below a certain threshold Excel automatically puts a dash in the cell, this of course causes problems in LabVIEW.  Is there a work around for this since I can not predict where the dash may appear?

---

I'm assuming your template is doing this. What specific problems are you having on the LabVIEW side? Is is at the reading of the cell values, or when you're trying to write out the values? Or, are you trying to do something else with the values that you read from the sheet?

[MeCoOp]

Yes, when I try to read a cell value that has a dash in it i get an error saying there is a type mismatch.

[smercurio_fc]

The problem is in the Variant To Data. You need to use a 2D array of strings in this case because you have dashes. Then, you can use the Fract/Exp String to Number function to get a 2D array of numbers:



Note that in this case the dash gets changed to a zero. Not sure if this is OK for your dataset. If it isn't, you can replace the dashes with "NaN" before feeding it to the Fract/Exp String to Number function. Then, when you do the conversion to numbers you'll get NaN instead of zero.

Also, I think the constant of 0 wired to the Index input for getting a specific sheet should be 1 since the sheet index numbers are 1-based, not 0-based.


Message Edited by smercurio_fc on 05-06-2008 05:11 PM