Sorry to ruin the fun in here, but there didn't appear to be any appropriate place to put this in. I have a project where I am getting 1,500 Excel tables. The tables need to be formatted and colored to look pretty, then imported into Word and laid out, and create a table of contents for each table title. I am going to deliver (1) a Word doc with a table of contents and 1,500 pretty tables, and (2) an Excel file with a straight list of pretty tables. I currently get 1,500 tables that look like this: I take a table design and "Copy > Paste Special > Format" 1,500 times, which keeps the data but styles the background. (some tables have more rows than others so i have to go back and touch up 50% of the tables and it comes out looking like this: I then copy all the rows in Excel and paste it into Word. I highlight the entire selection of table data and adjust the columns that get squished to even them out. I highlight all 1,500 table names and style them as a "heading", so I can make a table of contents. I also insert page breaks throughout the entire document to prevent tables from being cut off and partially displayed on the next page. The word doc looks like this: My questions/comments: Is there any way I can improve this process? I know that I can make a macro to import data from Excel to Word and it will automatically style it. The benefit to this is also if data changes in Excel, you only have to update one document. The downside to this is that it appears you have to make a lot of selections and import the tables to be styled one-by-one. Is this so? I need the Excel tables and Word tables to be styled. The way I am currently doing it seems okay, but Im curious if there is a better way? Can I insert "headings" in Excel and will it carry over to Word to make the table of contents easier to make? Is there anything I am missing that will let me take a ton of unstyled tables, apply a design to them and export them with a table of contents with page numbering?
There are a few ways I would approach this; I think you can embed excel tables in word. I seem to remember there is a way to point the word document at excel and tell it that you want all the tables from a spreadsheet. Don't ask me how though, I can't remember. The second option is to post the job either here in the trade, sell, buy section and say you will pay 1c per spreadsheet, or something similarly crazy. Or you could post the job on a website like oDesk or Guru, where there may be someone who will do it for not very much money. The last option, which is something I would try only because I know HTML, is I would copy all the data into a HTML document, and then do find and replace to set up headings and CSS codes, so that once the codes were in, I only need to use them once. Sorry I have not been of much help....
Hey there Yea your first suggestion is something I've tried. When I copied 100 tables at once and Paste Special into Word as Excel table, it actually scales down all tables to fit onto one page, instead of creating pages to make room from them