Hi all,
I have received a project to write a VBA program to convert an Excel file of specific format to a Word doc file. Before I forge ahead, I want to take the obvious precaution and hear different approaches that experts recommend, and which is the best – or as is often the case, which is the least bad.
The Excel file has many worksheets. Many of the worksheets contain more than one visibly distinct logical table. Each table includes column headers and row headers, i.e., text descriptions for columns and rows. Each table is different.
The sample Excel file given to me has all data as “zeros”. However there will be many clients for this Excel file and VBA program that I write, and each client will fill in the numerical data in its own way. Thus, all tables are fixed format, i.e., columns and rows are the same and text descriptions are the same; only the data changes.
Some of the Excel tables seem to be too big to fit on a Word doc page.
So far I have considered:
1. Straight VBA ‘copy and pour’ of Excel ‘tables’ into Word doc, and then post formatting of the resulting Word tables.
2. Pre-create a Word document or template, pre-preparing all tables in the Word doc for matching the Excel ‘tables’. And then perform a logical copy of the data cell-by-cell from the Excel ‘tables’ to the Word doc tables.
3. Somebody suggested to me to use MailMerge in the Word doc and import Excel worksheets.
4. Somebody suggested to me to insert an SQL query to import Excel worksheets into the Word doc.
Opinions on all the above?
TIA
-avraham
