[RESOLVED] Adding an Excel Worksheet in an Automation Addin

[shed]

Hello All,

Basically i'm trying to write an Automation Addin that implements the IDTExtensibilty2 interface which allows my Addin to receive a reference to the Excel Object the Addin is running on. I can do this all fine and i can use the Excel reference to return and set cell values on the active worksheet so i know that the reference is set right.

However the first thing my Addin needs to do is add a new worksheet to the workbook so i used the following code (moXL is the reference to the Excel object) :

moXL.Worksheets.Add After:=moXL.Sheets(moXL.Sheets.Count)

but this does nothing! Is it not possible to create new worksheets from within an Addin or could i be missing something?

Cheers.

[si_the_geek]

Hi shed, welcome to VBForums!

I'm asumming that moXL is an application object, in which case you have made a common mistake in the arrangement of objects in Excel.

The Application object contains WorkBooks, and each of these workbooks contains WorkSheets; the application itself does not directly contain worksheets.

If you already have workbooks, you need to add your sheets to them; otherwise you need to create new workbooks.

An example of adding a workbook, and a worksheet to it:

Dim moXLBook as Excel.Workbook
  Set moXLBook = moXL.WorkBooks.Add
  moXLBook.WorkSheets.Add After:=moXLBook.Worksheets(moXLBook.Worksheets.Count) 
 
'or: 
  moXL.Workbooks(1).Worksheets.Add After:=moXL.Workbooks(1).Worksheets(moXL.Workbooks(1).Worksheets.Count)

[shed]

Thanks for the welcome Si

I tried your suggested code but it produces the same response. I noticed it does actually change the active worksheet to the last one in the workbook but does not then add a new one. Both my original code and your code works in VBA (obviously substituting moXL for Application) as a simple macro as expected but not in my VB addin.

This is what's making me think that maybe Automation Addins cannot perform such operations

[si_the_geek]

Ok, what may be happening is that it is being added, but is not being displayed. Try this:

Dim moXLBook as Excel.Workbook
Dim moXLSheet as Excel.WorkSheet
  Set moXLBook = moXL.WorkBooks.Add
  moXLBook.Windows(0).Visible = True
  Set moXLSheet = moXLBook.WorkSheets.Add After:=moXLBook.Worksheets(moXLBook.Worksheets.Count) 
  moXLSheet.Visible = xlSheetVisible

[shed]

Ah, i've sorted it now by redoing the project as a COM Addin rather than an Automation Addin. I'm still a bit baffled as to why it wasn't working before but never mind.

Thanks for the help.