View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.programming
Mike Taylor Mike Taylor is offline
external usenet poster
 
Posts: 17
Default 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.