Converting lots of little excel into word?

November 19, 2012 4:51 AM

ExcelFilter: I have a whole bunch of excel files. More than 100 of them. Each file has about one page of print area, but some in portrait and others in landscape. They are mostly text in cells; not a lot of formulas being used. I would like to (1) merge all these files into one Word document; (2) have a page break so that each file (more or less) is one page; and (3) do it with a minimum of cutting and pasting. Opening everything as a PDF and viewing in Preview is also an option, but less optimal since I'll certainly need to adjust some formatting and page breaks. Maybe on publisher? Is anything like this possible? Or do I need to do it page-by-page? What would you do? I'm on a Mac 10.8.2.
posted by RandlePatrickMcMurphy to computers & internet (9 answers total)

Perhaps there's a way to do this with Automator?
posted by RandlePatrickMcMurphy at 4:55 AM on November 19, 2012

So you want 100 pages of word, with a snapshot of an Excel file?

What exactly are you doing with it? Is it to be published somehow?

I think knowing what you're aiming for ultimately would really help determine the best method for doing this.
posted by Ruthless Bunny at 5:41 AM on November 19, 2012

If you want to do *exactly this*, you're going to have to brush up on your Word VBA automation skills and spend at least a solid morning debugging some poorly documented features.

However, if you're willing to compromise, you could use Acrobat. On windows at least, using Acrobat, you can collate a bunch of Excel files into a single PDF file. As long as you setup the print area and page formatting in each Excel file beforehand, Acrobat will take care of the rest and it'll look good.
posted by Mons Veneris at 5:53 AM on November 19, 2012 [1 favorite]

This is probably easy to do with a macro. Publisher might be a better option, since Word will try to take over the formatting. But maybe not if you insert the excel files as objects.

The macro would look something like this. (this is not code, obviously. Just the logic.)

for each file in $directory
create new page
insert file.printablearea as object
loop
posted by gjc at 6:24 AM on November 19, 2012

Thanks. Yes, I was looking to get this into a publishable format. Turns out with automator I can accomplish something similar - format the xls, convert to pdf and combine the pdfs. Thanks...
posted by RandlePatrickMcMurphy at 6:34 AM on November 19, 2012

This can be done, but there are issues. Short of learning VBA or some other language, there are a couple of options depending on what software you have available. A quick and dirty way to do it is via Acrobat Pro’s Combine files. Also, good ole ASAP Utilities will allow you to combine files, but you do have to deal with page breaking later.

If you have Acrobat full version and it fully and correctly installed, you can simply drag and drop the Excel sheets to a dialog and it will do the work for you. The major caveat is your Excel print area. Should you have a sheet that is too wide for a page, it will break to the next page. The solution is to set the print sizing to 1 page wide by 999 pages long. That way, it will come out as one page. You can also specify landscape or portrait at that time. But this has to be done in advance of the merge and done for each Excel file. There is probably a way to batch process the page setup issue, but I don't have that answer. I know there are utilities for Word batching, but have never had a chance to really test them out.

Another option is to use an Excel sheet aggregator to combine all sheets into one long sheet. Following that, you have to do some clean up and deal with print sizing. Tedious, but not all that bad with a few minor tricks using sorting and deleting en masse of empty rows

Bottom line, there is so single magic bullet for what you want to do. It is very possible to create what you have described, but it will probably a be a process of a few steps and not a one-button operation. There are simply too many variables from the individual sheets to account for if you want to make this into a super-clean presentable form (as opposed to say, a functional backup).

Alternately, if you are just looking to make a single page backup and don’t care too much how it looks, that is really not a big deal. You still have to push some buttons, but most of the finessing is removed from the job, which makes things far easier. I do this stuff all the time, but given that each set of source files is different, I am hesitant to suggest a single best solution. As with any job that I do, without seeing the data first, it is impossible to really provide a decent solution.

If interested, MeMail me and i can look a bit further into it for you.
posted by lampshade at 9:24 AM on November 19, 2012

Another approach you can take is moving all of these Excel sheets to a single Excel file. That way you'd end up with one Excel file with 100s of tabs. It is manual but at 5 seconds per sheet, it would take less than 10 minutes. Create a new blank Excel file 'Dest'. Then go to each of the source Excel files and right-click on the sheet name (tab on the bottom) > Move or Copy > To book: Dest. Every so often, save Dest. Close the source files without saving. Soon you will have one huge Excel file with lots of tabs. Then you can try to convert that to Word.
posted by chime at 8:16 PM on November 19, 2012 [1 favorite]

Excel file with 100s of tabs. It is manual but at 5 seconds per sheet, it would take less than 10 minutes.

FYI - ASAP Utils has a nice multi-file import function for that. Takes more time to select all the sheets than actually do the import. One caveat is that it works best with single tab XLSX files. Multi page per sheet is OK though.
posted by lampshade at 5:12 AM on November 20, 2012

Thanks - I'll give ASAP a try...
posted by RandlePatrickMcMurphy at 4:52 AM on December 3, 2012