RadarBob
04-14-2003, 09:57 PM
Dispite several books and the on-line references from MS word, I can't seem to get going on an application I need to build. I just can't seem to get the pieces together. Any help, hints, code, references, other help web sites, etc. are greatly appreciated.
The Problem Domain
I need to bulid a complex report from data pulled from an MS SQL Server database (via a web page). Our technique has been to save the data into and excel spreadsheet and then interactively mailmerge to a Word template. Mailmerge won't work for the heirarchial dataset I have - I need to read an indeterminate number of records for each page of my report document; so I must hand-code a solution.
The Concept
The user will have a pre-existing Word template. A Word macro - more strictly speaking, a VBA program (the program I need to write) - is automatically triggered when a new document w/ this template is created, which will do the following:
Prompt for path/filename of the Excel spreadsheet. This file is assumed to be on the local drive, but not a specific location or file name.
The spreadsheet is read / fed into a recordset object
Using the recordset, the data for that report page is placed on the template; bookmarks mark where each specific data field goes.
Multiple records, all with the same "master Key", and nested loops for sub-keys, are read and respective data is placed on the template page. The original data set is in master-key / sub-key(s) order.
When the "master Key" field changes, start a new page.
When finished, the user saves / prints / views the report as needed.
My concept calls for a recordset because I'm familar with those from writing web pages; and I found an article about putting an excel spreadsheet into a recordset. If I can manipulate the spreadsheet directly on a "record" level, then that would be ok I guess - cut out the middleman (the recordset).
Please don't respond with something like "why don't you use (your favorite report tool here)" That's a non-starter around here.
The Problem Domain
I need to bulid a complex report from data pulled from an MS SQL Server database (via a web page). Our technique has been to save the data into and excel spreadsheet and then interactively mailmerge to a Word template. Mailmerge won't work for the heirarchial dataset I have - I need to read an indeterminate number of records for each page of my report document; so I must hand-code a solution.
The Concept
The user will have a pre-existing Word template. A Word macro - more strictly speaking, a VBA program (the program I need to write) - is automatically triggered when a new document w/ this template is created, which will do the following:
Prompt for path/filename of the Excel spreadsheet. This file is assumed to be on the local drive, but not a specific location or file name.
The spreadsheet is read / fed into a recordset object
Using the recordset, the data for that report page is placed on the template; bookmarks mark where each specific data field goes.
Multiple records, all with the same "master Key", and nested loops for sub-keys, are read and respective data is placed on the template page. The original data set is in master-key / sub-key(s) order.
When the "master Key" field changes, start a new page.
When finished, the user saves / prints / views the report as needed.
My concept calls for a recordset because I'm familar with those from writing web pages; and I found an article about putting an excel spreadsheet into a recordset. If I can manipulate the spreadsheet directly on a "record" level, then that would be ok I guess - cut out the middleman (the recordset).
Please don't respond with something like "why don't you use (your favorite report tool here)" That's a non-starter around here.