VBA Stuck in nested loop

Question

I'm writing a code that compares the first few characters of a workbook (fromWB) name (tempName) with the name of a worksheet (sheetName). If they match, then the data from the file is copied into the worksheet (ws). I'm trying to get my code to iterate through the folder of files and through the workbook (fromWB) with multiple worksheets but the code is stuck in the do while loop (iterating through the worksheets), and I'm not sure how to fix it. It stops copying and pasting into the worksheet because tempName isn't changing since the code is not looping through the folder of files. I debugged the code and I'm not getting any errors. Any help would be appreciated!!

EDIT: Would two nested For Each loops work?

Sub Import(Optional sPath As Variant)
Dim SelectFolder As FileDialog
Dim fromWB As Workbook
Dim toWB As Workbook
Dim ws As Worksheet
Dim fileName As String
Dim sheetName As String
Dim tempName As String


'Application.ScreenUpdating = False
'Application.DisplayAlerts = False
'Application.EnableEvents = False

'Retrieve Target Folder Path From User
Set SelectFolder = Application.FileDialog(msoFileDialogFolderPicker)

With SelectFolder
    .Title = "Select a Folder"
    .AllowMultiSelect = False
    If .Show <> -1 Then GoTo NextCode
    sPath = .SelectedItems(1) & "\"
End With

'In Case of Cancel
NextCode:
sPath = sPath
If sPath = "" Then GoTo ResetSettings

sFile = Dir(sPath & "*xlsx")
Workbooks("Temp.xlsx").Activate
Set toWB = ActiveWorkbook

Do While sFile <> ""
    Set fromWB = Workbooks.Open(sPath & sFile)
    tempName = Left(sFile, 3)
    Range("A1:B10").Select
    Selection.Copy

    For Each ws In toWB.Worksheets
        Windows("Temp.xlsx").Activate
        sheetName = ActiveSheet.Name
        If sheetName = tempName Then
            ws.Activate
            Range("A4").PasteSpecial xlPasteAllUsingSourceTheme
        End If
        ws.Activate
    Next ws
sFile = Dir()
Loop


ResetSettings:
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True
End Sub

score 1 · Answer 1 · edited May 23 '17 at 12:13

1

I don't think there is a problem with your for each loop. Seems to me it will be getting stuck in the do while loop.

You are not changing sFile anywhere in the loop so it will never break.

Do While sFile <> ""

Loop

EDIT:

Sub LoopThroughFiles()
    Dim MyObj As Object, MySource As Object, file As Variant
    file = Dir("c:\testfolder\")
    While (file <> "")
      'code here
      file = Dir
    Wend
End Sub

From: Loop through files in a folder using VBA?

edited May 23 '17 at 12:13

Community

1
1

answered Jun 03 '15 at 14:37

tea_pea

1,482
14
19

It looks like he's changing sFile in the loop: sFile = Left(sFile, 3). This will never allow sFile to be equal to an empty string though. At some point the loop will get stuck on the last 3 (or less) characters of the string. – Rob Cutmore Jun 03 '15 at 14:44
Ah sorry that's what I meant. Do you know how I can change it so it'll go to the next one? – Jun 03 '15 at 14:45
I changed it so I'm not changing sFile (I think) and added sFile = Dir() after Next ws. Made these changes in the question. I'm still getting the same result though, so I'm not sure why it won't open the next file in the folder. – Jun 03 '15 at 14:50
It still isn't looping through the files – Jun 03 '15 at 14:56
So it isn't stuck anymore but it is incorrectly matching and pasting data. – Jun 03 '15 at 15:29

VBA Stuck in nested loop

1 Answers1