Returning Excel variables C#

Question

I have a Windows Forms application with 5 methods (each based off of the user clicking a button). In each method, I would like to open the same excel file the same way. However, in each method I want to select a different range on the worksheet. I tried creating a function to open the excel file rather than rewriting it 5 times...

// method to open Excel and load a the workbook based on date selected.
public Tuple<Microsoft.Office.Interop.Excel.Application, Workbook, Worksheet> openExcel() 
{
    Microsoft.Office.Interop.Excel.Application excelObj = new Microsoft.Office.Interop.Excel.Application();    
    string fileName = @"C:\Users\" + userName + @"\Documents\Visual Studio 2015\Projects\ProgramForMom\ProgramForMom\bin\Debug\Excel Files\" + frm2.year.Text + " Expenses"; 
    Workbook wb = excelObj.Workbooks.Open(fileName, 0, false, 5, "", "", false, XlPlatform.xlWindows, "", true, false, 0, true, false, false); 
    wb.Activate();  // Activates file.
    Worksheet ws = wb.Worksheets[frm2.month.Text];   
    ws.Activate();  
    return Tuple.Create(excelObj, wb, ws);
}

All that works fine.

I tried referenced this function in one of the methods...

var excelObj = openExcel();
Workbook wb = openExcel();
Worksheet ws = openExcel();
var cellValue = ws.Range["A1"].Value2;

and I get an error saying...

"Cannot implicitly convert type 'System.Tuple' to 'Microsoft.Office.Interop.Excel.Workbook'. An explicit conversion exists (are you missing a cast?)"

I get the same error for the worksheet. It says the same exact thing just substitutes the word worksheet in place of workbook.

Can you please explain what I have done wrong? Thank you.

Joel Coehoorn · Accepted Answer · 2016-07-17 23:49:07Z

2

var result = openExcel();
var excelObj = result.Item1;
Workbook wb = result.Item2;
Worksheet ws = result.Item3;
var cellValue = ws.Range["A1"].Value2;

answered Jul 17, 2016 at 23:49

Joel Coehoorn

418k114 gold badges582 silver badges820 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Michael Over a year ago

This also works. So both this solution and the one posted by Mong Zhu are very useful. Just wanted to let people know.

Mong Zhu · Accepted Answer · 2016-07-18 18:24:36Z

1

You have a mismatch between the return type of your method (which is a Tuple) and the type of the variables in which you want to catch the output of openExcel

it should look more like this

Tuple<Microsoft.Office.Interop.Excel.Application, Workbook, Worksheet> allThreeInOne = openExcell();

then you can try and fiddle everything apart... OR

what you also can do is to access the value right at the point of the function call:

var excelObj = openExcel().Item1;
Workbook wb = openExcel().Item2;
Worksheet ws = openExcel().Item3;

this way you would assign exactly the matching type to the variables

EDIT: Tha latter solution is not advisable since you would unnecessarily open the file 3 times just to get the result that you would have gotten already from the first call,as Joel Coehoorn correctly pointed out.

fiddling the tuple apart would be the way to go:

var excelObj = allThreeInOne.Item1;
Workbook wb = allThreeInOne.Item2;
Worksheet ws = allThreeInOne.Item3;

edited Jul 18, 2016 at 18:24

answered Jul 17, 2016 at 22:04

Mong Zhu

23.8k10 gold badges47 silver badges81 bronze badges

6 Comments

Michael Over a year ago

Perfect! Thank you so much. Exactly what I needed!

Mong Zhu Over a year ago

you are welcome. Tuples are quite a nice tool to gather information for a short time in one place

Michael Over a year ago

Yes definitely. I saw this one post where somebody wanted to create 3 functions or use global variables to achieve something similar to my problem. Your solution is way better! Very easy, neat and time saving.

Mong Zhu Over a year ago

why don't you actually create one method to load the excel file and pass the different ranges as parameters? this way you could determine with one method which part of the workbook to load and return only the stuff that you need

Michael Over a year ago

I have about 7 different ranges. Is it still worth it. If so, could you show how I would do that please?

|

Collectives™ on Stack Overflow

Returning Excel variables C#

2 Answers 2

1 Comment

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related