B
bcnu
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
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