I'm trying to speed up the macro and not show each screen and workbook as it
opens.

I've got the following code, this user group created for me, to make a data
list of path, filename and sheet names of numerous files within a folder.
The macro switches between the data page and the new workbook being
investigated. I'm trying to not show all this bouncing back and forth. I've
tried Application.ScreenUpdating = False and ActiveWindow.Visible = False but
cannot figure out where to insert or which one to use.

I appreicate, as always, any help you might be able to provide. Happy New
Year

Option Explicit
Sub ProcessAll()

Const sPath As String = "C:\Documents and Settings\gejones\Desktop\Updated
Equipment_impact files\"

Dim wb As Workbook, i As Integer
With ThisWorkbook.Sheets("Data")
.Cells.Clear
'Set up Column Headers
.Cells(1, 1) = "Path"
.Cells(1, 2) = "Folder"
.Cells(1, 3) = "Workbook"
.Cells(1, 4) = "Worksheet"
End With


With Application.FileSearch
.NewSearch
.LookIn = sPath
.SearchSubFolders = True
.Filename = "*.xls"


Application.ScreenUpdating = False
If .Execute() Then
For i = 1 To .FoundFiles.Count
Set wb = Workbooks.Open(.FoundFiles(i))
ProcessWorkbook wb
wb.Close SaveChanges:=False
Next i
End If
End With

End Sub

Sub ProcessWorkbook(oWB As Workbook)
Dim i As Long
Dim s As Worksheet
Dim d As Worksheet

Set d = ThisWorkbook.Worksheets("Data")
'sheet data has "filename" in C1, "sheetname" in D1
i = d.Cells(d.Rows.Count, 3).End(xlUp).Offset(1, 0).Row

For Each s In oWB.Worksheets
With d.Rows(i)
.Cells(1).Value = oWB.Path
.Cells(2).Value = Right(oWB.Path, Len(oWB.Path) - 73) 'you can get
the folder name from the Path
.Cells(3).Value = oWB.Name
.Cells(4).Value = s.Name
End With
i = i + 1
Next s

Windows("listing of CORONA names with macro.xls").Activate
ActiveWindow.Visible = True
Application.ScreenUpdating = True

End Sub

Your ProcessWorkbook Sub ends like this:
Windows("listing of CORONA names with macro.xls").Activate
ActiveWindow.Visible = True
Application.ScreenUpdating = True

ProcessWorkbook runs for every file you need to open and check - so after
each workbook file you check you are making the session visible and active
again (and then you never switch it back) - I think this is why you are still
seeing all the bouncing back and forth. Unless you need these lines for some
reason after each book is checked, move them to the end of your ProcessAll
Sub. That way it will only switch it back on after ALL files have been
processed.

As for which to use: ScreenUpdating if you want a "frozen", but visible
screen; ActiveWindow.Visible to truly make it invisible. If you make it
invisible, there is no point to also setting ScreenUpdating since you won't
see any screen updates when it is invisible anyway.

Hope it helps!
K Dales

"bcnu" wrote:

I'm trying to speed up the macro and not show each screen and workbook as it
opens.

I've got the following code, this user group created for me, to make a data
list of path, filename and sheet names of numerous files within a folder.
The macro switches between the data page and the new workbook being
investigated. I'm trying to not show all this bouncing back and forth. I've
tried Application.ScreenUpdating = False and ActiveWindow.Visible = False but
cannot figure out where to insert or which one to use.

I appreicate, as always, any help you might be able to provide. Happy New
Year

Option Explicit
Sub ProcessAll()

Const sPath As String = "C:\Documents and Settings\gejones\Desktop\Updated
Equipment_impact files\"

Dim wb As Workbook, i As Integer
With ThisWorkbook.Sheets("Data")
.Cells.Clear
'Set up Column Headers
.Cells(1, 1) = "Path"
.Cells(1, 2) = "Folder"
.Cells(1, 3) = "Workbook"
.Cells(1, 4) = "Worksheet"
End With


With Application.FileSearch
.NewSearch
.LookIn = sPath
.SearchSubFolders = True
.Filename = "*.xls"


Application.ScreenUpdating = False
If .Execute() Then
For i = 1 To .FoundFiles.Count
Set wb = Workbooks.Open(.FoundFiles(i))
ProcessWorkbook wb
wb.Close SaveChanges:=False
Next i
End If
End With

End Sub

Sub ProcessWorkbook(oWB As Workbook)
Dim i As Long
Dim s As Worksheet
Dim d As Worksheet

Set d = ThisWorkbook.Worksheets("Data")
'sheet data has "filename" in C1, "sheetname" in D1
i = d.Cells(d.Rows.Count, 3).End(xlUp).Offset(1, 0).Row

For Each s In oWB.Worksheets
With d.Rows(i)
.Cells(1).Value = oWB.Path
.Cells(2).Value = Right(oWB.Path, Len(oWB.Path) - 73) 'you can get
the folder name from the Path
.Cells(3).Value = oWB.Name
.Cells(4).Value = s.Name
End With
i = i + 1
Next s

Windows("listing of CORONA names with macro.xls").Activate
ActiveWindow.Visible = True
Application.ScreenUpdating = True

End Sub

Perfect. Thanx.

"K Dales" wrote:

Your ProcessWorkbook Sub ends like this:
Windows("listing of CORONA names with macro.xls").Activate
ActiveWindow.Visible = True
Application.ScreenUpdating = True

ProcessWorkbook runs for every file you need to open and check - so after
each workbook file you check you are making the session visible and active
again (and then you never switch it back) - I think this is why you are still
seeing all the bouncing back and forth. Unless you need these lines for some
reason after each book is checked, move them to the end of your ProcessAll
Sub. That way it will only switch it back on after ALL files have been
processed.

As for which to use: ScreenUpdating if you want a "frozen", but visible
screen; ActiveWindow.Visible to truly make it invisible. If you make it
invisible, there is no point to also setting ScreenUpdating since you won't
see any screen updates when it is invisible anyway.

Hope it helps!
K Dales

"bcnu" wrote:

I'm trying to speed up the macro and not show each screen and workbook as it
opens.

I've got the following code, this user group created for me, to make a data
list of path, filename and sheet names of numerous files within a folder.
The macro switches between the data page and the new workbook being
investigated. I'm trying to not show all this bouncing back and forth. I've
tried Application.ScreenUpdating = False and ActiveWindow.Visible = False but
cannot figure out where to insert or which one to use.

I appreicate, as always, any help you might be able to provide. Happy New
Year

Option Explicit
Sub ProcessAll()

Const sPath As String = "C:\Documents and Settings\gejones\Desktop\Updated
Equipment_impact files\"

Dim wb As Workbook, i As Integer
With ThisWorkbook.Sheets("Data")
.Cells.Clear
'Set up Column Headers
.Cells(1, 1) = "Path"
.Cells(1, 2) = "Folder"
.Cells(1, 3) = "Workbook"
.Cells(1, 4) = "Worksheet"
End With


With Application.FileSearch
.NewSearch
.LookIn = sPath
.SearchSubFolders = True
.Filename = "*.xls"


Application.ScreenUpdating = False
If .Execute() Then
For i = 1 To .FoundFiles.Count
Set wb = Workbooks.Open(.FoundFiles(i))
ProcessWorkbook wb
wb.Close SaveChanges:=False
Next i
End If
End With

End Sub

Sub ProcessWorkbook(oWB As Workbook)
Dim i As Long
Dim s As Worksheet
Dim d As Worksheet

Set d = ThisWorkbook.Worksheets("Data")
'sheet data has "filename" in C1, "sheetname" in D1
i = d.Cells(d.Rows.Count, 3).End(xlUp).Offset(1, 0).Row

For Each s In oWB.Worksheets
With d.Rows(i)
.Cells(1).Value = oWB.Path
.Cells(2).Value = Right(oWB.Path, Len(oWB.Path) - 73) 'you can get
the folder name from the Path
.Cells(3).Value = oWB.Name
.Cells(4).Value = s.Name
End With
i = i + 1
Next s

Windows("listing of CORONA names with macro.xls").Activate
ActiveWindow.Visible = True
Application.ScreenUpdating = True

End Sub