VBA to copy 1st sheet of files from several different folders

Hi. The code in the bottom code window will copy the first sheet of all .xls files that reside in a single folder into a master workbook. For example:

Fpath = "S:\Accounting\News NOP\
Fname = Dir(Fpath & "*.xls")

I need to find a way to get through several different folders on a website to reach different files. An example of the path would be "https://infoportal.red.com/programming/filmcontracts/WBFO/Annual_Film/" This folder would have several files in it but I would need to open and copy the first sheet of the only one that begins with the string "Film" (ie, Film_Source_File_WBFO.xls).

There would be several folders, all like the one above except with different TV station call letters where that one has WBFO.

For example:

"https://infoportal.red.com/programming/filmcontracts/WLBT/Annual_Film/"
"https://infoportal.red.com/programming/filmcontracts/WJKF/Annual_Film/"

The folder at the end of each path would have a source file labeled "Source_File_(station name).xls

Is there any way to modify this code to do this, or another way to do it? Thanks! :smile:

Private Sub CommandButton1_Click()
Dim Fpath As String, Fname As String
     
    With Application
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
        .EnableEvents = False
    End With
     
    Fpath = "S:\Accounting\News NOP\" ' change to suit your directory
    Fname = Dir(Fpath & "*.xls")
     
     
    With Workbooks("NOP-Backup file.xls") 'MUST BE OPEN
        Do While Fname <> ""
            If Fname <> .Name Then
                Workbooks.Open Fpath & Fname
                 'MOVE ONLY IF NOT   SAVING ON CLOSE. IF SAVING, USE COPY.
                Workbooks(Fname).Sheets(1).Move After:=.Sheets(.Sheets.Count)
            End If
            Fname = Dir
        Loop
    End With
     
    With Application
        .ScreenUpdating = True
        .Calculation = xlCalculationAutomatic
        .EnableEvents = True
    End With
    Dim i As Integer
Dim varLinks As Variant
varLinks = ActiveWorkbook.LinkSources(xlLinkTypeExcelLinks)
For i = UBound(varLinks) To LBound(varLinks) Step -1
ActiveWorkbook.BreakLink varLinks(i), xlLinkTypeExcelLinks
Next i
     
End Sub

Re: VBA to copy 1st sheet of files from several different folders--Disregard Question

I am going to post a much shorter question that may resolve the issue. Please disregard this post.

Thank you.