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

Important Notice

Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

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


    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:

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

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!