Excel Files Combined

deefel
deefel used Ask the Experts™
on
I have a large number of excel files I need to open and run a macro to count columns and count non-blank and blank values on a row.  The first 8 columns are the same and then after ~ the columns per file various.  It could be 1 column to 45 columns.  Also a file could have only 1 record or it could have over 300 records.  I am trying to figure out a way to open all of the files at once apply the macros to remove a few columns and add 3 columns to hold the "total count", "blanks", and "completed" values, along with having  the output from each file combined into one larger file.  Any help would be greatly appreciated.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
SAP/ERP Data Player
Commented:
Please try Power Query, menu: Data>GetData>Launch Power Query Editor
Then, you can add you Excel file by click New Source>from file one by one
Goto Append Queries, you can combine all data from your selected files into one sheet.

And, after you set up this connection, you can just click refresh to update the data from your files
Untitled.jpg

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial