How to get the EXCEL's WORKSHEET NAME?? 2

[qajussi]

Hi!

I am trying to populate names of the worksheets in the EXCEL workbook.

Anybody knows how to access them using VBScript or ADO.

I have a Country.xls.
Inside, I have worksheets named by counties like these:
Albani,Argentina,Canada,......etc.

I want to read these names of the worksheets and populate them in a combo box.


I know worksheet can be accessed by [worksheet$].
How can I go through each of the worksheets and get the country names.

THank you.

 

[SkipVought]

Hi,

dim ws as worksheet
for each ws in activeworkbook.worksheets
  MyCombo.AddItem ws.name
next

Skip,

_{[red]Be advised:[/red] When you ignite a firecracker in a bowl of vanilla, chocolate & strawberry ice cream, you get...
Neopolitan Blownapart!}

 

[qajussi]

Thanks So much SkipVought!

You are awesome.

 

[qajussi]

Can you help ??

Set objCat = CreateObject("ADOX.Catalog")
Set objCat.ActiveConnection = objConn

For Each tbl In objCat.Tables
sTableName = tbl.Name
cLength = Len(sTableName)
iTestPos = 0
iStartpos = 1
'Worksheet names with embedded spaces are enclosed
'by single quotes.
If Left(sTableName, 1) = "'" And Right(sTableName, 1) = "'" Then
iTestPos = 1
iStartpos = 2
End If

If Mid(sTableName, cLength - iTestPos, 1) = "$" Then
Cells(iRow, 1) = Mid(sTableName, iStartpos, cLength - (iStartpos + iTestPos))
MsgBox Cells(iRow, 1)
iRow = iRow + 1
End If
Next



I found this code from MSDN.microsoft site.
But I don't understand Cells().
I am getting error from it.

All I am trying to do is get the names of the worksheets.
Thanks.

 

[PBAPaul]

gajussi

The command Cells(3,4) means the cell in the 3rd row and 4th column.

From your code it looks as though it is initially reading iRow as 0 and this is creating the error. Cell A1 is designated as Cells(1,1).

Initially set iRow = 1 and I think you will be OK.

Paul

 

[SkipVought]

In addition to what Paul suggested, I'd EXPLICITLY reference the worksheet as the object of the cells range...

        If Mid(sTableName, cLength - iTestPos, 1) = "$" Then
            Sheets("WhateverSheetName").Cells(iRow, 1) = Mid(sTableName, iStartpos, cLength - (iStartpos + iTestPos))
            MsgBox Sheets("WhateverSheetName").Cells(iRow, 1)
            iRow = iRow + 1
        End If

Skip,

_{[red]Be advised:[/red] When you ignite a firecracker in a bowl of vanilla, chocolate & strawberry ice cream, you get...
Neopolitan Blownapart!}

 

[qajussi]

Thank you so much for your help.

Another question.

How can I sort this worksheet names??

Even though worksheets are listed in the alphabetical order, it comes out of order.

Thanks again.

 

[SkipVought]

Write the names into a column in a test sheet, then Sort the column.

Skip,

_{[red]Be advised:[/red] When you ignite a firecracker in a bowl of vanilla, chocolate & strawberry ice cream, you get...
Neopolitan Blownapart!}