Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

I am in need of some advice/references. I am currently trying to develop a legal document interface. There are certain fields in which I need to query out of my sql db and have those fields output into a document that can be printed. I am trying to develop a user interface where people can enter fields that will output to a document template but at the same time I need the template to be able to pull data from the SQL database. This is the reason why I think that VB might be my best choice and because it is one of the only OOP languages I am familiar with presently. Does anyone know that best way to be able to handle this type of job??

I know that you can use VBA within MS Word and have the form output variables to a word template. But, is there a way to have the word document also pull information from the SQL db? Is the best option to use VB linked to SQL and run queries to get the information from the database and then have it output to a for within VB? Is it possible for VB to be linked to a SQL db and output variables and SQL fields to a Word Template?

I have looked into Mail Merge and I see that it allows users to pull data from an Access query but I dont think it would be easy to automate and it seems that users would need to have an advanced knowledge of MS Word and Access to handle this. I am not finding much useful information online so I came here.

Any advice or references would be greatly appreciated. If there is a better way please let me know.

share|improve this question

5 Answers 5

up vote 1 down vote accepted

Look into using MS Office Automation

You can automate the mail merge as well, Basically you can automate anything you can do in office.

All the objects used for creating office macros are accessible for use outside of office. So if you want to know how to do something via code, simple record a Macro then look at the code and translate it to your language of choice.

share|improve this answer
    
Automation, which can be done in VB6 and VB.NET too, is the first thing that came to mind. –  Joshua Drake Apr 6 '12 at 14:32
    
@Morons - Thanks for your response. So do you think that my best option would be to use office for this type of situation?? Great article by the way. –  CLO_471 Apr 6 '12 at 14:35
    
@Morons I think that Word must be installed for this to work. Or am I wrong? –  Christian P Apr 6 '12 at 14:36
    
If you go with something else, it need to exports to Word very well. Because your users are going to want to edit the final DOC. Most things the export to word LOOK nice in words but editing those docs PITA. –  Morons Apr 6 '12 at 14:39
    
Yes, Word must be installed on the computer running the code. (So The client or the server, but not both. Of course the client will need word it is going to open the doc) –  Morons Apr 6 '12 at 14:40

You have the following options (at least):

1-Pure VBA

2-.NET application with VBA automation

3-.NET application without VBA automation

The problem with all 3 approaches is that you may need to re-work the code if the template changes.

The problems with 1 and 2 are: - User must have Office installed

  • It is not trivial to deploy office macros unless you know what you are doing well

  • VBA does not have fancy GUI

  • What if Office version is upgraded or if not all users are using the same version?

Option 2 is specifically sensitive to office libraries availability on the user's machine and uses COM which is something you want to avoid if you can.

Option 3 could be used by obtaining a 3rd party tool that could generate MS-Word document without the need for the user to have MS-Office installed. Depending on the complexity of the output document, you could even use a free report writer to display the resulting document as a report or save it as an MS-Word document. Almost all report writers can do that. You could define your input text and format either in a MS-Word document or as a separate application-specific file using HTML, XML, etc.).

This option provides an easier installation and distribution but is more complex to develop and may cost you some money.

The advantages of this is that it does not depend on MS-Office version installed much. MS has always been compatible with its older file formats.

share|improve this answer
    
All users will have the MS Office installed as well as my automation server (core i5, 8 RAM, Win7). Thanks so much for the reply. You provided me with alot of useful information and things to consider. At this point I am thinking that my best bet is going to be to use strictly VBA for cost reasons and keep things the same for users. Most users at my firm are not accepting to change so I try to create things that will not affect or change the way they do things. –  CLO_471 Apr 9 '12 at 13:02

If you are going the VBA route, connecting to an SQL database and pulling some information from there is easy. You can use classic "ADO", here

http://www.vbexplorer.com/VBExplorer/vb_feature/june2000/Database_Beginner_ADO_DAO.asp

you will find some examples. Of course, when you have to design a dialog based user interface, I would prefer to use VB.NET instead (together with automation, just like the others suggested.)

share|improve this answer

The thing about Office Automation on a server is that it's very heavy. I've found that working with third party tools that manipulate Word documents directly are the better route. For instance, I've successfully used SyncFusion's tools to create excel and word documents from a data-driven app. You want something that can work with word templates (that you can possibly store in the database) and can do something similar to mail-merge with your data. Funny thing, I've used this in a legal office application just as you are describing. The throughput of the syncfusion library puts office automation to shame and it produces very high quality documents.

Here is a link to samples of working with their library

share|improve this answer
    
Thanks for the post I will take a look at this utility as well and see what I can come up with. –  CLO_471 Apr 6 '12 at 16:31
    
If you dont mind me asking.....which edition of SyncFusion did you use?? –  CLO_471 Apr 6 '12 at 16:37
    
No I don't mind...sorry for being vague. I had a subscription to the full suite but the one I'm referring to here is the Reporting Edition it includes DocIO, XLSIO, PDF, Project, and others. –  Mike Brown Apr 6 '12 at 17:16

In addition to @Morons answer you could try a Macro to handle the more complicated bits of the Mail Merge automatically for your users so that they wouldn't need to know how to do anything except run the macro your create. Look into Visual Basic for Applications for more information on macros.

VBA enables building user defined functions, automating processes and accessing Windows API...

share|improve this answer
    
I know that I can write macros for Mail Merge but would I be able to incorporate SQL information into the form as well. –  CLO_471 Apr 6 '12 at 14:39
    
@CLO_471 I've edited the relevant section from Wikipedia into my answer. –  Joshua Drake Apr 6 '12 at 14:42

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.