Using Microsoft Query to summarize multiple workbooks

  • I put together a Excel 2016 spreadsheet using Power Query that brings in data from 7 different workbooks. The intended use of this file was for a business office using Excel 2013.
    I learned that corporate IT department will not allow installation of Power Query add-in for Excel 2013.


    I have been looking at using Microsoft Query to do what I was able to do with Power Query.
    I know very little about Microsoft Query & SQL.
    I have 7 different user workbook files each with one worksheet that I need to bring into one Excel workbook sheet summary file.
    Is there a way to write Excel MS Query to bring in the 7 different worksheet files data into one Excel summary sheet file and be able to refresh the summary file on a regular basis??
    I have found postings on how to access multiple worksheets in one workbook file, but I stumped on how to access 7 different workbook files, again using MS Query.


    I was also looking at the "consolidate" feature if this would do what I need.


    Any help / guidance with how I can accomplish my task would be mosy greatly appreciated.

  • Quote

    I learned that corporate IT department will not allow installation of Power Query add-in for Excel 2013.


    Monumental stupidity right there.


    Chances are you you will need to write some VBA... start with a blank workbook that you will use as the "Summary" workbook... You will have to point to each workbook, open it, read it and bring that data into the summary workbook. Take a read of this useful article to get you started...


    https://msdn.microsoft.com/en-us/lib...or=-2147217396



    But honestly, I would be kicking up a stink with your management team as you have a legitimate business problem and a solution already available... Ask Your corporate IT department will they support your VBA application after you develop it ;)


    Ger

    Check out our new reputation system. Click on the Like button under the post!
    _______________________________________________

    [SIZE=8px]There are 10 types of people in the world. Those that understand Binary and those that dont.


    Why are Halloween and Christmas the same? Because Oct 31 = Dec 25...


    The BEST Lookup function of all time


    Dynamic Named Ranges are your bestest friend[/SIZE]
    _______________________________________________