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