VBA code to extract external file links

    Hello


    I have an input sheet ( attached ). It contains two tabs ( Input 1 and Input 2). These have data for different years ( From 2011-2021) and different products ( A to G). Both of these tabs takes values from external excel sheets


    I need a VBA code to identify or create a list which contains tab name , the name of external sheet used and for which product and which year it is used . Please note that some of the products use more than one external file and I all the name that are used


    I have attached a sample output for the same . But I need a VBA code to do this as my original file has a lot more data.


    1. For example in the output sheet you can see for product A year 2011 file name is Dummy4.xlsx and similarly for product A year 2012.


    This is just a sample output . I need a VBA code that gives the full list along with year and product name.


    Thanks in advance

    Input.xlsxOutput.xlsx

    What do you expect the "File used" value to look like (on the Output sheet) when there are multiple files making up the formula eg:


    Code
    ='E:\DownloadedFiles\[Dummy2.xlsx]dummy1'!$B$6+'E:\DownloadedFiles\[Dummy2.xlsx]dummy1'!$C$5

    or:

    Code
    ='E:\DownloadedFiles\[Dummy4.xlsx]dummy 3'!$D$9+'E:\DownloadedFiles\[Dummy4.xlsx]dummy 3'!$D$9+'E:\DownloadedFiles\[Dummy3.xlsx]dummy2'!$D$9


    In the 1st example, the 2 files referenced happen to be the same but in the 2nd example there are 3 files in the formula and 2 of those are unique.

    when there is more than one file and if these are unique then I need name of both the files "File used" column .


    For example in the 2nd example you mentioned 2 unique files are Dummy4.xlsx and Dummy3.xlsx . so I would like the output in the "File Used" column to be like this:


    1. Dummy4.xlsx

    2 .Dummy3.xlsx

    One way to process the input file into the output you have suggested is via a stand-alone macro as per attached:

    InputExtractor v1.xlsm


    This will process all the sheets in the specified input file (based on the sample provided) and write the results to the specified output file.

    The "template" used for output file is stored as the Output sheet in this stand-alone macro.


    Based on the sample provided, the output file looks as per attached:

    Output.xlsx

Participate now!

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

Register Yourself Login