Listing all worksheets in a workbook

[PaulUK]

Hi,
Are there any examples of looping through the workbook and getting the
names of all the worksheets please?
Thanks.

[HR...@telefonica.net]

This should work,

 

Sub GetNames()

Dim i As Byte
Dim arrSheetsnames() As String

 

i = ThisWorkbook.Sheets.Count

ReDim arrSheetsnames(i)

For i = 1 To ThisWorkbook.Sheets.Count
   
    arrSheetsnames(i) = ThisWorkbook.Worksheets(i).Name

Next i

End Sub

 

regards

HR

[Govert van Drimmelen]

Hi Paul,

The code posted in reply would work from VBA, but we need some minor
changes to make it work from an Excel-DNA macro. In particular, we
need to get hold of the Application object, and use ActiveWorkbook
instead of ThisWorkbook, since the code will not be associated with a
particular workbook.

There is something else I need to address. Excel has two interfaces,
the COM automation interface familiar from VBA and a C API described
in the Excel .xll SDK. Excel-DNA gives you the opportunity to use
either API.

To use the COM automation interface, you need some way of getting hold
of the root Application object (this is 'magically' always around in
VBA, and the default object when resolving a token like ThisWorkbook).
From a macro in Excel-DNA, you get hold of the right Application
object by accessing ExcelDnaUtil.Application. From there the object
model is accessed as usual.

To use the C API, Excel-DNA defined a type called XlCall (after the
xlcall.h header file in the SDK). To call C API functions, you use
XlCall.Excel(XlCall.xlcXXXXX, param1, param2...).

The XlCall.xlfXXX and XlCall.xlcXXX constanst refer to the various C
API functions, which match the Excel 4.0 macro functions, and are
documented in the help file that is available from Microsoft here:
http://www.microsoft.com/downloads/en/details.aspx?FamilyID=c09bf7f7-d30e-4ce9-8930-5d03748ca5cd&displaylang=en.

As an example, to get the worksheets in a workbook, you use the
GET.WORKBOOK information function (xlfGetWorkbook). When passed the
first parameter '1', if will return an array with all the sheets in
the workbook. A fairly complete example of accessing the GET.XXX
information functions is in the Distribution\Samples\GetInfoAddin.dna
file, and the matching GetInfoSample.xls book. To load these, just
make a copy of ExcelDna.xll in the same directory, rename to
GetInfoAddin.xll and open.

For the COM automation style: below is a complete .dna file with
VB.NET code which adds a menu and macro to dump the list of sheets in
the active workbook to a new sheet.

Regards,
Govert

<DnaLibrary>
<![CDATA[
' Importing the ExcelDnaUtil type allows the
' direct call to the Application property work.
Imports ExcelDna.Integration.ExcelDnaUtil
Public Module Test

<ExcelCommand(MenuName:="Test", MenuText:="List Sheets")> _
Sub GetNames()
Dim i As Integer
Dim count As Integer
Dim sheetNames() As String
Dim wb As Object
Dim sh As Object

' Get hold of the application object and active workbook
wb = Application.ActiveWorkbook

' Get sheet names into an array
count = wb.Worksheets.Count
ReDim sheetNames(count)
For i = 1 To count
sheetNames(i) = wb.Worksheets(i).Name
Next i

' Dump sheet names to a new sheet
sh = wb.Worksheets.Add()
For i = 1 To count
sh.Cells(i, 1).Value = sheetNames(i)
Next i
End Sub

End Module
]]>
</DnaLibrary>



On Jun 3, 9:28 am, "HR...@telefonica.net" <HR...@telefonica.net>
wrote:

[Paul Beeston]

Thanks very much for your help guys.
I forgot to mention I am working with C# but the translation from your VB to C# is simple.
I did try using: XlCall.Excel(XlCall.xlfGetWorkbook, 1, workbook) to return the names of the worksheets and now I have 2 more questions please!

1) the function argument above I have called "workbook" should that be:
    a) the name of the workbook as a string e.g. "workbook1.xlsx" or
    b) an instance (or whatever the static version of a variable is called) of the open workbook as in:
   
    static Excel.Application excelApp    = (Excel.Application)ExcelDnaUtil.Application;
    static Excel.Workbook workbook     = excelApp.ActiveWorkbook;

2) the data type returned from the XlCall function is "object", in C# how can I convert the returned object into a string[] that I can loop through using a foreach statement?

Thanks,
Paul.

[Govert van Drimmelen]

Hi Paul,

1. xlfGetWorkbook takes an information type and an optional workbook
name as a string.

The XlCall functions don't mix with the COM objects like Application
and Workbook at all. (The only other type involved with the C API /
XlCall functions is the ExcelReference type, which is a bit like a
Range object and describes a block of cells on a sheet.)

The GET.WORKBOOK function has this signature: (copied from that Excel
4 macro help file)
GET.WORKBOOK(type_num, name_text)

Type_num is a number that specifies what type of workbook
information you want.
Type_num Returns
1 The names of all sheets in the workbook, as a
horizontal array of text values.
Names are returned as [book]sheet.
[...37 other info types here...]
Name_text is the name of an open workbook. If name_text is
omitted, it is assumed to be the active workbook.


2. XlCell.Excel(...) throws an exception of type XlCallException or
returns an 'object' and the real type will be one of these types:
ExcelError, ExcelMissing, ExcelEmpty, ExcelReference, double, string,
bool or an object[,] array with contents the other types.

In this case I suspect the 'horizontal array of text values' means
you'll get a 2D object[,] array with one row and a column for every
sheets, each entry in the array being a string.

You can get the size of a particular dimension in a multi-dimension
array with Array.GetLength(n).

So you might try:

object result = XlCall.Excel(XlCall.xlfGetWorkbook, 1);
object[,] sheetNames = (object[,])result;
for (int j = 0; j < sheetNames.GetLength(1); j++)
{
string sheetName = (string)sheetNames[0,j];
// use sheetName here.
}

(People say VB.NET is more verbose than C#. Only sometimes.)

There was a start to making some wrappers for the XlCall functions ...

Regards,
Govert

[Paul Beeston]

Thanks again Govert - here's what I found:

object result = XlCall.Excel(XlCall.xlfGetWorkbook, 1);            

object[,] sheetNames = (object[,])result;

threw this exception: 

error ExcelDna.Integration.XlCallException: Exception of type 'ExcelDna.Integration.XlCallException' was thrown.

   at ExcelDna.Integration.XlCall.Excel(Int32 xlFunction, Object[] parameters)

   at TradeLog.ExcelIO.ExcelRW.ListWorksheets(String[]& aWorksheets, String sheetNameFilter)

This works fine (adapted a bit to extract it from the class that contains this code) :

  public static RetCode ListWorksheets(ref string[] aWorksheets)
        {
            RetCode retCode                = RetCode.Error;

            Excel.Application excelApp    = null;

            Excel.Workbook workbook        = null;  

            string errorMsg                = "";
            List<string> alWorksheets    = null;

            excelApp = (Excel.Application)ExcelDnaUtil.Application; 
            workbook = excelApp.ActiveWorkbook;    
            try
            {        
                alWorksheets = new List<string>();
                foreach (Excel.Worksheet sheet in workbook.Worksheets)
                {
                    alWorksheets.Add(sheet.Name);
                }
                aWorksheets = alWorksheets.ToArray();
            }
            catch(Exception ex)
            {
                errorMsg = "Error in ListWorksheets. " + ex;                
                retCode = RetCode.Error;
            }
            return retCode;            
        }

Thanks,

Paul.

[Govert van Drimmelen]

Hi Paul,

So you have it working through the COM automation interface - thanks
for posting your code.

But it fails when using the C API. Some possible reasons why

object result = XlCall.Excel(XlCall.xlfGetWorkbook, 1);

might throw that exception are
1. that it is being called from a worksheet function that is not
marked as IsMacroType=true. There is some anectodal evidence that
calling the COM interface from UDFs that are not marked as
IsMacroType=true might also be problematic. So if you run into
anything funny, that might be something to look for.
2. you are calling it directly from a Ribbon onAction-handler. In this
context the C API is not suipprorted - you need to call a macro
through onAction="RunTagMacro" tag="MyMacro".

All of this sounds complicated, mainly due to the fact that Excel-DNA
gives you access to all the different interface options, some of which
are not appropriate in particular contexts. As I get to know the
details I'll get smarter at giving particular guidance.

Some information about how this function is called from your code
would help me to understand what is going on too.

Regards,
Govert

[Paul Beeston]

Hi Govert,

You are right, your scenario #2 (calling from a Ribbon onAction-handler) is what I am doing.

Is what you are calling the "COM automation interface" the Excel Interop functionality?

Paul.