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:
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.
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