Get the current / active worksheet

Get the current / active worksheet

• nitesh_garg

  This is not a very simple problem to solve.

  The problem is that Globals.ThisWorkbook.ActiveSheet returns a PIA object - i.e. Microsoft.Office.Interop.Worksheet. However, Controls collection is defined on Microsoft.office.Tools.Worksheet. Basically, you need to match the object you get back from ThisWorkbook.ActiveSheet to either Sheet1, Sheet2 or Sheet3 object (which you can downcast to Microsoft.Office.Tools.Worksheet and use Controls.AddListObject).

  Doing the match is is probably the least intuitive part. Unfortunately, VSTO does not define any collection (e.g. Microsoft.Office.Tools.Worksheets) which you can iterate later. So you need to come up with that on your own. The simplest thing to do would be to write something like this:

  object activeSheet = Globals.ThisWorkbook.ActiveSheet;

  Microsoft.Office.Tools.Worksheet activeVSTOSheet;

  if (activeSheet.Equals(Globals.Sheet1.InnerObject))
      activeVSTOSheet = Globals.Sheet1;
  else if (activeSheet.Equals(Globals.Sheet2.InnerObject)
     activeVSTO Sheet = Globals.Sheet2;
  else ....

  if (activeVSTOSheet != null)
      listObject = activeVSTOSheet.Controls.AddListObject(...);

   

  
  
  

• Andrew A

  Hi,
  
  Your guess is 100% correct, and I actually did your suggested logic before your reply, but the result is: Exception from HRESULT: 0x800A03EC
  
  The exception thrown when creating and return a new VSTO worksheet object in Globals.ThisApplication class.
  
  Thanks
  
  

• mig16

  Hi Peter:

  You access the current sheet using the "Me" keyword. So, you'd use a line such as this:

  Me.Controls.AddListObject(Me.Range("A1", "A5"), "MyListObject")

  Thanks!

  Mike Hernandez
  Community Program Manager
  VSTO Team

  
  

• elainel311

  Sorry, I'm not actually want to "create" a new worksheet dynamically, but just retrieve / cast the 'ActiveSheet' (yes, just 1 command as in VBA world) in VSTO 2.0 now, because 10 worksheets need to be created if user want to add 10 listobjects by using your suggested technique.

  However, if we can obtain the current / active worksheet object (native or VSTO one), then I can use AddListObject method directly.

  * BTW, I can't cast the Excel.Worksheet to a Excel OM by following the suggested sample: The "RuntimeCallback does not exist in the current context." display when I tried the code in my UserControl.cs file.

  Thanks for other suggestion.

  
  

• MiltGrin

  Hi Mike,
  
  How about if I want to execute the code on a User Control (ActionPane)
  this.Controls (control tree) is refer to System.Windows.Forms.Controls.ControlCollection only...
  
  Thanks,
  
  

• MMS2006

  Ah... this is not possible solution, because users may create new worksheet in their own wish, and what they want is just use my ActionPane button handler to return data (create listobject dynamically behind the scene), so the if-else case is not possible. Any other solution It's just 1 line of code like ActiveSheet as in VBA programming, huh OMG...

  Thanks anyway.

  
  

• Dongwei

  Hi Peter,

  The article below addresses your issue very well. It comes with a sample app too!

  http://msdn.microsoft.com/office/understanding/vsto/codesamples/default.aspx pull=/library/en-us/odc_vsto2005_ta/html/officevstodynamiccontrols.asp

  The sample application creates new Excel worksheet at runtime. Then it creates a VSTO Worksheet object to wrap the Excel worksheet object. Once you have a correctly constructed VSTO Worksheet object you can call AddListObject method on it to do what you need.

  If you run into any issues while implementing the solution, please start a new thread.

  Thanks

  
  
  

• mr hankey

  Dear Mohit,

  Thanks for bearing with me, I really need your help in solving this tech problem. I set a breakpoint (green line below) and the exception thrown at "red line".

  In my ThisWorkbook.cs file, I change private modifier to internal:

  internal Microsoft.Office.Tools.Excel.Worksheet GetExtendedWorksheet(
              ref Microsoft.Office.Interop.Excel.Worksheet nativeWorksheet) {

              // Get the IHostItemProvider instance.
                  Microsoft.VisualStudio.Tools.Applications.Runtime.IHostItemProvider hostItemProvider =
                      (Microsoft.VisualStudio.Tools.Applications.Runtime.IHostItemProvider)(RuntimeCallback.GetService(typeof(Microsoft.VisualStudio.Tools.Applications.Runtime.IHostItemProvider)));

                  // Create the new worksheet and return it to calling function.
                  return new Microsoft.Office.Tools.Excel.Worksheet(hostItemProvider,
                              RuntimeCallback,
                              nativeWorksheet.CodeName,
                              Container,
                              nativeWorksheet.Name);
             }

  In my user control file:

                  string strActiveCellLocation = Globals.ThisWorkbook.ThisApplication.ActiveCell.get_Address(true, true, Microsoft.Office.Interop.Excel.XlReferenceStyle.xlA1, null, null);
                  string strListObj = "Testing" + System.Guid.NewGuid();

                  Excel.Worksheet s = (Excel.Worksheet)Globals.ThisWorkbook.ActiveSheet;
                  Microsoft.Office.Tools.Excel.Worksheet ws = Globals.ThisWorkbook.GetExtendedWorksheet(ref s);
                  Microsoft.Office.Tools.Excel.ListObject DataIslandListObject = ws.Controls.AddListObject(ws.Range[strActiveCellLocation, Type.Missing], strListObj);
                  DataIslandListObject.AutoSetDataBoundColumnHeaders = true;
                  DataIslandListObject.ShowAutoFilter = false;

  What I want to do is to dynamically create a ListObject, databinded and then "Add" (insert more specifically) to the "Active Worksheet", but what I can do over the last 2 weeks is just create control on the fly, nothing else is working now.. :(

  Moreover, I catch the exception and below is the stack trace and exception details:

  Exception from HRESULT: 0x800A03EC
  Server stack trace:
  Exception rethrown at [0]:
     at System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage(IMessage reqMsg, IMessage retMsg)
     at System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData& msgData, Int32 type)
     at Microsoft.Office.Interop.Excel._Workbook.get_Container()
     at Microsoft.Office.Tools.Excel.Workbook.get_Container()
     at yyy.ThisWorkbook.GetExtendedWorksheet(Worksheet& nativeWorksheet) in C:\Documents and Settings\Peter Peter\My Documents\Visual Studio 2005\Projects\xx\yyy\ThisWorkbook.cs:line 219

  So I guess the exception is come from the incompatibility of Excel.Worksheet and Tools.Excel.Worksheet conversion, but this is critical to me for this task...
  

   

  
  

• shmulik_segal

  He's referring to the Excel Object Model.
  

• pdurbha

  If you do not need to use any of the databinding capabilities then just use what Excel OM

  What's OM

  Moreover, if I don't need databinding capability, then I won't use ListObject, which is supposed to be the best control introduced in VSTO 2.0 for databinding use. So, your assumption is wrong and the fact is I need to create ListObject dynamically, bind dataset to it and then AddListObject to the "active worksheet".

  Appreciate if you have any other solution.

  Thanks,

  
  

• gafrank

  Hi,

  Anyone know how to add Excel host controls on the "ACTIVE" worksheet dynamically

  Thanks,

  
  

• waddies

  Can you please debug and tell me what is the exact line of code that is causing this exception to occur Also, are you getting a valid Worksheet object when you are getting the ActiveSheet
  
  

• yeos_lee

  If you do not need to use any of the databinding capabilities then just use what Excel OM let's you do.

  Microsoft.Office.Interop.Excel.Worksheet sheet = (Microsoft.Office.Interop.Excel.Worksheet)ThisWorkbook.ActiveSheet;

  Microsoft.Office.Interop.Excel.ListObjects listObjects = sheet.ListObjects as Microsoft.Office.Interop.Excel.ListObjects;
  Microsoft.Office.Interop.Excel.ListObject newList = listObjects.Add(Microsoft.Office.Interop.Excel.XlListObjectSourceType.xlSrcRange,
  somerange, false, Microsoft.Office.Interop.Excel.XlYesNoGuess.xlYes, Type.Missing, Type.Missing);
  

  
  
  

• ChristianEhrlicher

  Hi Peter,

  You need to use a combination of the responses that people have provided.

  It is my understanding that you want to add a VSTO ListObject control to the Active worksheet and you are writing the code on a user control. If this is incorrect, please let me know.

  You can add a VSTO ListObject only to a VSTO worksheet and not the native excel worksheet. So, you will need to obtain a VSTO worksheet corresponding to the native excel worksheet that is the Active sheet. Take the code in the article pointed to by Apurva and add it to your ThisWorkbook class. Make the function internal instead of private. Putting the code in the ThisWorkbook class will resolve the compiler error that you are seeing.

  Then, in your usercontrol code, use the following code to get the VSTO worksheet for the active worksheet and add the ListObject:

  Microsoft.Office.Interop.Excel.Worksheet activeSheet = Globals.ThisWorkbook.ActiveSheet;

  Microsoft.Office.Tools.Excel.Worksheet extendedSheet = Globals.ThisWorkbook.GetExtendedWorksheet(activeSheet);

  Microsoft.Office.Tools.Excel.ListObject listObject = extendedSheet.AddListObject(.......);

  listObject.SetDataBinding( ...... );

  Hope this helps.

  
  
  
• Get the current / active worksheet