0

I have certain ranges within an Excel file and I create an instance of Word template from within Excel VBA. This Word template has a bunch of Doc variables so that I can replace the values of those Doc Variables with my defined variables using precise Excel sheet ranges. For some weird reason, Doc variable laid down in Word template file are not being updated.

Could someone please extend your help and advise what possible mistake I might be making here.

Sub Generate_CoverLetter()

    Dim client, bo, invoice_currency, vesselName As String
    Dim invoiceAmount 'As Single
    Dim bo_rng, rngBO, rngCustName, rngAmount, rngVesselName, rngCurrencyCode, rngCommencedDate As Range
    Dim SearchParams As Variant
    Dim SearchParamsCols As Variant
    Dim wdApp As Object
    Dim wdDoc As Word.Document
    
    Set bo_rng = Application.InputBox( _
      Title:="Select BO range", _
      Prompt:="Select a cell to pull in BO number....", _
      Type:=8)
      
    bo = bo_rng.value
    
    lastRow = Range("A" & Application.Rows.Count).End(xlUp).Row

    SearchParams = Array("field_19", "customer_name", "total_amount", "invoice_currency_code", "field_43", "field_71")
    orderColumn = seachParamColumn(SearchParams(0))
    cnameColumn = seachParamColumn(SearchParams(1))
    amountColumn = seachParamColumn(SearchParams(2))
    currencyColumn = seachParamColumn(SearchParams(3))
    commencedDateColumn = seachParamColumn(SearchParams(4))
    vesselColumn = seachParamColumn(SearchParams(5))
    
    
    ' core ranges
    Set rngBO = Range(Cells(2, orderColumn), Cells(lastRow, orderColumn))
    Set rngCustName = Range(Cells(2, cnameColumn), Cells(lastRow, cnameColumn))
    Set rngAmount = Range(Cells(2, amountColumn), Cells(lastRow, amountColumn))
    Set rngVesselName = Range(Cells(2, vesselColumn), Cells(lastRow, vesselColumn))
    
    ' invoice parameter ranges
    Set rngCurrencyCode = Cells(bo_rng.Row, currencyColumn)
    Set rngCommencedDate = Cells(bo_rng.Row, commencedDateColumn)


    client = Cells(bo_rng.Row, rngCustName.Column)
    InvoiceNumber = Cells(bo_rng.Row, 1) ' .value
    invoice_currency = rngCurrencyCode.value
    invoiceAmount = Application.WorksheetFunction.SumIfs(rngAmount, rngBO, "=" & bo, rngCustName, "=" & client)
    invoiceAmount = Format(invoiceAmount, "#,##0.00")
    commencedDate = rngCommencedDate.value

    ' This weird looking loop is here because an order might have several rows in Excel data
    ' but only one of those rows might have the name of the vessel

    For Each cell In rngVesselName.SpecialCells(xlCellTypeVisible)
        If cell.Column = vesselColumn And Not IsEmpty(cell) Then
            vesselName = cell.value
            Exit For
        End If
    Next
    
    
    MsgBox InvoiceNumber & vbLf & _
            invoice_currency & " " & invoiceAmount & vbLf & _
            client & vbLf & _
            vesselName & vbLf & _
            commencedDate

Set wdApp = CreateObject("Word.Application")
wdApp.Visible = True
Set wdDoc = wdApp.Documents.Add

Set wdDoc = wdApp.Documents.Open("C:\Users\smiq\Documents\Custom Office Templates\CL.dotm")
Set wdDoc = ActiveDocument
wdDoc.Activate

wdApp.WindowState = wdWindowStateMaximize

ActiveDocument.Variables("wd_vesselName").value = vesselName
ActiveDocument.Variables("wd_CommencedDate").value = commencedDate
ActiveDocument.Variables("wd_invoiceNumber").value = InvoiceNumber
ActiveDocument.Variables("wd_invoiceAmount").value = invoiceAmount

End Sub


Function seachParamColumn(param As Variant)
Dim c
    With Range("1:1")
    Set c = .Find(param, , xlValues)
        If Not c Is Nothing Then
         seachParamColumn = Range(c.Address).Column
        End If
    End With

End Function
SmIqbal
  • 99
  • 1
  • 13
  • 1
    There are some issues with your code but if we just concentrate on your issue first, just before you try to assign the value to your word variables, can you check to see if your variables (i.e. `vesselName`, `commencedDate`, `InvoiceNumber` and `invoiceAmount`) have values? – Zac Jul 22 '20 at 14:10
  • Zac, yes I have already checked my variables and they have correct values. The only problem is that doc variables inside word file are not being populated with those variable values coming from Excel. – SmIqbal Jul 22 '20 at 14:53
  • Besides that I would love to know your suggestions as to where do you see issues and how can we improve this code overall. – SmIqbal Jul 22 '20 at 14:55
  • I want to mention in advance that finding column numbers through a function has a genuine reason in my code. The raw data which is coming from an Oracle database, every now and then has some changes and the heading columns get a different place. As a consumer of this raw data we do not know what are the most recent changes that have been made to the raw data column sequence. This therefore makes my code flexible to pick correct column numbers for all the required fields without having the need to change code. – SmIqbal Jul 22 '20 at 15:03
  • 1
    Finding column number is actually a very good approach. I've used this approach a few times when columns are not static. As you mentioned, this approach gives the flexibility that you would need in this scenario. I will give it a go tomorrow and post something here – Zac Jul 22 '20 at 15:22
  • Zac thanks for your comments. Yes, columns are not static, at least for a long time. I would love to see your comments tomorrow. And also as I said, please do mention if you have any other suggestions for overall improvement. I am learning and trying to improve my skills. Of course, I should have declared a few other variables for column numbers etc and it would have been nice to use Option Explicit to avoid the use of undeclared variables, but I am in such a haste and therefore ignored this completely for now. – SmIqbal Jul 22 '20 at 15:30
  • Zac, Finally resolved the issue. The template document is also using a mail merge feature to grab some mailing information like the client's name, address, contact number, etc. from an Excel file. For some reason, the docvariables which I defined within the Word template file were using "\* mergeformat" parameter and I believe this was the main culprit. I can tell you that I did not add these field parameters myself to all the docvariables and God knows where they came from. As soon as I removed these unwanted "\* mergeformat" parameters from all docvariables, my code became functional. – SmIqbal Jul 23 '20 at 13:49
  • Glad to hear that. I dont think you need to do that. I was just testing something and i think the reason why your fields weren't getting update is because after you update the fields, you just had to run this command: `ActiveDocument.Fields.Update`. This should have resolved your problem. Although i wouldn't use things like `ActiveDocument` as it's fraught with danger – Zac Jul 23 '20 at 13:56

0 Answers0