Results 1 to 17 of 17

Thread: Help! What is wrong? My code is not looping!

Threaded View

Previous Post Previous Post   Next Post Next Post
  1. #1

    Help! What is wrong? My code is not looping!

    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
    Last edited by elaineada75; 06-08-2022 at 09:20 AM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •