Produce a word doc for each unique record in a worksheet

[Beatrix]

Hi All,

I am trying to find out the best approach to produce a word doc for each unique record in the worksheet. The process would be like producing the same form dynamically for each unique record in the worksheet. The form in word doc would have many tables. In the spreadsheet, each row would be a unique record with a unique ID number. Which means each record in the worksheet would create one form in word. At the end of the process If I have 10 records in the worksheet then I would have 10 word docs in one folder. Say: A_5001 , B_5002, C_5003,......,J_5010

My question is, which is the best way to design the structure?

Option1: Creating a dynamic structure in a worksheet to run data easily in excel then converting it to word doc? (I don't know if there would be some format issues when the tables are moved from excel to word)

Option2: Pulling data from the worksheet into the tables in word doc directly?

Can anyone help me on how to do this please?

I attached sample files with mock data however the original worksheet would have more records with many columns and the original word doc would have more tables with many figures.

Cheers
B.

[mancubus]

i would create a template word document with 2 blank tables (attached) save it in the same folder with excel file then run the code below.

Sub vbax_53086_fill_word_tables()
'requires a reference to Microsoft Word XX.0 Object Library from Tools/References in VBE


    Dim objWord As Word.Application
    Dim objDoc As Word.Document
    Dim objTable1 As Word.Table, objTable2 As Word.Table
    Dim i As Long, j As Long
    
    Set objWord = New Word.Application
    With objWord
        .Visible = True
        For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row
            Set objDoc = .Documents.Open(ThisWorkbook.Path & "\A_template.docx")
            With objDoc
                .Tables(1).Cell(1, 2).Range.Text = Cells(i, 1).Value
                .Tables(1).Cell(2, 2).Range.Text = Cells(i, 2).Value
                .Tables(1).Cell(3, 2).Range.Text = Cells(i, 9).Value
                
                .Tables(2).Cell(2, 1).Range.Text = Format(Cells(i, 3).Value, "###%")
                .Tables(2).Cell(2, 2).Range.Text = Format(Cells(i, 4).Value, "###%")
                .Tables(2).Cell(2, 3).Range.Text = Cells(i, 5).Value
                .Tables(2).Cell(2, 4).Range.Text = Format(Cells(i, 6).Value, "###%")
                .Tables(2).Cell(2, 5).Range.Text = Format(Cells(i, 7).Value, "###%")
                .Tables(2).Cell(2, 6).Range.Text = Cells(i, 8).Value
                
                .SaveAs Replace(.FullName, "template", Range("A" & i))
                .Close False
            End With
        Next i
        .Quit
    End With


End Sub

[Beatrix]

thanks mancubus. It's easy to understand.

Question1: Is there any restriction for number of tables in word template or number of columns in the spreadsheet? In original template I'll add more tables so I will adjust the code but I wonder if there is a limit for it?

Question2: In below line I tried to change it as Range("B" & "A" & i) but didn't work. I need to use 2 columns to name the new word docs.

.SaveAs Replace(.FullName, "template", Range("A" & i))

[mancubus]

You are welcome.

1
insert as many tables as you want to word template and see if you can. Afaik, it is limited to system resources. I personally didn't need more than 30 tables before.

2
try Range("B" & i) & Range("A" & i)

[Beatrix]

Perfect! Thanks again.

[snb]

Use the attached file as a 'template'.

Sub M_snb()
  sn = Sheet1.Cells(1).CurrentRegion
  
  With GetObject("G:\OF\A_5001 snb.docx")
     For j = 2 To UBound(sn)
       For jj = 1 To UBound(sn, 2)
         .variables("H_" & jj) = sn(j, jj)
       Next
       .Fields.Update
       .saveas2 "G:\OF\A_5001_snb_" & Format(j, "000") & ".docx"
    Next
    .Close 0
  End With
End Sub

[Beatrix]

Thanks for the code snb.

I am trying to understand the script. I searched on UBound function and have an idea about how it works. I need to edit the script as it pulls Heading1 figures into Heading7 and 8 so cell reference or range might be wrong. Trying to find out which bit I need to change. Do you mind to tell me what "H_ " means in below line please?

.variables("H_" & jj) = sn(j, jj)

[snb]

I defined docvariables in the word document.
You can insert the content of a docvariable using the docvariable field.
You can make these fields visible/non visible toggling Alt-F9
So you can alter the docvariables' numbers.

PS. I used the exact sequence you used in your example.

[Beatrix]

Thanks again snb. This is very clever. I've checked word doc vba editor before I asked you but couldn't see a track on doc-variables. Now it makes sense. The original form would have more tables and it would be slightly different but my understanding is I can apply to this for multiple tables only I would need to change the array dimension of UpperBound am I right? In the script sn is array name. j and jj are the array dimension. If I have a third table then would it be jjj=3 ? I don't know if it sounds right

[snb]

Almost

in Excel only 2 dimensions suffice: rows (j) and columns(jj)
an Array (sn) in this case is a perfect mirror of the values in an Excel worksheet.

The amount of tables is a completely independent matter.

As you can see in the document I uploaded you can arbitrarily (ad libitum) decide which documentvariable has to be inserted in which cell of which table.
So you can adapt your Worddocument without any need to adapt the VBA code as well.

Docvariables are a very poorly documented part of Word (but one of the most powerful).

It's nice to see that you try to get to grips with the code. That will help you in the future tremendously.

[Beatrix]

Hi snb,

Hmmm it makes sense. I was thinking how the doc-variable would know the right cell to pull data but we use column number to define it. "H_2" in this doc-variable 2 refers to column B. It takes sometime for me to understand but got it at the end.

If you don't mind 3 more questions for this script:

1- I need to use the names in column B when I save as each word doc in the process. Instead of 001, 002, 003 etc. It should use the data in cell B2, B3, B4 .... etc.
I google SaveAs2 method to understand but I think it's not possible to adjust this method to get that done? It seems possible with SaveAs Replace method but not sure if I could integrate it to this script?

2- In data source (excel worksheet) some cells are number, some percentage, some decimal etc. How do I change the cell format in this script? Is it something to do with word doc when I set the doc-variables?

3- sn(j,jj) = sn(rows,columns) why we use 2 for rows and 1 for columns? I played with 2 and 1 to understand the logic but it gave run time error when I changed them.

Many thanks for helping me to learn.

Best Regards
B.

[snb]

ad 1

Sub M_snb() 
    sn = Sheet1.Cells(1).CurrentRegion 
     
    With GetObject("G:\OF\A_5001 snb.docx") 
        For j = 2 To UBound(sn) 
            For jj = 1 To UBound(sn, 2) 
                .variables("H_" & jj) = sn(j, jj) 
            Next 
            .Fields.Update 
            .saveas2 "G:\OF\" & sn(j,2) & ".docx" 
        Next 
        .Close 0 
    End With 
End Sub

ad 1 you still have to change the path (G:\OF\) into something that applies to your system.
ad 2 e.g. to represent decimals: { Docvariable H_07 \# 0,00 }

or you could use:

Sub M_snb() 
    sn = Sheet1.Cells(1).CurrentRegion 
     
    With GetObject("G:\OF\A_5001 snb.docx") 
        For j = 2 To UBound(sn) 
            For jj = 1 To UBound(sn, 2) 
                .variables("H_" & jj) = sn(j, jj)
                if jj=3 then .variables("H_" & jj)=formatpercent(sn(j,jj))
                if jj=4 then .variables("H_" & jj)=formatcurrency(sn(j,jj))
                if jj=5 then .variables("H_" & jj)=formatnumber(sn(j,jj),2)
                if jj=6 then .variables("H_" & jj)=formatdatetime(sn(j,jj),1)
            Next 
            .Fields.Update 
            .saveas2 "G:\OF\" & sn(j,2) & ".docx" 
        Next 
        .Close 0 
    End With 
End Sub

ad 3 it's my ideosyncratic convention to use the variable j for rows and jj for columns; nothing magic about it (could be any variable name as long is it is being applied consistently).

[Beatrix]

I will go for option2. much much appreciated. This script helped me a lot to understand better the logic of the language.

[Beatrix]

Hi snb,

I am working on the original forms now and I need to create 77 docvariable H_1, H_2, H_3,...H_77 etc. I tried to copy/paste from the variables you set up and changed the numbers but it copied the same values to each field :o) Then I tried to set up manually but when I press Alt+F9 I can't make them hidden. Don't know how to set up a docvariable. Do I need to run a macro to do it? Simply I type { docvariable H_1 } but it's not working :| I think it's gonna take years to be able to write my own code with lack of programming mindset :/

Cheers
B.

[snb]

Use ctrl-F9, then you'll see the accolades. Type between the accolades: Docvariable H_1 . That's all

Or use:

Sub M_snb()
   ActiveDocument.Content.InsertAfter String(80, vbCr)
   For j = 1 To 77
      ActiveDocument.Fields.Add ActiveDocument.Paragraphs(j).Range, 64, "H_" & j, False
   Next
End Sub

[Beatrix]

Thanks very much snb!!

[Beatrix]

Hi snb,

I used below script as you recommended regarding the format issues. However the format is not working right for each docvariable. I inserted 77 docvariable in word. However some of them pops up with the wrong format. Please see attachments for H_3, H_4, H_6, H_49, H_51 etc.

Also if cell is blank then it gives an error "Error! No document variable supplied". Is it possible to leave docvariable blank when cells are blank in the spreadsheet?

I attached word docs ( one template one outcome) and the spreadsheet.

I appreciate for all your help.

Sub M_snb() 
    sn = Sheet1.Cells(1).CurrentRegion 
     
    With GetObject("G:\OF\A_5001 snb.docx") 
        For j = 2 To UBound(sn) 
            For jj = 1 To UBound(sn, 2) 
                .variables("H_" & jj) = sn(j, jj) 
                If jj=3 Then .variables("H_" & jj)=formatpercent(sn(j,jj)) 
                If jj=4 Then .variables("H_" & jj)=formatcurrency(sn(j,jj)) 
                If jj=5 Then .variables("H_" & jj)=formatnumber(sn(j,jj),2) 
                If jj=6 Then .variables("H_" & jj)=formatdatetime(sn(j,jj),1) 
            Next 
            .Fields.Update 
            .saveas2 "G:\OF\" & sn(j,2) & ".docx" 
        Next 
        .Close 0 
    End With 
End Sub

[mancubus]

as it is summer time in northern hemisphere, snb may be on vacation.


in order to get true format, you should match the jj with column number.
regarding the latest excel file you uploaded, i think below code will do what you want.

change ThisWorkbook.Path and template.docx to suit.

Sub M_snb_v1()
    
    sn = Sheet1.Cells(1).CurrentRegion
     
    With GetObject(ThisWorkbook.Path & "\template.docx")
        For j = 2 To UBound(sn)
            For jj = 1 To UBound(sn, 2)
                .Variables("H_" & jj) = sn(j, jj)
                If jj = 3 Then .Variables("H_" & jj) = FormatDateTime(sn(j, jj), 1)
                If jj = 49 Then .Variables("H_" & jj) = FormatPercent(sn(j, jj))
                If jj = 51 Then .Variables("H_" & jj) = FormatPercent(sn(j, jj))
                If jj = 53 Then .Variables("H_" & jj) = FormatPercent(sn(j, jj))
                If jj = 55 Then .Variables("H_" & jj) = FormatPercent(sn(j, jj))
                If jj = 57 Then .Variables("H_" & jj) = FormatPercent(sn(j, jj))
                If jj = 59 Then .Variables("H_" & jj) = FormatPercent(sn(j, jj))
                If jj = 61 Then .Variables("H_" & jj) = FormatPercent(sn(j, jj))
                If jj = 62 Then .Variables("H_" & jj) = FormatPercent(sn(j, jj))
                If jj = 63 Then .Variables("H_" & jj) = FormatPercent(sn(j, jj))
                If jj = 64 Then .Variables("H_" & jj) = FormatPercent(sn(j, jj))
                If jj = 65 Then .Variables("H_" & jj) = FormatPercent(sn(j, jj))
                If jj = 67 Then .Variables("H_" & jj) = FormatPercent(sn(j, jj))
                If jj = 69 Then .Variables("H_" & jj) = FormatPercent(sn(j, jj))
            Next
            .Fields.Update
            .SaveAs2 ThisWorkbook.Path & "\" & sn(j, 2) & ".docx"
        Next
        .Close 0
    End With

End Sub

[mancubus]

whoops...
there are some n/a's in numeric columns.
FormatDateTime, FormatCurrency, FormatPercent, FormatNumber functions' arguments must be numbers. otherwise code will throw type mismatch error.


this may handle type mismatch error:

Sub M_snb_v2()
    
    sn = Sheet1.Cells(1).CurrentRegion
     
    With GetObject(ThisWorkbook.Path & "\template.docx")
        For j = 2 To UBound(sn)
            For jj = 1 To UBound(sn, 2)
                .Variables("H_" & jj) = sn(j, jj)
                If jj = 3 And IsNumeric(sn(j, jj)) Then .Variables("H_" & jj) = FormatDateTime(sn(j, jj), 1)
                If jj = 49 And IsNumeric(sn(j, jj)) Then .Variables("H_" & jj) = FormatPercent(sn(j, jj))
                If jj = 51 And IsNumeric(sn(j, jj)) Then .Variables("H_" & jj) = FormatPercent(sn(j, jj))
                If jj = 53 And IsNumeric(sn(j, jj)) Then .Variables("H_" & jj) = FormatPercent(sn(j, jj))
                If jj = 55 And IsNumeric(sn(j, jj)) Then .Variables("H_" & jj) = FormatPercent(sn(j, jj))
                If jj = 57 And IsNumeric(sn(j, jj)) Then .Variables("H_" & jj) = FormatPercent(sn(j, jj))
                If jj = 59 And IsNumeric(sn(j, jj)) Then .Variables("H_" & jj) = FormatPercent(sn(j, jj))
                If jj = 61 And IsNumeric(sn(j, jj)) Then .Variables("H_" & jj) = FormatPercent(sn(j, jj))
                If jj = 62 And IsNumeric(sn(j, jj)) Then .Variables("H_" & jj) = FormatPercent(sn(j, jj))
                If jj = 63 And IsNumeric(sn(j, jj)) Then .Variables("H_" & jj) = FormatPercent(sn(j, jj))
                If jj = 64 And IsNumeric(sn(j, jj)) Then .Variables("H_" & jj) = FormatPercent(sn(j, jj))
                If jj = 65 And IsNumeric(sn(j, jj)) Then .Variables("H_" & jj) = FormatPercent(sn(j, jj))
                If jj = 67 And IsNumeric(sn(j, jj)) Then .Variables("H_" & jj) = FormatPercent(sn(j, jj))
                If jj = 69 And IsNumeric(sn(j, jj)) Then .Variables("H_" & jj) = FormatPercent(sn(j, jj))
            Next
            .Fields.Update
            .SaveAs2 ThisWorkbook.Path & "\" & sn(j, 2) & ".docx"
        Next
        .Close 0
    End With

End Sub