Hello
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?