Link to home
Start Free TrialLog in
Avatar of cyimxtck
cyimxtckFlag for United States of America

asked on

Activate worksheet C# Excel

I have tried many things to try and get Excel to have a selected sheet in an Add-In.  So everything works in my code until I get to line: xlApp.ActiveWorkbook.Sheets[1].Activate();

Passing the name of the sheet to activate would be the best route to success but at this point I am trying anything to get this to work.

Here is some of the code that I have tried so far:

       const String Config = "Config";
        private Microsoft.Office.Interop.Excel.Application app = null;
        private Microsoft.Office.Interop.Excel.Worksheet worksheet = null;
       
        /// <summary>
        /// Read the config sheet cells
        /// </summary>
        /// <param name="Row">Row to Read</param>
        /// <param name="Col">Column to Read</param>
        /// <returns></returns>
        public String CellValueStr(
                                    Int64 Row
                                  , Int64 Col
                                    )
        {
            String cellValue = String.Empty;
            String CurrentSheet = Config;

            var xlApp = new Microsoft.Office.Interop.Excel.Application();
            Excel.Workbooks xlWorkbooks = xlApp.Workbooks;

            xlApp.ActiveWorkbook.Sheets[1].Activate();
            xlWorkbooks.Sheets[1].Activate();
            xlSheet.Activate();
           
            xlWorkbook = xlWorkbooks.Open(@"C:\filename.xlsx");



            //Excel.Worksheet sheet = (Excel.Worksheet)this.app.Worksheets[CurrentSheet];
            //Excel.Worksheet sheet = (Excel.Worksheet)ActiveWorkbook.Sheets["Sheet2"];

            //((Excel.Worksheet)this.app.ActiveWorkbook.Sheets[1]).Select();
            //((Excel.Worksheet)this.Application.ActiveWorkbook.Sheets[1]).Select();
            //sheet.Select(Type.Missing);

            //Worksheet sheet = (Worksheet)Workbook.Sheets["ASD"];
            //sheet.Select(Type.Missing);

            System.Data.OleDb.OleDbConnection MyConnection;

            MyConnection = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + tbFileName.Text + ";Extended Properties=Excel 12.0;");

            Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();

            //get the workbook
            Microsoft.Office.Interop.Excel.Workbook excelBook = xlApp.Workbooks.Open(tbFileName.Text);

            //get the first worksheet
            Microsoft.Office.Interop.Excel.Worksheet wSheet = excelBook.Sheets.Item[1];


            var xls = new Microsoft.Office.Interop.Excel.Application();


            xls.ActiveWorkbook.Sheets[CurrentSheet].Activate();

            xls.ActiveWorkbook.Sheets[1].Activate();
            xls.ActiveSheet();

            cellValue = (string)(worksheet.Cells[Col, Row] as Excel.Range).Value;

            return cellValue;
        }

Visual Studio 2015 and Excel 2016, 2013 and 2010

Please let me know if I am losing my mind as it seems none of the examples on this site work out?  (More coffee?)

So as an end result I would like to retrieve various values in certain sheets to perform actions based on these values.  The business currently has this functionality but it is written in VBA which is not going to work long term for us.

Thanks in Advance!
Avatar of AndyAinscow
AndyAinscow
Flag of Switzerland image

Does changing
xlApp.ActiveWorkbook.Sheets[1].Activate();
to
xlApp.ActiveWorkbook.Sheets[0].Activate();
make a difference (zero based indexing)
Avatar of cyimxtck

ASKER

Same exception I get with 1 or 0...

System.NullReferenceException was unhandled by user code
  HResult=-2147467261
  Message=Object reference not set to an instance of an object.
  Source=ExcelUpload
  StackTrace:
       at ExcelUpload.Helper.CellValueStr(Int64 Row, Int64 Col) in C:\Users\Train Man\documents\visual studio 2015\Projects\ExcelUpload\ExcelUpload\Helper.cs:line 37
       at ExcelUpload.Master.Cell() in C:\Users\Train Man\documents\visual studio 2015\Projects\ExcelUpload\ExcelUpload\Master.cs:line 31
       at ExcelUpload.Master.testwrite() in C:\Users\Train Man\documents\visual studio 2015\Projects\ExcelUpload\ExcelUpload\Master.cs:line 39
       at ExcelUpload.Upload.button1_Click(Object sender, RibbonControlEventArgs e) in C:\Users\Train Man\documents\visual studio 2015\Projects\ExcelUpload\ExcelUpload\Upload.cs:line 20
       at Microsoft.Office.Tools.Ribbon.RibbonPropertyStorage.ControlActionRaise(IRibbonControl control)
       at Microsoft.Office.Tools.Ribbon.RibbonPropertyStorage.ButtonClickCallback(RibbonComponentImpl component, Object[] args)
       at Microsoft.Office.Tools.Ribbon.RibbonManagerImpl.Invoke(RibbonComponentCallback callback, Object[] args)
       at Microsoft.Office.Tools.Ribbon.RibbonManagerImpl.System.Reflection.IReflect.InvokeMember(String name, BindingFlags invokeAttr, Binder binder, Object target, Object[] args, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParameters)
  InnerException:
The notion of Active... works only when the Excel application is visible.

Reference the workbook directly instead.

cellValue = (string) xlApp.Workbooks[1].Sheets[1].Cells[Col, Row] as Excel.Range).Value;

If you have many operations to perform in the same sheet, you might want to define variable that you will reuse for each operation

var mySheet =  xlApp.Workbooks[1].Sheets[1];
cellValue = (string) mySheet.Cells[Col, Row] as Excel.Range).Value;
In future please say what error you get should you have one, I expected the code to be running but not doing what you wanted.  My fault for assuming :-(  

You currently have:
xlApp.ActiveWorkbook.Sheets[1].Activate();
            xlWorkbooks.Sheets[1].Activate();
            xlSheet.Activate();
           
            xlWorkbook = xlWorkbooks.Open(@"C:\filename.xlsx");

Open in new window


Try opening a workbook first, does this change help?
            xlWorkbook = xlWorkbooks.Open(@"C:\filename.xlsx");
xlApp.ActiveWorkbook.Sheets[1].Activate();
            xlWorkbooks.Sheets[1].Activate();
            xlSheet.Activate();
           

Open in new window

I am guessing that you are converting a recorded VBA macro into C#. If so, the first thing you should realize is that recorded macros include a large number of .Activate and .Select statements--none of which belong in a professionally written macro. Please post the VBA macro so it can be optimized before you try to convert to C#.

You should also be aware that Microsoft is pushing JavaScript as the macro language of the future. JavaScript macros will eventually be able to run on any Excel endpoint (iOS, Android, Windows desktop, Mac OS X, Excel Online). Right now, you can use Visual Studio to develop JavaScript macros that work on Windows desktop in Excel 2016.
@Jacques here is the error I get with your code:

Severity      Code      Description      Project      File      Line
Error      CS0039      Cannot convert type 'string' to 'Microsoft.Office.Interop.Excel.Range' via a reference conversion, boxing conversion, unboxing conversion, wrapping conversion, or null type conversion      ExcelUpload      C:\documents\visual studio 2015\Projects\ExcelUpload\ExcelUpload\Helper.cs      40
@byundt

I cannot upload the VBA because it is something that I don't have.  Business requirements and a "locked" spreadsheet is what I have.

The task is pretty straight forward it seems but getting this to work is WAY more difficult than I would have ever expected.

Thanks,

B
If the workbook can be opened, but the VBA is password protected, I might be able to remove the password protection for you.
I appreciate the help along those lines and would take you up on that but....if that ever got out that happened (yikes)....would violate the terms of my contract.  :(

If I can just get this to read the cells I think I can pull it together based on the information that they provided me.
In that case, be aware that there is commercial software that can remove the password protection on VBA code, and you can find it easily with a Bing or Google search. The software should cost you less than an hour of your billing rate.
I did not spot the As Range at the end of the command. Try the following:

cellValue = (string) xlApp.Workbooks[1].Sheets[1].Cells[Col, Row].Value;
Here is what I am getting now:

An exception of type 'System.Runtime.InteropServices.COMException' occurred in ExcelUpload.dll but was not handled in user code

Additional information: Invalid index. (Exception from HRESULT: 0x8002000B (DISP_E_BADINDEX))

xlApp.Workbooks[1].Sheets = 'xlApp.Workbooks[1].Sheets' threw an exception of type 'System.Runtime.InteropServices.COMException'
I am a solid ADO guy so this isn't in my wheel house obviously....is there a "better way" to do this?  (if that is a dumb question, please disregard) LOL

COM always seems to have problems in my experience (which, admittedly, is very little).  This is very perplexing that the difficulty in what seems to be a fairly trivial task is staggering.

Thanks for all your help so far and very quick answers.
An invalid index exception on xlApp.Workbooks[1].Sheets seems to indicate that there is no Worksheet in the Workbook.

You can verify that by running the Excel application visible (xlApp.Visible = True)
There is a sheet there....I am running this from the IDE

1) run the project (F5)
2) Excel pops up and I select the saved spreadsheet
3) click in the Add-In ribbon and click the button I placed in there
4) walk through the code and let VS tell me what happens (F10/11, hovering, immediate window, etc.)

That error has popped up and not sure what is happening?  

So the file is saved, which will eliminate some non-sense that "Hey I cannot read that unsaved file"  The spreadsheet has 2 worksheets in there so it wouldn't choke on a zero based index....same error when I did this as well:

cellValue = (string)xlApp.Workbooks[0].Sheets[0].Cells[Col, Row].Value;
ASKER CERTIFIED SOLUTION
Avatar of cyimxtck
cyimxtck
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.