Search

Recent Entries

ZeroSSL with IIS 10
Account Breach Monitoring
DBML Visualizer VERY Slow - solved!
Poor Man's SQL Log Shipping (Kinda)
Excel Column Letter To Number.com
Easy & Powerful Ecommerce Solution
asp:Menu Flicker on Page_Load - Solved
Windows Server 2008 Install DLL into GAC

Categories

Skip Navigation Links.
You are here: Rants, Rave, & Tips

 

By Ted Krapf on 3/15/2013 6:47 AM
A client yesterday had a common problem, "Ted I have 100+ Excel documents that I need to be able to quickly search through, and I'd like to combine all those work books into a single .xlsx file so that I can just search in one document."

Sounds simple enough right?  The first thing we tried together was scouring the web for a VBA script to do this.  Seems simple, get a list of the files in a directory, open each doc, and copy and paste its contents into the main document.  Well after an hour of stumbling through VBA syntax, help files, blogs, etc, we just couldn't find one that would work.  The best we found that worked would combine the all the worksheets of all the other documents into the main workbook as worksheets -- still leaving us having to manually copy and paste 100's of worksheets into one.

We had also found an array of different commercial 'combine' tools that ranged from dollars to $1,000's.  Needless to say, being a programmer, I wanted to see how I could do this myself and so I could share the solution with you!...