Can anyone please help?
My code is not looping. I have many files in the folder, but it can only read one file correctly and stop. What is wrong? Pl advise. Thanks.
Sub LoopFileS()
Dim wbConsol As Workbook
Dim wbTemp As Workbook
Dim myPath As String
Dim myFile As String
Dim myExtension As String
Dim FldrPicker As FileDialog
Dim stSupplierName As String
Dim stCompliance As String
Dim starResults As String
Dim starResults1 As String
Dim starResults2 As String
Dim lNextRow As Long
'Optimize Macro Speed
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
Set wbConsol = ThisWorkbook
Set FldrPicker = Application.FileDialog(msoFileDialogFolderPicker)
With FldrPicker
.Title = "Select A Target Folder"
.AllowMultiSelect = False
If .Show <> -1 Then Exit Sub ' GoTo NextCode
myPath = .SelectedItems(1) & "\"
End With
'In Case of Cancel
NextCode:
'myPath = myPath
If myPath = "" Then Exit Sub
myExtension = "*.xls*"
'Target Path with Ending Extention
myFile = Dir(myPath & myExtension)
'Loop through each Excel file in folder
Do While myFile <> ""
'Set variable equal to opened workbook
Set wbTemp = Workbooks.Open(Filename:=myPath & myFile)
'Ensure Workbook has opened before moving on to next line of code
DoEvents
stSupplierName = Range("B3")
stCompliance = Range("C17")
starResults = Range("C21")
starResults1 = Range("C23")
starResults2 = Range("C27")
wbTemp.Close SaveChanges:=False
lNextRow = Sheet1.Range("A" & Rows.Count).End(xlUp).Row + 7
Range("B" & lNextRow) = stSupplierName
Range("C" & lNextRow) = stCompliance
Range("D" & lNextRow) = starResults
Range("E" & lNextRow) = starResults1
Range("F" & lNextRow) = starResults2
DoEvents
myFile = Dir
Loop
Application.ScreenUpdating = True
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
End Sub