Beatrix
12-11-2015, 08:45 AM
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.
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.