Excel to GH new service release

Replies to This Discussion

Permalink Reply by Nicholas Mundell on December 10, 2010 at 2:07pm

PS, This is the code Im using to send back to excel.

 

Dim xlApp As Object

''Grab a running instance of Excel  

 xlApp = System.Runtime.InteropServices.Marshal.GetActiveObject("Excel.Application")

 

''send volume and area to Excel    

xlApp.range("B12").Value = volume    

xlApp.range("B13").Value = area

 

 

 

Nick

Permalink Reply by Hashim Sulieman on August 4, 2011 at 1:35pm

Are you Nick of Stevens? Just checking. Hashim Sulieman.

Permalink Reply by Nicholas Mundell on June 28, 2012 at 8:28am

Yes Hashim!

P.A.E represent.

 

N

Permalink Reply by Damien Alomar on August 5, 2011 at 2:30pm

I believe that changes in the GH scripting note have caused a slightly different interaction with the Excel COM dll than what was before. Most of the objects/methods are fine, but you have to pay a little closer attention to what type is really being returned or the exact type that you're working with.  I'll explain below (BTW, this is in C# and I don't know if the VB.Net syntax/compiler causes things to behave differently).

 

The "old" way of doing things was fairly simple straight forward syntax (this assumes you already have gotten your workbook and worksheet references).

 

//Retrieve data from a given cell

someData = myWorkSheet.Cells[x,y];

// Assign data to a given cell

myWorkSheet.Cells[x,y].Value = someData;

 

Unfortunately this no longer works because of the object type that the mySheet.Cells[x,y] returns, which is akin to a base COM object type.  So in order to be able to do anything with this, you'll need to cast the result of Cells as a range, like so.

 

Range cellData = (Range)myWorkSheet.Cells[x,y];

 

Now that you actually have a data type that you can work with, you'd think you can just use the Value property as you could before, but not really.  For reasons unknown to me (and maybe this is just a C# thing), but there are separate methods for getting and setting a value; get_Value and set_Value* respectively.  I will note that I did not see these pop up with intellisense in the GH editor, but it did within Visual Studio.  So now the syntax above would expand to something like this.

 

Range cellData = (Range)myWorkSheet.Cells[x,y];

theRealData = cellData.get_Value();

 

To make things even trickier, there is one argument to the get_Value method of RangeValueDataType.  99.9% of the time, you won't care what that RangeValueDataType is, and because this is a COM dll, that argument is technically optional. This is definitely where there's likely going to be a C#/VB.Net split. C# does not have the concept of optional arguments, so you have to supply something.  VB does, so you can get away with calling get_Value without any arguements.  In order to mimic this behavior in C# you'd have to do something like this.

 

Range cellData = (Range)myWorkSheet.Cells[x,y];

theRealData = cellData.get_Value(System.Reflection.Missing.Value);

 

Hopefully that helps in actually getting to the data within Excel.  One last thing though.  The result of get_Value is an object, so you should probably cast that into the specific type that you're looking for.  My recommendation for this is that you cast the result of get_Value as a string (which its pretty much guaranteed to be), then use Parse functions for types like int and double.  See the code below (with some graceful failure for good measure).

 

Range cellData = (Range)myWorkSheet.Cells[x,y];

string theRealData = (string)cellData.get_Value(System.Reflection.Missing.Value);

try{

double myDoubleFromExcel = double.Parse(theRealData);

}

catch(Exception e){

Print(string.Format("{0} from Excel could not be parsed as a double.", theRealData));

return;

}

 

 

*looking over my notes, I don't explicitly say anything about set_Value, but I would be extremely surprised if A) it wasn't there, and B) its structure didn't mimic get_Value.  There is a possibility though, so if what I mentioned doesn't hold true for set_Value, don't say I didn't give you a heads up.