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)

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]

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