none
For Each row in Excel, Create word Document based on template and populate data RRS feed

  • Question

  • Hi,

    I have created several VBA projects over the years and i am now looking at converting some of these to VSTO to make it easier to deploy and enhance them. I am using Office 2010 and Visual Studio 2010 and can am able to use VB or C# for development.

    What i would like is to have an excel spreadsheet with data and a button. When the button is clicked it loops through each row of data on the spreadsheet and creates a new word document based on a template and populates bookmarks with the data from the excel row.

    Once i have this working i would then hookup custom properties in to the word document, eg Version Number, and Current State of document (as the document will go through several processes)

    Could anyone point me in the right direction or offer any help on how i might achieve this. I am extremely new to VSTO so i am trying to learn by using projects that are relevant to me and not just Hello Word samples.

    Many Thanks

    Jason

    Friday, February 15, 2013 12:59 PM

Answers

  • Hi Jason

    Before you dig in, I feel I should mention that there are alternative, and much more efficient options to do what you propose.

    1. Open XML (SDK), working with the closed files for both Excel and Word

    2. Open XML on the Word side; OLE DB connection on the Excel side

    3. Use content controls in Word instead of bookmarks, in any case

    If you really want to do it the way you describe, VB.NET code will essentially be the same as VBA code as far as the manipulation of Word and Excel goes, with some small adjustments. Do you already have this in VBA?

    On the VSTO side, you'd use the VSTO technology to create the UI in the Ribbon and hook it up to your code. Here's your starting point:
    http://msdn.microsoft.com/en-us/library/bb386097.aspx

    Then, even though you don't want "Hello World" I do recommend you at least read through the information on working with the Word and Excel object models in VSTO. No need for us to have to repeat something that's already explained in the documentation...
    http://msdn.microsoft.com/en-us/library/bb386107.aspx
    http://msdn.microsoft.com/en-us/library/bb157892.aspx


    Cindy Meister, VSTO/Word MVP, my blog

    Friday, February 15, 2013 5:34 PM
    Moderator

All replies

  • Hi Jason

    Before you dig in, I feel I should mention that there are alternative, and much more efficient options to do what you propose.

    1. Open XML (SDK), working with the closed files for both Excel and Word

    2. Open XML on the Word side; OLE DB connection on the Excel side

    3. Use content controls in Word instead of bookmarks, in any case

    If you really want to do it the way you describe, VB.NET code will essentially be the same as VBA code as far as the manipulation of Word and Excel goes, with some small adjustments. Do you already have this in VBA?

    On the VSTO side, you'd use the VSTO technology to create the UI in the Ribbon and hook it up to your code. Here's your starting point:
    http://msdn.microsoft.com/en-us/library/bb386097.aspx

    Then, even though you don't want "Hello World" I do recommend you at least read through the information on working with the Word and Excel object models in VSTO. No need for us to have to repeat something that's already explained in the documentation...
    http://msdn.microsoft.com/en-us/library/bb386107.aspx
    http://msdn.microsoft.com/en-us/library/bb157892.aspx


    Cindy Meister, VSTO/Word MVP, my blog

    Friday, February 15, 2013 5:34 PM
    Moderator
  • Hi Cindy,

    Thank you for your reply. I do indeed have a basic idea of what i want to achieve written in VBA in an old project. I prompt a user for a month and year and then loop through an excel sheet to gather the required data, create an instance of the template then populate the bookmarks and save the file to Pdf before emailing out. I have attached code below. Apologies at the state of it, I am self taught in VBA so I have been slowly piecing this together so it could do with some refactoring and improvement.

    Basically the example below searches all Swimmers to see if a 'Swimmer' has data in a payment workbook, if so it generates an invoice based on the data, if a Swimmer doesn't have data in a payment workbook then it generates a blank invoice.

    Dim SwimmerT As New SwimmerTest Dim util As New Utility Private Sub cmbGenerate_Click() ' Set up variables Dim SwimmerDetails As Worksheet Dim Payments As Worksheet Dim PaymentData As Workbook Dim InvoiceTemplatePath As String Dim LastRow As Long Dim SwimmersData As Range Dim Rng As Range Dim curYear As String Dim CurMonth As String Dim wordApp As Object Dim wordDoc As Object Dim SavePath As String Dim SwimmerFee As Currency Dim Fee As Currency Dim MonthValue Dim nthDay Dim InvoiceFee Dim CurRow As Integer Dim cellName As String Dim cellYear As String Dim tName As String Dim tYear As String Dim tLastRow As Integer Dim payment As Range Dim invoiceRange As Range Dim invoiceIRow Dim tmpLastRow Dim YearValue Dim invoiceName Dim invoiceCurRow

    ' InvoiceGen is Form Name CurMonth = InvoiceGen.cmbMonthList.Value curYear = InvoiceGen.cmbYearList.Value util.MonthNumber = CurMonth MonthValue = util.MonthNumber util.maxWeeks = util.TDaysInMonth(CInt(curYear), CInt(MonthValue), 1) ' Set Save Path SavePath = ThisWorkbook.Worksheets("Administration").Range("B3") Set PaymentData = Workbooks.Open(SavePath & "Sunday.xlsm") ' Prompt User MsgBox "Generating Invoices", vbExclamation, "Invoices" Application.ScreenUpdating = False InvoiceTemplatePath = "C:\SharkyData\Templates\Sharky Invoice.docx" Set wordApp = CreateObject("Word.Application") wordApp.Visible = False Set SwimmerDetails = ThisWorkbook.Sheets("Swimmer Details") SwimmerDetails.Activate Dim FoundSwimmer As Boolean With SwimmerDetails LastRow = SwimmerDetails.Cells(Rows.Count, 1).End(xlUp).Row For Each SwimmersData In SwimmerDetails.Range("A3:A" & LastRow) SwimmersData.Rows.EntireRow.Select SwimmerT.SwimmerRow = ActiveCell.Row SwimmerT.SwimmerName = .Cells.Range(NameColumn & SwimmerT.SwimmerRow).Value SwimmerT.ClassTime = .Cells.Range(SwimTimeColumn & SwimmerT.SwimmerRow).Value SwimmerT.SwimDay = .Cells.Range(SwimDayColumn & SwimmerT.SwimmerRow).Value SwimmerT.isLittleSwimmer = .Cells.Range(LittleSwimmerColumn & SwimmerT.SwimmerRow).Value SwimmerT.ContactPrefs = SwimmerDetails.Range(DefaultContactColumn & SwimmerT.SwimmerRow) FoundSwimmer = False Set wordDoc = wordApp.Documents.Add(InvoiceTemplatePath) Set Payments = PaymentData.Sheets(CurMonth) Payments.Activate tmpLastRow = Payments.Cells(Rows.Count, 1).End(xlUp).Row For Each invoiceRange In Payments.Range("A2:A" & tmpLastRow) invoiceRange.Rows.EntireRow.Select invoiceCurRow = ActiveCell.Row invoiceName = Payments.Cells.Range("A" & invoiceCurRow).Value YearValue = Payments.Cells.Range("B" & invoiceCurRow).Value If (StrComp(SwimmerT.SwimmerName, invoiceName, vbTextCompare) = 0) And (StrComp(YearValue, curYear, vbTextCompare) = 0) Then FoundSwimmer = True MsgBox "We Found " & SwimmerT.SwimmerName & " Invoice Date : " & YearValue If SwimmerT.isLittleSwimmer Then SwimmerFee = Format(CDbl(5.25), "#,##0.00") Else SwimmerFee = Format(CDbl(8.5), "#,##0.00") End If Dim Wks(1 To 5) As String Wks(1) = Payments.Range("D" & invoiceCurRow) Wks(2) = Payments.Range("E" & invoiceCurRow) Wks(3) = Payments.Range("F" & invoiceCurRow) Wks(4) = Payments.Range("G" & invoiceCurRow) Wks(5) = Payments.Range("H" & invoiceCurRow) Payments.Range("L" & invoiceCurRow).Value = "True" Dim CarriedFrom CarriedFrom = Payments.Range("J" & SwimmerT.SwimmerRow) Dim CarriedTo CarriedTo = Payments.Range("K" & SwimmerT.SwimmerRow) Dim Counter Dim wkNo Dim Wk Set wordDoc = wordApp.Documents.Add(InvoiceTemplatePath) Dim InvoiceTotal As Double InvoiceTotal = 0 For Counter = 1 To util.maxWeeks wkNo = Wks(Counter) nthDay = NthDayOfWeek(CInt(curYear), CInt(MonthValue), CInt(Counter), 1) Dim bmark bmark = CStr("Wk" & Counter) Select Case Wks(Counter) Case "" wordDoc.Bookmarks(bmark).Range.Text = nthDay & " - " & Format$(SwimmerFee, "Currency") & " To Pay" InvoiceTotal = InvoiceTotal + SwimmerFee Case "P" wordDoc.Bookmarks(bmark).Range.Text = nthDay & " - Paid " & Format$(SwimmerFee, "Currency") If InvoiceTotal > 0 Then InvoiceTotal = InvoiceTotal - SwimmerFee End If Case "A" wordDoc.Bookmarks(bmark).Range.Text = nthDay & " Absent" InvoiceTotal = InvoiceTotal + SwimmerFee Case "AH" wordDoc.Bookmarks(bmark).Range.Text = nthDay & " Absent With Holiday" Case "PH" wordDoc.Bookmarks(bmark).Range.Text = nthDay & " - Paid Holiday Carried Forward to " & CarriedTo Case "CF" wordDoc.Bookmarks(bmark).Range.Text = nthDay & " - Credit Carried Forward From " & CarriedFrom Case "C" wordDoc.Bookmarks(bmark).Range.Text = nthDay & " Cancelled" End Select Next Counter InvoiceFee = InvoiceTotal ' Pass Data to Word Bookmarks wordDoc.Bookmarks("bSwimmerName").Range.Text = SwimmerT.SwimmerName ' Setup Bookmarks wordDoc.Bookmarks("bMonth").Range.Text = CurMonth wordDoc.Bookmarks("bYear").Range.Text = curYear wordDoc.Bookmarks("bDate").Range.Text = util.NthDOW(CInt(curYear), CInt(MonthValue), 1, 1) wordDoc.Bookmarks("bFees").Range.Text = Format$(InvoiceFee, "Currency") Dim FileName As String FileName = "C:\SharkyData\Templates\" & SwimmerT.SwimmerName & " " & CurMonth & " " & curYear & ".pdf" wordDoc.ExportAsFixedFormat OutputFileName:=FileName, ExportFormat:=wdExportFormatPDF Dim EmailType As String EmailType = "Invoice" Call SendAsEmail(EmailType, SwimmerT.SwimmerName, CurMonth, curYear, SwimmerT.ContactPrefs, FileName) wordDoc.Close SaveChanges:=False Payments.Activate FoundSwimmer = True Exit For End If Next invoiceRange If FoundSwimmer = False Then MsgBox "We Found Noone Add A Row" invoiceIRow = Payments.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row Payments.Cells(invoiceIRow, 1).Value = SwimmerT.SwimmerName Payments.Cells(invoiceIRow, 2).Value = curYear Payments.Cells(invoiceIRow, 3).Value = SwimmerT.ClassTime Payments.Cells(invoiceIRow, 12).Value = "True" If SwimmerT.isLittleSwimmer Then SwimmerFee = Format$(CDbl(5.25), "0.00") Else SwimmerFee = Format$(CDbl(8.5), "0.00") End If Dim InvoiceTotal1 As Double InvoiceTotal1 = 0 Dim Counter1 Dim wkNos Set wordDoc = wordApp.Documents.Add(InvoiceTemplatePath) For Counter1 = 1 To util.maxWeeks nthDay = NthDayOfWeek(CInt(curYear), CInt(MonthValue), CInt(Counter1), 1) ' With wkNo Dim bmark1 bmark1 = CStr("Wk" & Counter1) wordDoc.Bookmarks(bmark1).Range.Text = nthDay & " - " & Format$(SwimmerFee, "Currency") & " To Pay" InvoiceTotal1 = InvoiceTotal1 + SwimmerFee Next Counter1 InvoiceFee = InvoiceTotal1 ' Pass Data to Word Bookmarks wordDoc.Bookmarks("bSwimmerName").Range.Text = SwimmerT.SwimmerName ' Setup Bookmarks wordDoc.Bookmarks("bMonth").Range.Text = CurMonth wordDoc.Bookmarks("bYear").Range.Text = curYear wordDoc.Bookmarks("bDate").Range.Text = util.NthDOW(CInt(curYear), CInt(MonthValue), 1, 1) wordDoc.Bookmarks("bFees").Range.Text = Format$(InvoiceFee, "Currency") Dim FileName1 As String FileName1 = "C:\SharkyData\Templates\" & SwimmerT.SwimmerName & " " & CurMonth & " " & curYear & ".pdf" wordDoc.ExportAsFixedFormat OutputFileName:=FileName1, ExportFormat:=wdExportFormatPDF Dim EmailType1 As String EmailType1 = "Invoice" Call SendAsEmail(EmailType1, SwimmerT.SwimmerName, CurMonth, curYear, SwimmerT.ContactPrefs, FileName1) wordDoc.Close SaveChanges:=False Payments.Activate End If ThisWorkbook.Activate Next SwimmersData End With wordApp.Quit PaymentData.Close 1 ' Unload Userform Unload Me End Sub

    I have looked at the VSTO links and am slowly getting through some VSTO books however it is piecing examples together to get a fully functioning project that is causing the issue. I am currently designing my Ribbon and Backstage view in XML, again this is something quite new to me.

    The reason I have opted for the VSTO approach was due to this being an interesting topic and seemed like the logical step up from VBA. Also the issue i had found in the past with VBA was that sometimes a link between a Template and the generated Document became broken if the document was run from a different network. Plus the issue of setting Macro security for every machine.

    I will definitely look in to content controls instead of bookmarks (my previous projects have mainly been in office 2003) and will have a look at the open Xml idea.

    Any other ideas / resources that you could point me towards would be appreciated.

    Regards

    Jason

    Friday, February 15, 2013 6:24 PM