Converting lots of Excel files to Word

Question

I have around 30 Excel spreadsheets, each with 20+ worksheets. Each of these worksheets follows the same basic format, though there are slight differences. I also have a Google doc with sections that match each of the worksheets.

Somehow I need to merge the data from the spreadsheets and the Google doc into a single, searchable, editable document.

So far, the plan is to have an intern copy/paste from Excel into the appropriate section in the Google doc, but besides being akin to torture, I'm afraid this approach will take days.

Can anyone think of a way to automate at least some of this?

I've been thinking that if I could somehow take all of the Excel data and migrate it into either Microsoft Word or a Google Doc, that would be a good first step.

I have used Google Apps Script in the past and could probably figure out how to write a macro in Excel, but I can't wrap my head around how I would actually accomplish what I need to do.

I'd appreciate any suggestions.

Answer 1

Wanted to provide an update in case anyone else ever has a similar problem...

I ended up moving all of the existing spreadsheets to Google Drive and using Google Apps Script to iterate through the folder to get the name and id of each file and then iterate through all worksheets of each of those spreadsheets, copy all data from each sheet and append it to a new master spreadsheet.

I then migrated the existing Google Doc to a Google Sheet so I could sort.

After that I manually copied the applicable sections from this Google Sheet into the correct, corresponding section of the master spreadsheet. I'm sure someone smarter than me would have been able to script that last part as well, but I was having trouble figuring out the logic and decided to brute force the last step.

Answer 2

Less of a solution, more of a proposal. Too long for a comment.

If you drag-and-drop the Excel file into Word it embeds a copy. The original can be renamed/removed, and the embedded version can still be opened and edited. You double-click, and it opens the data in Excel.

According to my searches for some people this file embeds as an icon into Word, like so:

But for most people (and for me) it embeds as a table, like so:

The problem with this is that it only shows the first worksheet. If you double-click you can still see and edit the rest of the sheets, but they won't show in the Word Doc.

I personally like the icons idea, it makes for a compact master document that contains 20+ Excel workbooks, each with 30+ worksheets. You can show all icons on a single page, or split them into categories. But it may not be what you need.

If you want to do the icons, and drag-and-drop inserts them as tables, it may be possible to change that behaviour. For some people (this will probably depend on OS and version of Office, it doesn't work for me on Mac using Office 2016), you can right-click on the embedded table/icon and "Convert" it from one type to the other, as described here. You may be able to select multiple and convert (although I doubt it will allow that). Or at that point, it may be easy to record a macro.

Inserting the Excel workbooks as icons can be done, but requires more steps. However if you are only working with 20 workbooks instead of 600 worksheets, it becomes more manageable. Go to Insert > Object, ensure "Display as icon" is checked, then select From File and open the file. Repeat for each workbook.

Or if you really need the tables to be visible, for every worksheet, I think you are looking at writing VBA to do it. I wouldn't know exactly where to start, it would likely take me half a day to nut it out. I'll leave that one to you, or another helpful soul who is a VBA expert.