Produce a word doc for each column_Docvariable+UBOUND function

[Beatrix]

Hi Everyone

I had below script from snb a while ago and changed the variable names only.

This script produces a word document for each row in excel worksheet by using doc-variable method. I have a similar task so I will use this script again but need to amend it as it should create the word document for each column this time. Can I change the array and For Loop as below?? Or what should I change in the script to achieve this result?

For c = 6 To UBound(ar)
            For r = 3 To UBound(3, ar)
                .Variables(r & "H_") = ar(r, c)

Sub Produce_snb_forms()
 
    'requires a reference to Microsoft Word Object Library from Tools/References in VBE
    
    ar = Sheet1.Cells(1).CurrentRegion
     
    With GetObject("Y:\MIS\vba tests\Annual Summary 2015.docx")
        For r = 2 To UBound(ar)
            For c = 1 To UBound(ar, 2)
                .Variables("H_" & c) = ar(r, c)
                                              
            Next
            .Fields.Update
            .SaveAs2 "Y:\MIS\vba tests\snb Forms\" & ar(r, 2) & ".docx"
        Next
        .Close 0
    End With
End Sub

Cheers
B.

[snb]

No reference to any Word library required.

Sub Produce_snb_forms()
  sn = Sheet1.Cells(1).CurrentRegion

  With GetObject("Y:\MIS\vba tests\Annual Summary 2015.docx")
     For jj = 1 To UBound(sn,2)
       .Variables("H") =" "
       For j = 2 To UBound(sn)
          .Variables("H") = .Variables("H") & vblf & sn(j, jj)
      Next
      .Fields.Update
      .SaveAs2 "Y:\MIS\vba tests\snb Forms\" & sn(j, 2) & ".docx"
    Next
    .Close 0
  End With
  End Sub

[Beatrix]

Thanks very much snb. I appreciate that. My understanding is it starts to loop from column now instead of rows but again I need to ask
Why we use 2 in UBound for columns?Whats the difference between UBound(sn,2) and UBound(sn)

For jj = 1 To UBound(sn,2)
For j = 2 To UBound(sn)

[snb]

the number of 'rows' - the first dimension- : ubound(sn) or ubound(sn,1)
the number of 'columns' - the second dimension- : ubound(sn,2)

More on arrays: see http://www.snb-vba.eu/VBA_Arrays_en.html

[Beatrix]

Thanks snb it makes sense. I've found your website very useful. Thanks for the link.

Cheers
B.

[Beatrix]

Hi snb,

I just realised that I made a mistake. I need to run data from spreadsheet to spreadsheet not to word doc. It look like a form thats why I kept thinking I would use the same method. Actually there are 109 headings as row in source spreadsheet and each heading goes to a cell from source spreadsheet to other spreadsheet. I need to produce a spreadsheet based on each column in source spreadsheet. I guess I can use the same loop but instead of doc variable how can I map the heading fields? Any suggestions?

No reference to any Word library required.

Sub Produce_snb_forms()
  sn = Sheet1.Cells(1).CurrentRegion

  With GetObject("Y:\MIS\vba tests\Annual Summary 2015.docx")
     For jj = 1 To UBound(sn,2)
       .Variables("H") =" "
       For j = 2 To UBound(sn)
          .Variables("H") = .Variables("H") & vblf & sn(j, jj)
      Next
      .Fields.Update
      .SaveAs2 "Y:\MIS\vba tests\snb Forms\" & sn(j, 2) & ".docx"
    Next
    .Close 0
  End With
  End Sub