Beginner at Microsoft Office Macros

Question

Actually, I'm not a beginner. I know nothing about them, though I do have some programming background.

This is the thing: I have a couple of Word documents and an Excel spreadsheets. The documents need to grab some data from the spreadsheet and then print. There needs to be one document per spreadsheet row, and they need to be printed all together.

I'm not asking for code or anything; I just want to know what's the right tool for the job, and if someone could point me to a tutorial or reference or something.

This is for Office 2003 (or XP, I'm not sure).

EDIT: It seems like there are many ways to do this, so it'd be great if someone listed the pros and cons of each solution. Keep in mind that it's something that will be done many times, and once programmed/recorded/whatever it should be easy to use for someone who is not a programmer.

Answer 1

You can load your Excel spreadsheets via VBA in an ADODB and read the ADODB row by row.

How To Use ADO with Excel Data from Visual Basic or VBA

Answer 2

Javier,

Couldn't find a good tutorial, but something like this should help you get going:

You can enable the developer toolbar, if it's not available from Word options. Then, click on the Visual Basic button and add a procedure or function that can be called from your document or a command button in the Word UI.

Sample showing some super basic Excel integration:

Public Function GetValue()

    Dim myExcel As Excel.Application
    Dim myWorkbook As Excel.Workbook
    Dim myWorkSheet As Excel.Worksheet

    Set myExcel = CreateObject("Excel.Application")
    Set myWorkbook = myExcel.Workbooks.Open("c:\temp\myworkbook.xlsx")


    Set myWorkSheet = myWorkbook.Worksheets(1)

    Dim cellValue As String

    cellValue = myWorkSheet.Cells(1, 1).Value

    GetValue = cellValue

End Function

This will require you add a reference to Excel object library (type library) from the Excel developer IDE.

Answer 3

You can accomplish this with the built-in mail merge facility in Word. There's a walkthrough of how to use it in Word 2003 here

---

edit: further to the question in the comments, once you have set up the mail merge document, you can save it complete with its link to the data source. This means that when the document is opened again the user just needs to say "Yes" to the choice of data being merged.

The user can (independently) also choose to have the mail merge toolbar displayed. Clicking on the "Merge to new document" button on the Mail Merge toolbar would cause the merged letters to be generated. If the toolbar isn't displayed then they need to go Tools > Letters and Mailings > Mail Merge and use the wizard to complete the job