Home

Consolidating all excel files within a unique folder

%3CLINGO-SUB%20id%3D%22lingo-sub-838630%22%20slang%3D%22en-US%22%3EConsolidating%20all%20excel%20files%20within%20a%20unique%20folder%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-838630%22%20slang%3D%22en-US%22%3E%3CDIV%3EI%20would%20like%20to%20combine%20data%20from%20more%20than%20100%20excel%20files%20(all%20in%20the%20same%20folder)%20into%20a%20unique%20file.%20The%20range%20from%20each%20spreadsheet%20that%20should%20be%20copied%20into%20a%20unique%20file%20goes%20from%20row%20A8%3AK8%20until%20A%3F%3AK%3F%20where%20%22%3F%22%20refers%20to%20the%20last%20row%20of%20the%20respective%20spreadsheet.%20Also%2C%20the%20names%20of%20the%20files%20are%20not%20standardised.%20Would%20it%20be%20possible%20to%20write%20a%20VBA%20code%20that%20asks%20excel%20to%20call%20every%20file%20within%20a%20folder%3F%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3EExample%20of%20a%20spreadsheet%20(selected%20cells%20should%20be%20copied%20into%20a%20new%20unique%20file)%3C%2FDIV%3E%3CDIV%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F130213i1770F272BBCB22DA%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20alt%3D%22clipboard_image_1.png%22%20title%3D%22clipboard_image_1.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3EFolder%20with%20113%20files%3C%2FDIV%3E%3CDIV%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F130223i03FD02D13BAE196D%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20alt%3D%22clipboard_image_3.png%22%20title%3D%22clipboard_image_3.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-838630%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-839168%22%20slang%3D%22en-US%22%3ERe%3A%20Consolidating%20all%20excel%20files%20within%20a%20unique%20folder%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-839168%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F396715%22%20target%3D%22_blank%22%3E%40PaulaSpinola%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20recommend%20using%20power%20query%20functions%20instead%20of%20writing%20a%20VBA%20script.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20follow%20the%20menu%20option%20Data-%26gt%3BGet%20Data-%26gt%3B%20From%20File%20-%26gt%3B%20From%20Folder.%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20will%20pick%20all%20files%20and%20then%20you%20need%20to%20apply%20the%20consolidation.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-840388%22%20slang%3D%22en-US%22%3ERe%3A%20Consolidating%20all%20excel%20files%20within%20a%20unique%20folder%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-840388%22%20slang%3D%22en-US%22%3E%3CP%3EMany%20thanks%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F368896%22%20target%3D%22_blank%22%3E%40Kodipady%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20would%20work%20but%20I%20actually%20need%20the%20colour%20formatting%20to%20be%20the%20same%20as%20in%20the%20original%20file%20(as%20I%20use%20it%20to%20run%20a%20macro%20afterwards).%20Is%20there%20a%20way%20different%20files%20within%20a%20folder%20could%20be%20combined%20while%20keeping%20the%20original%20format%20(either%20using%20power%20query%20or%20VBA)%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-841019%22%20slang%3D%22en-US%22%3ERe%3A%20Consolidating%20all%20excel%20files%20within%20a%20unique%20folder%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-841019%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F396715%22%20target%3D%22_blank%22%3E%40PaulaSpinola%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20principle%20everything%20you%20can%20do%20using%20keyboard%20%2C%20VBA%20can%20do%20it%20for%20you.%20hence%26nbsp%3B%20I%20believe%20it%20should%20be%20possible%20to%20keep%20same%20format%20in%20a%20VBA%20script.%20i%20am%20not%20an%20expert%20on%20VBA.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EUsing%20power%20query%2C%20it%20may%20not%20be%20possible%20to%20keep%20same%20format.%20but%20it%20provides%20lot%20more%20flexibility%20to%20consolidate%20and%20transform.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-842336%22%20slang%3D%22en-US%22%3ERe%3A%20Consolidating%20all%20excel%20files%20within%20a%20unique%20folder%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-842336%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F368896%22%20target%3D%22_blank%22%3E%40Kodipady%3C%2FA%3E%3C%2FP%3E%3CP%3EWhen%20using%20your%20suggestion%2C%20is%20there%20any%20way%20that%20I%20can%20tell%20excel%20to%20only%20consolidate%20data%20from%20row%208%20(until%20the%20last%20row%20in%20each%20of%20the%20spreadsheets)%3F%20I%20clicked%20on%20Combine%20%26amp%3B%20Edit%20(print%20screen%20below)%20but%20did%20not%20find%20any%20option%20to%20select%20a%20range.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F130584i088FE87843842270%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20alt%3D%22clipboard_image_0.png%22%20title%3D%22clipboard_image_0.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F130585i41943C20961B64D9%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20alt%3D%22clipboard_image_1.png%22%20title%3D%22clipboard_image_1.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
PaulaSpinola
Occasional Contributor
I would like to combine data from more than 100 excel files (all in the same folder) into a unique file. The range from each spreadsheet that should be copied into a unique file goes from row A8:K8 until A?:K? where "?" refers to the last row of the respective spreadsheet. Also, the names of the files are not standardised. Would it be possible to write a VBA code that asks excel to call every file within a folder?
 
Example of a spreadsheet (selected cells should be copied into a new unique file)
clipboard_image_1.png
 
Folder with 113 files
clipboard_image_3.png
4 Replies

@PaulaSpinola 

I recommend using power query functions instead of writing a VBA script.  

Please follow the menu option Data->Get Data-> From File -> From Folder. 

It will pick all files and then you need to apply the consolidation. 

 

Many thanks @Kodipady 

This would work but I actually need the colour formatting to be the same as in the original file (as I use it to run a macro afterwards). Is there a way different files within a folder could be combined while keeping the original format (either using power query or VBA)?

@PaulaSpinola 

In principle everything you can do using keyboard , VBA can do it for you. hence  I believe it should be possible to keep same format in a VBA script. i am not an expert on VBA.

 

Using power query, it may not be possible to keep same format. but it provides lot more flexibility to consolidate and transform. 

@Kodipady

When using your suggestion, is there any way that I can tell excel to only consolidate data from row 8 (until the last row in each of the spreadsheets)? I clicked on Combine & Edit (print screen below) but did not find any option to select a range.

clipboard_image_0.png

 

clipboard_image_1.png

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
38 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies