+ Reply to Thread
Results 1 to 7 of 7

Consolidate all data from multiple files into single excel file

  1. #1
    Registered User
    Join Date
    07-01-2020
    Location
    Yerevan
    MS-Off Ver
    2010
    Posts
    44

    Consolidate all data from multiple files into single excel file

    Hi All,

    I have dozens of excel files (each having different number of sheets). However each excel file has a sheet named "ALL"

    1. All the files are named differently
    2. All the workbooks have the same number (4) of sheets with same names ("001", "004", "058", "002").

    I would like to write a vba code that all data in the sheet "ALL" from different files will be consolidated into single new excel file without actually opening the files???

    I need this as I have dozens of files and it will take a long time to manually copy paste data manually.

    I will be very thankful for your help.

    See attached the sample of one of those files.
    Attached Files Attached Files

  2. #2
    Administrator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365 Win 11 Home 64 Bit
    Posts
    21,877

    Re: Consolidate all data from multiple files into single excel file

    Here is a tutorial on how to do this easily with Power Query

    https://www.youtube.com/watch?v=qbMW0iSgH4I
    Alan


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  3. #3
    Registered User
    Join Date
    07-01-2020
    Location
    Yerevan
    MS-Off Ver
    2010
    Posts
    44

    Re: Consolidate all data from multiple files into single excel file

    I would very much appreciate if we could do this through VBA (as for some reasons we do not use Power Query in my Company)

  4. #4
    Administrator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365 Win 11 Home 64 Bit
    Posts
    21,877

    Re: Consolidate all data from multiple files into single excel file

    Power query is available to you as an addin for Excel 2010.

    https://www.microsoft.com/en-us/down....aspx?id=39379

    Some questions.
    1. Are all the files to be consolidated in the same folder?
    2. If 2 is yes, then what is the path to the folder?
    3. Are there files in the same folder that should not be combined?
    4. If 3 is yes, either provide the names or move them to a new location.
    5. Should the consolidated data be in one sheet or should the data be separated into separate sheets for each file?
    6. If one sheet, should the data be identified to the original file?
    Last edited by alansidman; 07-21-2020 at 10:26 AM.

  5. #5
    Registered User
    Join Date
    07-01-2020
    Location
    Yerevan
    MS-Off Ver
    2010
    Posts
    44

    Re: Consolidate all data from multiple files into single excel file

    Here is answer to your questions.

    1. Yes
    2. C:\Users\emil abrahamyan\Documents\Accuity
    3. No
    4. All should be in the same one sheet
    5. Preferably yes

    let me know if you have other questions.

  6. #6
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    20,401

    Re: Consolidate all data from multiple files into single excel file

    Quote Originally Posted by lilit2222 View Post
    I would like to write a vba code that all data in the sheet "ALL" from different files will be consolidated into single new excel file without actually opening the files???
    Try
    Change Sheets(1) to suit.
    Please Login or Register  to view this content.

  7. #7
    Administrator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365 Win 11 Home 64 Bit
    Posts
    21,877

    Re: Consolidate all data from multiple files into single excel file

    Here is some untested code for you to try

    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Consolidate all data from multiple files into single excel file
    By lilit2222 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-14-2020, 09:26 AM
  2. Extract data – Multiple Files – to Single File – in Excel
    By Nostradamus in forum Excel Programming / VBA / Macros
    Replies: 17
    Last Post: 03-11-2017, 12:00 PM
  3. Consolidate data from multiple excel files into single file
    By hkkk in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-22-2013, 03:34 AM
  4. Consolidate data from multiple excel files into single file
    By hkkk in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-22-2013, 12:36 AM
  5. How to consolidate data from multiple excel files into single sheet in new book?
    By Pradeep M B in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 04-07-2013, 09:37 AM
  6. [SOLVED] Macro to read the multiple csv files and consolidate into single excel
    By parthmittal2007 in forum Excel Programming / VBA / Macros
    Replies: 20
    Last Post: 01-03-2013, 03:40 AM
  7. Consolidate Data from Multiple Files (Different File Names) into a Master File
    By dspraveen_23 in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 12-08-2012, 12:59 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1