SOLVED

Workbook link to current folder

Copper Contributor

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?

10 Replies

@abyerley Yes, you can use a folder as a source in the GetData menu.  That will allow you to extract details from all workbooks within that folder.  

 

DexterG_III_0-1663805803520.png

 

@DexterG_III Thank you! I'm not familiar with these query functions, so I'll take a closer look. Much appreciated.

@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,

Dexter

Thank you Dexter. Here's what I'm trying to do (knowing that there may be a more elegant way to do it): I have a series of folders that contain 21 data export files (also .xlsx). I need to capture one row from each of those files (always Row 10) and parse it into two columns in the destination workbook. (From there, the workbook does what it needs to do.) Additionally, I need the workbook to function as intended when copied into another folder of 21 data files. I hope this makes sense, and thank you again if you can guide me further.

@abyerley Okay thanks.  I'll set up an example query.  

 

What is being parsed in row 10, if I may ask, so I can include in the query example to get you started?  

 

Dexter

Essentially, row 10 of the first data file is turned into two columns in the destination spreadsheet. Cells A10, C10, E, G, etc. become column 1, Cells B10, D10, F, H, etc. become column 2. Row 10 of the second data file is parsed in the same way into columns 3 and 4. And so on.

@abyerley Just to clarify, you'd like the extracted data in a single row (42 columns in total  or 21 files * 2 columns) rather than have 21 rows with 2 columns?  

We'll end up with 42 columns in total.
best response confirmed by abyerley (Copper Contributor)
Solution

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

 

DexterG_III_0-1663866898350.png

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

DexterG_III_3-1663867366033.png

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.  

DexterG_III_5-1663869204442.png

 

Once configured correctly you can see the output is 42 columns comprised of merged data from the 21 files:

 

DexterG_III_4-1663868989274.png

Hope this helps and good luck. 

@DexterG_III Thank you very  much! I really appreciate your work on this.

1 best response

Accepted Solutions
best response confirmed by abyerley (Copper Contributor)
Solution

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

 

DexterG_III_0-1663866898350.png

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

DexterG_III_3-1663867366033.png

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.  

DexterG_III_5-1663869204442.png

 

Once configured correctly you can see the output is 42 columns comprised of merged data from the 21 files:

 

DexterG_III_4-1663868989274.png

Hope this helps and good luck. 

View solution in original post