Loop thru multiple files - Modify worksheet visible property
 

Can anyone share idea(s) for code that will programatically loop
through all the .xls files in a directory (path is c:\Data\DataFiles) and

1) set the Visible property for all ten worksheets in each .xls file to visible?

Here's what I've tried so far...

Option Explicit
Sub VisibleTrue()
Dim basebook As Workbook
Dim mybook As Workbook
'Dim Item As Worksheet
Dim i As Long
Application.ScreenUpdating = False
With Application.FileSearch
.NewSearch
.LookIn = "C:\Data\DataFiles\Sept"
.SearchSubFolders = False
.FileType = msoFileTypeExcelWorkbooks
If .Execute() 0 Then
Set basebook = ThisWorkbook
For i = 1 To .FoundFiles.Count
Set mybook = Workbooks.Open(.FoundFiles(i))
For Each sh In Sheets
sh.Visible = True
Next sh
Next i
End If
mybook.Close
Application.ScreenUpdating = True
End Sub

Any ideas are greatly appreciated.

Loop thru multiple files - Modify worksheet visible property
 

This should work:

Option Explicit
Sub VisibleTrue()
Dim basebook As Workbook
Dim mybook As Workbook
'Dim Item As Worksheet
Dim sh as Object
Dim i As Long
Application.ScreenUpdating = False
With Application.FileSearch
.NewSearch
.LookIn = "C:\Data\DataFiles\Sept"
.SearchSubFolders = False
.FileType = msoFileTypeExcelWorkbooks
If .Execute() 0 Then
Set basebook = ThisWorkbook
For i = 1 To .FoundFiles.Count
Set mybook = Workbooks.Open(.FoundFiles(i))
For Each sh In myBook.Sheets
sh.Visible = True
Next sh
myBook.Close SaveChanges:=True
Next i
End If

Application.ScreenUpdating = True
End Sub


--
regards,
Tom Ogilvy

Mike Taylor wrote in message
om...
Can anyone share idea(s) for code that will programatically loop
through all the .xls files in a directory (path is c:\Data\DataFiles) and

1) set the Visible property for all ten worksheets in each .xls file to
visible?

Here's what I've tried so far...

Option Explicit
Sub VisibleTrue()
Dim basebook As Workbook
Dim mybook As Workbook
'Dim Item As Worksheet
Dim i As Long
Application.ScreenUpdating = False
With Application.FileSearch
.NewSearch
.LookIn = "C:\Data\DataFiles\Sept"
.SearchSubFolders = False
.FileType = msoFileTypeExcelWorkbooks
If .Execute() 0 Then
Set basebook = ThisWorkbook
For i = 1 To .FoundFiles.Count
Set mybook = Workbooks.Open(.FoundFiles(i))
For Each sh In Sheets
sh.Visible = True
Next sh
Next i
End If
mybook.Close
Application.ScreenUpdating = True
End Sub

Any ideas are greatly appreciated.