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)
.