Excel Sheet Names - Excel.Application.Sheets("sheet1") 
Author Message
 Excel Sheet Names - Excel.Application.Sheets("sheet1")

I am working on a VB app that manipulates Excel files, the program is
essentially a sophisticated Macro, but for reasons that aren't worth
explaining Excel VBA does not have everything I need.

The problem is that I cannot figure out how to read the names of
spreadsheets. I can count the number of spreadsheets, I can read the name of
the active sheet, I can do whatever I want to do to the file, but I cannot
get the program to give the string name of the other inactive sheets.

The code for manipulating the sheets seems really odd to me.

Excel.Application.Sheets("sheet1"). . .

Sheets() has all the properties of an array, but the array points are the
name of the sheets as strings. I have been trying to find a numerical value
for parts of the array. If I enter a number in place of the string it gives
me good ol' error number 9.

If anybody can tall me how to read the names of inactive sheets without
knowing them in advance, I would be grateful.

Thanks,

Matt Baranowski



Sun, 14 Apr 2002 03:00:00 GMT  
 Excel Sheet Names - Excel.Application.Sheets("sheet1")
Matthew,

Sheets is a collection of Sheet objects under the Workbook object, not the
Application Object.  In Excel VBA, this code would be similar to this
(untested):

    Dim ws As Worksheet

    For Each ws In Workbooks(<<name>>).Worksheets
        MsgBox ws.Name
    Next ws

So, in VB, you may do something like this (untested):

    Dim xlApp As New Excel.Application
    Dim xlWB As Excel.Workbook
    Dim xlWS As Excel.Worksheet

    Set xlWB = xlApp.Workbooks.Open(<<full path of wb>>)

    For Each xlWS In xlWB.Worksheets
        MsgBox xlWS.Name
    Next xlWS

    '/ << other code here>>

    xlWB.Close savechanges:=False '/ or True if you wish
    Set xlWB = Nothing
    xlApp.Quit
    Set xlApp = Nothing

By the way, I'm interested in knowing why you are using VB as opposed to VBA
to do this (that is, if you have time to explain).

Regards,
Jake Marx


Quote:
> I am working on a VB app that manipulates Excel files, the program is
> essentially a sophisticated Macro, but for reasons that aren't worth
> explaining Excel VBA does not have everything I need.

> The problem is that I cannot figure out how to read the names of
> spreadsheets. I can count the number of spreadsheets, I can read the name
of
> the active sheet, I can do whatever I want to do to the file, but I cannot
> get the program to give the string name of the other inactive sheets.

> The code for manipulating the sheets seems really odd to me.

> Excel.Application.Sheets("sheet1"). . .

> Sheets() has all the properties of an array, but the array points are the
> name of the sheets as strings. I have been trying to find a numerical
value
> for parts of the array. If I enter a number in place of the string it
gives
> me good ol' error number 9.

> If anybody can tall me how to read the names of inactive sheets without
> knowing them in advance, I would be grateful.

> Thanks,

> Matt Baranowski



Sun, 14 Apr 2002 03:00:00 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. CreateObject("Excel.Sheet")

2. CreateObject("Excel.Sheet")

3. VB SCript: CreateObject("Excel.Sheet") function

4. GetObject("","InternetExplorer.Application") fails in Excel VBA

5. GetObject("","InternetExplorer.Application") fails in Excel VBA

6. Getting the list of Excel Sheet Names using VBA

7. Excel Sheet Name

8. Use VBA to send Task Folder name to an Excel Sheet

9. getting the name of the active sheet (Excel)

10. Accessing Excel Sheet Names in VB 4.0

11. Changing name on Excel sheet

12. change excel sheet name

 

 
Powered by phpBB® Forum Software