Hi Andrew,
Welcome to the Forum.
Your code is actually *extremely close* to being correct. Only a tiny subtlety is causing your code to have problems.
The key is this:
Code:
xlWorkBook = (Excel.Workbook) xlApp.ActiveWorkbook;
xlWorkBook.Worksheets.get_Item(1);
Your first line is successfully getting the active workbook (assuming that there is one!). Note that Application.ActiveWorkbook can return null, if there is no visible workbook, so you probably want to test for it. On most systems, creating a new Excel.Application also creates a new workbook by default, so you are fine, but it's usually best to check.
The second line is the problem. The second line is accessing the first Worksheet (the worksheet on the farthest left) in the Worksbook, but this is not necessarily the active sheet -- the user could have Sheet2 or Sheet3 selected, for example.
In order to get the active sheet, you are best off accessing it directly via Application.ActiveSheet and then casting to a Excel.Worksheet. It's best to cast using the 'as' operator in this case and then testing for 'null' because the active sheet can be a Chart sheet, which cannot be safely cast to a Worksheet. And if there is no active sheet, then the Application.ActiveSheet property will also return 'null'. So casting via the 'as' operator and then checking for 'null' is the way to go:
Code:
xlApp = new Excel.Application();
xlWorkSheet = xlApp.ActiveSheet as Excel.Worksheet
if (xlWorkSheet != null)
{
// Etc...
}
Ok, a couple of other very minor items:
(1) You should not make use of the Excel.ApplicationClass. You should, instead, utilize the 'Excel.Application' instead. So instead of:
Code:
xlApp = new Excel.ApplicationClass();
You should use:
Code:
xlApp = new Excel.Application();
For many things there is no difference, but you will hit problems at some point if you keep using the ApplicationClass. Use the Excel.Application instead. (Technically, 'Excel.Application' is an interface, so it seems confusing to use the 'new' keyword on it, but the Primary Interop Assemblies (PIAs) know how to handle this correctyl for you.
Ok, one last minor item:
Code:
xlWorkSheet = (Excel.Worksheet) xlWorkBook.Worksheets.get_Item(1);
The above works fine. But you'll be happy to know that the indexers work just fine with Excel, so you don't have to call the get_Item() accessor method the way you are. That is, you can just do this:
Code:
xlWorkSheet = (Excel.Worksheet) xlWorkBook.Worksheets[1];
You also might want to be careful of your line:
Code:
MessageBox.Show(xlWorkSheet.get_Range("A1", "A1").Value2.ToString());
The problem is that if the cell is empty, the call to 'Value2.ToString()' will be, effectively, a call to 'null.ToString()' and you will get a null reference exception. Instead, I would test for 'null' explicitly:
Code:
object theValue = xlWorkSheet.get_Range("A1", "A1").Value2;
if (theValue != null)
{
MessageBox.Show(theValue.ToString());
}
else
{
MessageBox.Show("The value is empty!");
}
Or, alternatively, you could use the static Convert.ToString() method to safely convert the object to string (returning an empty string if the object is null):
Code:
object theValue = xlWorkSheet.get_Range("A1", "A1").Value2;
MessageBox.Show(Convert.ToString(theValue));
Putting this all together, I think your code could look something like this:
Code:
Excel.Application xlApp;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
xlApp = new Excel.Application();
xlWorkSheet = xlApp.ActiveSheet as Excel.Worksheet;
if (xlWorkSheet != null)
{
xlWorkBook = (Excel.Workbook)xlWorkSheet.Parent;
}
else
{
// Either no active sheet at all (because no visble workbook)
// or the active sheet is a chart sheet (not a worksheet).
// Not sure what you want to do here! Up to you!!
}
object theValue = xlWorkSheet.get_Range("A1", "A1").Value2;
MessageBox.Show(Convert.ToString(theValue));
I hope this helps, Andrew. Good luck with it, it looks like you're on the right track...
,
Mike
ray:
