Listing xls files and sheets (excluding Hidden tabs) help

[kenpo_man]

Hi all,

I bow to the wisdom of this group. I have been able to find
information on how to list the xls files and all associated sheet tab
names, but I cannot figure out how to exclude the hidden sheet tabs.
I have created a macro for one of my bosses so he can print all
sheets, but if fails due to the hidden sheets.

here is the code for identifying the file names and sheets:

Sub BooksandSheets()

ScreenUpdating = False
Set wb = Workbooks.Add
Debug.Print wb.Name
Cells(1, 1) = "Workbook"
Cells(1, 2) = "SheetName"
intcounter = 2
Set fs = CreateObject("Scripting.FileSystemObject")
Set FSDir = fs.GetFolder("O:\Accounting\FLTACCTG\Account\Private
\Reports\2007\Current Month") ' Change Path
Set folder = FSDir.Files
For Each file In folder
Workbooks.Open Filename:=file.Name, UpdateLinks:=False,
ReadOnly:=True
For Each ws In Worksheets

Workbooks(wb.Name).Worksheets(1).Cells(intcounter, 1) =
file.Name
Workbooks(wb.Name).Worksheets(1).Cells(intcounter, 2) =
ws.Name
For Each SH In Worksheets
If (SH.Visible <> True) Then
Workbooks(wb.Name).Worksheets(1).Cells(intcounter, 3)
= "HIDDEN"
Else
Workbooks(wb.Name).Worksheets(1).Cells(intcounter,
3) = "NOT HIDDEN"
End If
Next SH
intcounter = intcounter + 1
Next ws
Workbooks(file.Name).Close False
Next

MsgBox "complete"

End Sub


I tried to differentiate between hidden and not hidden files, but it
ran into an issue when one sheet is hidden, it considers them ALL
hidden.

Any help is appreciated.

Thank you,

Ray

 

[Tom Ogilvy]

You don't need that
for each sh in worksheets loop. You are already looping over all the
sheets.

Sub BooksandSheets()
Dim bk as Workbook, wb as workbook
Dim sh1 as Worksheet, ws as Worksheet
Dim fs as Object, File as Object
Dim FSDir as Object, Folder as Object

ScreenUpdating = False
Set wb = Workbooks.Add
set sh1 = wb.Worksheets(1)
Debug.Print wb.Name
sh1.Cells(1, 1) = "Workbook"
sh1.Cells(1, 2) = "SheetName"
intcounter = 2
Set fs = CreateObject("Scripting.FileSystemObject")
Set FSDir = fs.GetFolder("O:\Accounting\FLTACCTG\Account\Private
\Reports\2007\Current Month") ' Change Path
Set folder = FSDir.Files
For Each file In folder
set bk = Workbooks.Open(Filename:=file.Name, _
UpdateLinks:=False, _
ReadOnly:=True)
For Each ws In bk.Worksheets

Workbooks(wb.Name).Worksheets(1).Cells( _
intcounter, 1) = file.Name
Workbooks(wb.Name).Worksheets(1).Cells( _
intcounter, 2) = ws.Name
Select Case ws.Visible
Case xlSheetVisible
sh1.cells(intCounter,3) = "NOT HIDDEN"
Case else
sh1.Cells(intcounter,3) = "HIDDEN"
end Select
intcounter = intcounter + 1
Next ws
bk.Close False
Next

MsgBox "complete"

End Sub

 

[kenpo_man]

You don't need that
for each sh in worksheets loop. You are already looping over all the
sheets.

Sub BooksandSheets()
Dim bk as Workbook, wb as workbook
Dim sh1 as Worksheet, ws as Worksheet
Dim fs as Object, File as Object
Dim FSDir as Object, Folder as Object

ScreenUpdating = False
Set wb = Workbooks.Add
set sh1 = wb.Worksheets(1)
Debug.Print wb.Name
sh1.Cells(1, 1) = "Workbook"
sh1.Cells(1, 2) = "SheetName"
intcounter = 2
Set fs = CreateObject("Scripting.FileSystemObject")
Set FSDir = fs.GetFolder("O:\Accounting\FLTACCTG\Account\Private
\Reports\2007\Current Month") ' Change Path
Set folder = FSDir.Files
For Each file In folder
set bk = Workbooks.Open(Filename:=file.Name, _
UpdateLinks:=False, _
ReadOnly:=True)
For Each ws In bk.Worksheets

Workbooks(wb.Name).Worksheets(1).Cells( _
intcounter, 1) = file.Name
Workbooks(wb.Name).Worksheets(1).Cells( _
intcounter, 2) = ws.Name
Select Case ws.Visible
Case xlSheetVisible
sh1.cells(intCounter,3) = "NOT HIDDEN"
Case else
sh1.Cells(intcounter,3) = "HIDDEN"
end Select
intcounter = intcounter + 1
Next ws
bk.Close False
Next

MsgBox "complete"

End Sub

--
Regards,
Tom Ogilvy
















- Show quoted text -

Tom,

You Rock!! Thank you so much. This has brought a new issue to light,
though. I have a user that saves other file types in this directory
so now I need to modify this code to look at MS Excel files only.

Thank you,

Ray