I need to loop through worksheet names of an open
workbook in VB.... any ideas how to do this (I just need
the name of every worksheet)

Hi
something like
sub foo()
dim wks as worksheet
for each wks in activeworkbook
msgbox wks.name
next
end sub

--
Regards
Frank Kabel
Frankfurt, Germany


wrote:
I need to loop through worksheet names of an open
workbook in VB.... any ideas how to do this (I just need
the name of every worksheet)

Sub List()

Dim ws As Integer

Worksheets.Add Befo=Worksheets(1)
ActiveSheet.Name = "Index"
For ws = 2 To Worksheets.Count
Worksheets("Index").Cells(ws, 1) = Worksheets(ws).Name
Next
End Sub

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



wrote in message
...
I need to loop through worksheet names of an open
workbook in VB.... any ideas how to do this (I just need
the name of every worksheet)


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.686 / Virus Database: 447 - Release Date: 14/05/2004

I have this and I keep getting an error message "Runtime
error 424 object required"

Here is my Code:

Private Sub CommandButton1_Click()

Dim mybook As Workbook
Dim wks As Worksheet
Dim FNames As String
Dim MyPath As String
Dim SaveDriveDir As String
Dim CurrentSheetName As String

SaveDriveDir = CurDir
MyPath = "C:\Data"
ChDrive MyPath
ChDir MyPath
FNames = Dir("*.xls")
If Len(FNames) = 0 Then
MsgBox "No files in the Directory"
ChDrive SaveDriveDir
ChDir SaveDriveDir
Exit Sub
End If

Application.ScreenUpdating = False

Do While FNames < ""
Set mybook = Workbooks.Open(FNames)

For Each wks In ActiveWorkbooks
CurrentSheetName = wks.Name
Workbook.Activate (Summary.xls)
Worksheets("Sheets1").Range("A1") = FName
Worksheets("Sheets1").Range("A2") =
CurrentSheetName
Workbook.Activate (mybook)
Next
mybook.Close False
FNames = Dir()
Loop
ChDrive SaveDriveDir
ChDir SaveDriveDir
Application.ScreenUpdating = True

End Sub
-----Original Message-----
Hi
something like
sub foo()
dim wks as worksheet
for each wks in activeworkbook
msgbox wks.name
next
end sub

--
Regards
Frank Kabel
Frankfurt, Germany


wrote:
I need to loop through worksheet names of an open
workbook in VB.... any ideas how to do this (I just
need
the name of every worksheet)
.

If you'd try the code I suggested in your previous thread, you'd see that
you don't have to open the workbooks in order to get the worksheet names
from them.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]


wrote:
I have this and I keep getting an error message "Runtime
error 424 object required"

Here is my Code:

Private Sub CommandButton1_Click()

Dim mybook As Workbook
Dim wks As Worksheet
Dim FNames As String
Dim MyPath As String
Dim SaveDriveDir As String
Dim CurrentSheetName As String

SaveDriveDir = CurDir
MyPath = "C:\Data"
ChDrive MyPath
ChDir MyPath
FNames = Dir("*.xls")
If Len(FNames) = 0 Then
MsgBox "No files in the Directory"
ChDrive SaveDriveDir
ChDir SaveDriveDir
Exit Sub
End If

Application.ScreenUpdating = False

Do While FNames < ""
Set mybook = Workbooks.Open(FNames)

For Each wks In ActiveWorkbooks
CurrentSheetName = wks.Name
Workbook.Activate (Summary.xls)
Worksheets("Sheets1").Range("A1") = FName
Worksheets("Sheets1").Range("A2") =
CurrentSheetName
Workbook.Activate (mybook)
Next
mybook.Close False
FNames = Dir()
Loop
ChDrive SaveDriveDir
ChDir SaveDriveDir
Application.ScreenUpdating = True

End Sub
-----Original Message-----
Hi
something like
sub foo()
dim wks as worksheet
for each wks in activeworkbook
msgbox wks.name
next
end sub

--
Regards
Frank Kabel
Frankfurt, Germany


wrote:
I need to loop through worksheet names of an open
workbook in VB.... any ideas how to do this (I just need
the name of every worksheet)
.

Sorry, I can not find your thread
-----Original Message-----
If you'd try the code I suggested in your previous
thread, you'd see that
you don't have to open the workbooks in order to get the
worksheet names
from them.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address
unmonitored]


wrote:
I have this and I keep getting an error
message "Runtime
error 424 object required"

Here is my Code:

Private Sub CommandButton1_Click()

Dim mybook As Workbook
Dim wks As Worksheet
Dim FNames As String
Dim MyPath As String
Dim SaveDriveDir As String
Dim CurrentSheetName As String

SaveDriveDir = CurDir
MyPath = "C:\Data"
ChDrive MyPath
ChDir MyPath
FNames = Dir("*.xls")
If Len(FNames) = 0 Then
MsgBox "No files in the Directory"
ChDrive SaveDriveDir
ChDir SaveDriveDir
Exit Sub
End If

Application.ScreenUpdating = False

Do While FNames < ""
Set mybook = Workbooks.Open(FNames)

For Each wks In ActiveWorkbooks
CurrentSheetName = wks.Name
Workbook.Activate (Summary.xls)
Worksheets("Sheets1").Range("A1") = FName
Worksheets("Sheets1").Range("A2") =
CurrentSheetName
Workbook.Activate (mybook)
Next
mybook.Close False
FNames = Dir()
Loop
ChDrive SaveDriveDir
ChDir SaveDriveDir
Application.ScreenUpdating = True

End Sub
-----Original Message-----
Hi
something like
sub foo()
dim wks as worksheet
for each wks in activeworkbook
msgbox wks.name
next
end sub

--
Regards
Frank Kabel
Frankfurt, Germany


wrote:
I need to loop through worksheet names of an open
workbook in VB.... any ideas how to do this (I just
need
the name of every worksheet)
.

.

wrote:
If you'd try the code I suggested in your previous thread, you'd see
that you don't have to open the workbooks in order to get the
worksheet names from them.
Sorry, I can not find your thread

Hmmm, maybe you're not the same person that posted the same type of question
a little while ago. You both came through as
. Anyway, here's what I posted to that
earlier thread - maybe it'll help you out:


The subroutine Demo below will take a folder path as an argument and return
a list of all Excel files (along with each file's worksheets) to the Debug
window. To use it, just enter this in the Debug window (or call it from
code):

Demo "C:\" '/ change this to whatever folder you want to use


Sub Demo(rsFolderPath As String)
Dim fso As Object
Dim fil As Object
Dim vWSNames As Variant
Dim v As Variant

Set fso = CreateObject("Scripting.FileSystemObject")

For Each fil In fso.GetFolder(rsFolderPath).Files
If StrComp(fil.Type, "Microsoft Excel " & _
"Worksheet", vbTextCompare) = 0 Then
Debug.Print fil.Path
vWSNames = mvGetWSNames(fil.Path)
For Each v In vWSNames
Debug.Print " " & CStr(v)
Next v
End If
Next fil

Set fso = Nothing
End Sub

Private Function mvGetWSNames(rsWBPath As String) _
As Variant
Dim adCn As Object
Dim axCat As Object
Dim axTab As Object
Dim asSheets() As String
Dim nShtNum As Integer

Set adCn = CreateObject("ADODB.Connection")
Set axCat = CreateObject("ADOX.Catalog")

With adCn
.ConnectionString = "Provider=Microsoft.Jet.OLEDB" & _
".4.0;Data Source=" & rsWBPath & ";Extended " & _
"Properties=""Excel 8.0;HDR=Yes;IMEX=1"""
.CursorLocation = 3
.Open
End With

Set axCat.ActiveConnection = adCn

For Each axTab In axCat.Tables
ReDim Preserve asSheets(0 To nShtNum)
asSheets(nShtNum) = Left$(axTab.Name, _
Len(axTab.Name) - 1)
nShtNum = nShtNum + 1
Next axTab

mvGetWSNames = asSheets

Set axCat = Nothing
adCn.Close
Set adCn = Nothing
End Function


There is no error handling, so you'd probably want to add some in the case
of a bad folder path or some unexpected error.


--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

Jake,
Your function enumerated all my worksheets *and* my 'named ranges',
however some results were missing a trailing character, e.g.

'Sheet Name Has $ dollar and gap$
(should be 'Sheet Name Has $ dollar and gap$')

'Sheet Name Has $ dollar and gap$'MyNam
(should not appear at all)

--

onedaywhen wrote:
Jake,
Your function enumerated all my worksheets *and* my 'named ranges',

Thanks - I forgot about named ranges being included in the tables
collection. The For Next loop should be modified as follows:

For Each axTab In axCat.Tables
If Right$(axTab.Name, 1) = "$" Then
ReDim Preserve asSheets(0 To nShtNum)
asSheets(nShtNum) = Left$(axTab.Name, _
Len(axTab.Name) - 1)
nShtNum = nShtNum + 1
End If
Next axTab

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

onedaywhen wrote ...

Jake,
Your function enumerated all my worksheets *and* my 'named ranges',
however some results were missing a trailing character, e.g.

'Sheet Name Has $ dollar and gap$
(should be 'Sheet Name Has $ dollar and gap$')

'Sheet Name Has $ dollar and gap$'MyNam
(should not appear at all)

It was a bit cowardly of me not to post suggested amendments, wasn't
it? I'm still unsure whether publicly correcting a MVP leads to me
being proposed or blackballed at the next MVP annual conference and
dinner dance.

Oh well, here goes (I drink soup audibly, which I've heard leads to an
instant blackball anyhow):

Public Function GetWSNames( _
ByVal WBPath As String _
) As Variant
Dim adCn As Object
Dim adRs As Object
Dim asSheets() As String
Dim nShtNum As Long
Dim nRows As Long
Dim nRowCounter As Long
Dim sSheet As String
Dim sChar1 As String
Dim sChar2 As String

Const INDICATOR_SHEET As String = "$"
Const INDICATOR_SPACES As String = "'"

Set adCn = CreateObject("ADODB.Connection")

With adCn
.ConnectionString = "Provider=Microsoft.Jet.OLEDB" & _
".4.0;Data Source=" & WBPath & ";Extended " & _
"Properties=""Excel 8.0;HDR=Yes"""
.CursorLocation = 3
.Open
End With

Set adRs = adCn.OpenSchema(20)
With adRs
nRows = .RecordCount
For nRowCounter = 0 To nRows - 1
sSheet = !TABLE_NAME
sChar1 = vbNullString
sChar2 = vbNullString
On Error Resume Next
sChar1 = Mid$(sSheet, Len(sSheet), 1)
sChar2 = Mid$(sSheet, Len(sSheet) - 1, 1)
On Error GoTo 0

Select Case sChar1

Case INDICATOR_SHEET
sSheet = Left$(sSheet, Len(sSheet) - 1)

Case INDICATOR_SPACES
If sChar2 = INDICATOR_SHEET Then
sSheet = Mid$(sSheet, 2, Len(sSheet) - 3)
End If

Case Else
sSheet = vbNullString

End Select

If Len(sSheet) 0 Then
ReDim Preserve asSheets(nShtNum)

' Un-escape
asSheets(nShtNum) = Replace(sSheet, _
INDICATOR_SPACES & INDICATOR_SPACES, _
INDICATOR_SPACES)

nShtNum = nShtNum + 1
End If

.MoveNext
Next
.Close
End With
adCn.Close

GetWSNames = asSheets

End Function

--

onedaywhen wrote:
It was a bit cowardly of me not to post suggested amendments, wasn't
it? I'm still unsure whether publicly correcting a MVP leads to me
being proposed or blackballed at the next MVP annual conference and
dinner dance.

I don't take any offense at being corrected. Anything that helps the
community avoid confusion and helps me learn at the same time is great IMO.
MVPs should be just as open to correction/constructive criticism as anyone
else here!

(I drink soup audibly, which I've heard leads to an
instant blackball anyhow):

Now *that* may be an issue. <g

Select Case sChar1

Case INDICATOR_SHEET
sSheet = Left$(sSheet, Len(sSheet) - 1)

Case INDICATOR_SPACES
If sChar2 = INDICATOR_SHEET Then
sSheet = Mid$(sSheet, 2, Len(sSheet) - 3)
End If

OK, you've corrected me twice in the same thread - definitely not invited to
the dinner dance now. Just because I don't use spaces in my worksheet names
doesn't mean that others don't. I'll amend my code to handle worksheet
names with spaces, too.

I notice that you typically invoke the OpenSchema method to loop through
tables (instead of using ADOX). I haven't done any testing, but is your
method more efficient? I would guess there's some additional overhead
introduced by the ADOX objects, but I'm wondering if you know how much
difference there is....

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

OK - another modification thanks to onedaywhen.

The For Next loop should now look like this:

For Each axTab In axCat.Tables
Select Case Right$(axTab.Name, 1)
Case "$"
ReDim Preserve asSheets(0 To nShtNum)
asSheets(nShtNum) = Left$(axTab.Name, _
Len(axTab.Name) - 1)
nShtNum = nShtNum + 1
Case "'"
If Mid$(axTab.Name, Len(axTab.Name) - 1, 1) _
= "$" And Left$(axTab.Name, 1) = "'" Then
ReDim Preserve asSheets(0 To nShtNum)
asSheets(nShtNum) = Mid$(axTab.Name, _
2, Len(axTab.Name) - 3)
nShtNum = nShtNum + 1
End If
End Select
Next axTab

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

I don't take any offense at being corrected. Anything that helps the
community avoid confusion and helps me learn at the same time is great IMO.
MVPs should be just as open to correction/constructive criticism as anyone
else here!

Amen to that :-)


Also, (To the OP now), don't conclude that because a respondent has no MVP tag
that that means you necessarily get any lesser experience/knowledge looking at
your question, as the very opposite can sometimes be true in these forums. Take
every answer you get and try it, and then base your judgement on the results.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------


<snip


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.686 / Virus Database: 447 - Release Date: 14/05/2004

Ken Wright wrote:
I don't take any offense at being corrected. Anything that helps the
community avoid confusion and helps me learn at the same time is
great IMO. MVPs should be just as open to correction/constructive
criticism as anyone else here!

Amen to that :-)

Can I get a hallelujah? <g

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

LOL, Halllllllllllleeeeeeeeeelllllllllllllluuuujjjjjjja aaahhhhhhhhhhhh :-)

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



"Jake Marx" wrote in message
...
Ken Wright wrote:
I don't take any offense at being corrected. Anything that helps the
community avoid confusion and helps me learn at the same time is
great IMO. MVPs should be just as open to correction/constructive
criticism as anyone else here!

Amen to that :-)

Can I get a hallelujah? <g

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.686 / Virus Database: 447 - Release Date: 14/05/2004

"Jake Marx" wrote ...

I notice that you typically invoke the OpenSchema method to loop through
tables (instead of using ADOX). I haven't done any testing, but is your
method more efficient? I would guess there's some additional overhead
introduced by the ADOX objects, but I'm wondering if you know how much
difference there is....

I'm not sure about efficiency issues (we're both using late binding,
after all!). I suppose if you are already using ADO in your project
then not using ADOX means one less reference!

<Respect for MVPsAs I'm sure you are already aware,</Respect for
MVPs the main reason for using the OpenSchema method is it gives me a
recordset object, rather than a collection, to work with. So I can do
recordset type things with the results e.g. use its Filter property
and GetRows method to get a 2-D array, use the CopyFromRecordset
method, associate it with the DataSource property of an ActiveX
control, etc.

[Unfortunately, the Filter method isn't sophisticated enough for our
Excel sheets exercise because the wildcard character in a LIKE clause
must be at the end (or the beginning *and* end); also the dollar sign
is reserved for numeric data.]

With OpenSchema my top level object is not restricted to being a
table, of course. For example, if I'm searching all tables for columns
with 'ID' in their name, a flatter structure is more useful:

Set adRs = adCn.OpenSchema(4) ' adSchemaColumns
adRs.Filter = "COLUMN_NAME LIKE '%ID%'"
adRs.Sort = "TABLE_NAME"

BTW the above doesn't work with the Excel provider :-(

--