Running C# code from excel which upload data in the SAME excel spreadsheet

Question

Good evening all,

After a few hours trying to get around the problem I though I might post here as I could not find a solution.

My goal is to:
1 - Start Excel (some C# libraries would be registered)
2 - Type a formula which will call some C# functions: I was actually thinking about two embedded functions DisplayInExcel(CalculateValues(parameters))
   i -> CalculateValues(parameters) return an Array
   ii -> DisplayInExcel takes an Array as parameters and display it in excel from the Cell calling the function
3 - The C# function will display some values in excel (as in i/ii or by any other way)

But I'm digressing my problem is that I can't manage to get the current open workbook in C# and update it.

The code I'm using actually opens a new version of the spreadsheet and will display it once the original one is closed (which is not a very interesting behaviour as I would have to re-open the spreadhseet for each update) :

public void WriteInExcel() //Excel.Workbook xlExcelWorkbook
        {
            Excel.ApplicationClass xlExcelApp = new Excel.ApplicationClass();
            Excel.Workbook xlWorkBook;
            Excel.Worksheet xlWorkSheet;
            Excel.Range xlRange;            
            string sPath = "C:\\Test\\AutomationAddin\\AutomationAddin\\TestAutomation.xls";
            
            xlWorkBook = xlExcelApp.Workbooks.Open(sPath,
            Type.Missing, Type.Missing, Type.Missing, Type.Missing,
            Type.Missing, Type.Missing, Type.Missing, Type.Missing,
            Type.Missing, Type.Missing, Type.Missing, Type.Missing,
            Type.Missing, Type.Missing);

  
            xlWorkSheet = (Excel.Worksheet)xlWorkBook.ActiveSheet;
            xlRange = xlWorkSheet.UsedRange;

            int iCol = xlRange.Column;
            int iRow = xlRange.Row;

            xlRange.Value2 = 5;

            for (int i = 1; i < 10; i++)
                xlWorkSheet.Cells[i, 5] = i;
        }

Is there any alternative from xlExcelApp.Workbooks.Open
Like xlExcelApp.Workbooks.GetActiveWorkBook?

Or would it be possible to pass the ActiveWorkbook from excel? I tried that by modifying the function:
public void WriteInExcel(Excel.Workbook xlExcelWorkbook)

But then when I call it from excel:

Public Sub WriteInExcel()
Dim oCommExample As CommExampleTest
Set oCommExample = New CommExampleTest
oCommExample.WriteInExcel(ActiveWorkbook)
End Sub

I have an error from a type mismatch.

I would really appreciate any help.

Cheers,
Josselin

Answer 1

Hi,

For discussions relating to excel developing, I recommend you create a new thread at :

http://social.msdn.microsoft.com/Forums/en-US/vsofficetools2008prerelease/threads
or 

http://www.microsoft.com/communities/newsgroups/en-us/default.aspx?dg=microsoft.public.excel.misc&cat=en_US_34a4dcef-6aac-414e-9881-2ba33de8be2f&lang=en&cr=US

You will get better and quicker responses from the experts there.

Best regards,
Harry

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.

Answer 2

Thanks Harry,

I posted on teh VSTO forum.

Cheers,
Jos