Sep 21 2022 03:24 PM
Sep 21 2022 03:24 PM
Hello! I'm looking for a way to create a link to another workbook that is always in the same folder as the destination workbook. I have a series of folders that contain files of exactly the same name. I'd like to be able to copy a workbook into each of these folders and have it look for data within the other workbooks in that folder. Is there a way to do this?
Sep 21 2022 11:08 PM
@abyerley My pleasure, although I admit I've offered nothing tangible. Only because of the vast possibilities - the capabilities are numerous and in some cases profound but are very situation specific.
For example, I remember that an inventory file was saved in the same folder every day. In order to plot inventory through time (as each file is always an independent snapshot), I relied on a lot of VBA code and macros to determine whether a new file existed and, if so, how to append it using the legacy import process. This situation today requires no VBA, and every single file within a folder (given the same layout and headers, etc), can be automatically consolidated into a single table.
If you're not familiar with PowerQuery, it might appear daunting without any further instruction. I'm willing to help more if you are able to share additional information (e.g. example files without any sensitive information and specifics about what components you are looking to extract from each of these files).
Best of luck,
Sep 22 2022 06:31 AM
Sep 22 2022 08:18 AM
Sep 22 2022 10:54 AM - edited Sep 22 2022 10:55 AMSolution
@abyerley Okay the example is attached. I've tried to make this as dynamic as I can so if the filename or location of the MasterFile changes, the query will update accordingly. The attached workbook is the one which consolidates row 10 from each of the files within the same folder:
You can see more easily than the end product how this query works to consolidate data from every workbook in the same folder except the master file
And then, of course, the data transformation steps to merge the columns and eventually transpose them into columns as opposed to rows as shown above. If additional files are added to the folder, they will automatically be transformed along with the others.
I believe most of the query will work up until the "Inserted Text Between Delimiters" step. These steps are very dependent on your naming convention of your files so it will need to be updated accordingly. You might consider making a copy of the file (one for implementation and the other for reference). Then hold Alt while opening the Excel application to start a new instance. This should allow you to open the query editor in both documents & step through the query of the reference file, while making necessary adjustments to your implementation file steps highlighted below.
Once configured correctly you can see the output is 42 columns comprised of merged data from the 21 files:
Hope this helps and good luck.