ActiveX Office Spreadsheet 9.0

[GaryN]

I'm trying to use this ActiveX object in a simple program to show a help file we have written in Excel. All I want to do i open and close the excel spreadshet and put the data onto the Front Panel. The best way I've seen to do this would be to put the data into the Microsoft Office Spreadsheet 9.0 object because this behaves much like Excel on the FP. So I've opened retreived the data successfully, I select the range and everything is fine, I go to write the data with the Value property and I get an error. I don't know if I missed a step or what but I can't figure it out. I've attached an image of the section of code in question. Any help would be greatly appreciated.

[unclebump]

Here is a spreadsheet object that reads the cell values. I made a quick modification to write to the activecell. I'm not sure if your error comes from not turning the data into a variant or your row and column cell selection. See if you can get the row/column selection to change the active cell, then you know the format works.

[GaryN]

---

@unclebump wrote:
Here is a spreadsheet object that reads the cell values. I made a quick modification to write to the activecell. I'm not sure if your error comes from not turning the data into a variant or your row and column cell selection. See if you can get the row/column selection to change the active cell, then you know the format works.

---

I'm looking at your VI and I understand what you're doing but I can't see it in the terms of my application. For one, I'm trying to write data to the spreadsheet file but it's two-dimensional. As opposed to writing to an active cell as you have, I need to write to an entire range. I suppose it could be written to do that using loops but one of my sheets has 600 rows or so and the others have approximately 200 which makes looping an unattractive option. After the row column selection I have no errors, though that doesn't necessarily mean everything was fine there. I've inserted a "To Variant" but still the same error occurs. Thanks for the ideas.

[unclebump]

After your row and column selection, put in an invoke node and choose the select property element. I think this will change the activecell to another position. Just to test the row and column format.

[unclebump]

Maybe the download at this link will help.

http://support.microsoft.com/kb/q258187/

http://support.microsoft.com/default.aspx?scid=kb;EN-US;306022

Office Web components version 11. May have better documentation.

http://www.microsoft.com/downloads/details.aspx?familyid=7287252C-402E-4F72-97A5-E0FD290D4B76&displa...

[GaryN]

What is the nature of variant data? It seems to not like my attempts at converting Variant data to a 2-D array. I'm toying with a few ideas now and if I put info into the spreadsheet and read it out, itgives me an error when I convert the data to an array of strings using the variant to data vi. I've wired the type as a 2-d array but to no avail. Any suggestions? Something with the variant data and what I'm doing to it seems to be the problem.

When I read the 2-D data off of the spreadsheet on the FP, the variant indicator I created only displays the first cell. Nothing else.

Message Edited by Gary D on 08-01-2005 01:01 PM

[unclebump]

Variant data can be anything. All data types can be turned into variant data. It can be difficult when trying to convert from variant to an unknown data type. What happens if you try to convert to a single string. Maybe your cell reference isn't really a 2D range??

[GaryN]

---

@unclebump wrote:

Variant data can be anything. All data types can be turned into variant data. It can be difficult when trying to convert from variant to an unknown data type. What happens if you try to convert to a single string. Maybe your cell reference isn't really a 2D range??

---

It works as expected with one cell. My range I'm trying to grab for the time being is just something simple like A1:B2. I wired a two dimensional array with a string type to it and I get a type mismatch when I run the program. I've initialized the array I'm wiring to the variant to data vi as a 2x2 array. Still I get a type mismatch. Like I said though, it works fine with one cell and the variant probe or indicator just shows: Value -> a1. Nothing about the other celss at all.

EDIT: It seems to be only taking cell 1. Even if I put in a 2-D range (either in A1:D4 format or as a Cell 1, Cell 2 deal) I get just a1. If I wire a single string to the variant to data vi it still works fine though I'm supposedly selecting a 2-D range. I think if I can find a way to select the proper range on the ActiveX MS Office Spreadsheet 9 object, that might fix my problem. But since I'm already at the range property, I don't know what else there is I can do.

Message Edited by Gary D on 08-01-2005 01:54 PM

[unclebump]

Here is a range select option. It isn't using absolute addressing for some reason, I think there is a parameter for that.

[GaryN]

---

@unclebump wrote:
Here is a range select option. It isn't using absolute addressing for some reason, I think there is a parameter for that.

---

I tried your select program and here's what I noticed on my computer. Whenever I run it, it selects a 3x3 area starting with the current cell. Whatever I put in the range it really doesn't matter.

Thanks for the help, I'm gonna keep trying but I feel I've stolen your day. This is really a frustrating problem because I can just dump the data into a table and be done with it, but that makes it much more difficult to use (no scrolling and difficult to navigate). Using the Excel interface would be much better for my purposes. Thanks again.

P.S. I'll still be checking the post for any other input. So don't be bashful people.