Forum Discussion

PaulaSpinola's avatar
PaulaSpinola
Copper Contributor
Sep 05, 2019

Consolidating all excel files within a unique folder

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)
 
Folder with 113 files
excel
Macros and VBA

4 Replies

  • Kodipady's avatar
    Kodipady
    Iron Contributor
    Sep 05, 2019

    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. 

     

    • PaulaSpinola's avatar
      PaulaSpinola
      Copper Contributor
      Sep 06, 2019

      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)?

      • Kodipady's avatar
        Kodipady
        Iron Contributor
        Sep 06, 2019

        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. 

Resources