looping for files within folder - prevent the code from attempting to open same file

[Jim885]

I have the below code that results in a loop error because the code does not ignore the source file.
Can someone adjust?

Hope I provided enough ...

Dim fso As FileSystemObject
Dim f As Folder, sf As Folder
Dim ofile As File
Dim lr As Long, LRM As Long
Dim MyPath As String, MyFile As String, File As Workbook
Dim sFolder As String
Dim sFile As String
Dim wbSource As Workbook
Dim wbMaster As Workbook
Dim shTarget As Worksheet
Dim shSource As Worksheet

Application.ScreenUpdating = False

On Error Resume Next
Set fso = CreateObject("Scripting.FileSystemObject")
Set f = fso.GetFolder(ThisWorkbook.Path & "\")

'================================================
' Loop through all files within the same folder
'================================================
For Each f In f
If Not f.Name = "Archive" Or Not f.Name = "ReportWriter.xlsm" Then
For Each ofile In f.Files
If Left(fso.GetExtensionName(ofile.Path), 4) = "xlsm" Then
Set wbMaster = ThisWorkbook
If ofile.Name <> ThisWorkbook.Name Then
                        Set wbSource = Workbooks.Open(ofile)

 

[Joe4]

It is tough to tell, since you only pasted part of your code.
However, I think you should move this line OUT of your For loop, and put it BEFORE the For loop:

Set wbMaster = ThisWorkbook

That value should never change, so there is no reason to have that statement within your loop.
And depending on what your other code is doing, if the last file you opened is the active file, it would be resetting wbMaster to that file (and not your original file).

 

[DanteAmor]

Try this:

Sub test()
  Dim wbSource As Workbook, wbMaster As Workbook
  Dim shTarget As Worksheet, shSource As Worksheet
  Dim fso As Object, f As Variant, oFile As Variant
  
  Application.ScreenUpdating = False
  
  'On Error Resume Next
  Set fso = CreateObject("Scripting.FileSystemObject")
  Set f = fso.GetFolder(ThisWorkbook.Path & "\")
  
  '================================================
  ' Loop through all files within the same folder
  '================================================
  For Each oFile In f.Files
    Select Case oFile.Name
      Case "Archive", "ReportWriter.xlsm", ThisWorkbook.Name
      Case Else
        If Left(fso.GetExtensionName(oFile.Path), 4) = "xlsm" And Left(oFile.Name, 1) <> "~" Then
          Set wbMaster = ThisWorkbook
          Set wbSource = Workbooks.Open(oFile)
          '
          'continue your code
          '
        End If
    End Select
  Next oFile
End Sub

 

[Jim885]

Thank you both. It will try both approaches tomorrow. I tried for the last 20 minutes and am getting lost ... Need a fresh start tomorrow.

 

[Jim885]

Joe4,
Thank you for your help. That helped me see and learn something about the code.
After stepping through the code after a good night's sleep, the code was doing exactly what you described.
Much appreciated.


Dante,
Your code is quick, short and amazing. I will use it for this project, and any of my future "looping through all files in a folder" projects.
Thank you so much.

 

[Joe4]

You are welcome.
Glad we could help.