filling multiple label captions with one or two For statements

[jj98664]

New user to the site. Thanks for all the great help throughout the years.

My question is this:

I am trying to autofill an invoice template in Word from a quote spreadsheet. The Word document is just a bunch of labels that the captions need to be changed so that they can be printed onto a pre-printed invoice that is normally handwritten.

I can open the word document, I can edit the caption of the standard name, address, date, etc. type labels. The part I am having problems with is the list of items, their quantities, and total prices.

These have all been named as follows: lblQTY, lblQTY1, lblQTY2... up to lblQTY12 (13 in all). I have been scouring the web for a way to fill in these labels without having to write code for each individual label. What I have found is the following:

**********************************************************************
with wddoc

for i = 1 to 12 'we are omitting the first row of items since they do not have a number

.controls("lblQTY" & i).caption = sheet1.range("A", i + 13).value

next i

end with
***********************************************************************

I am using Office 2010, and it comes back with the error: "Run-time error '438': Object doesn't support this property or method.


Any help would be greatly appreciated.


JJ - Vancouver, WA

[gmaxey]

.controls is not a method or property of the document object. What do you mean by "Labels?" What exactly are you trying to fill? If you are trying to fill a content control then use

.SelectContentControlsByTitle("lblQTY" & I).Item(1).Range.Text

[jj98664]

I am trying to fill the caption option in an ActiveX label on a Word 2010 document that has been accessed from an Excel 2010 document.

[gmaxey]

I would suggest finding something other than ActiveX controls :-(

Besides presenting security risks, activeX controls become inlineshapes in Word (not controls) and working with them is never a walk in the sun:


Sub ScratchMacro()
'A basic Word macro coded by Greg Maxey
Dim oILS As InlineShape
Dim lngIndex As Long
For lngIndex = 1 To 12
For Each oILS In ActiveDocument.InlineShapes
If oILS.OLEFormat.ClassType = "Forms.Label.1" Then
If oILS.OLEFormat.Object.Name = "lblQTY" & lngIndex Then
oILS.OLEFormat.Object.Caption = sheet1.Range("A", lngIndex + 13).Value
End If
End If
Next oILS
Next lngIndex
lbl_Exit:
Exit Sub
End Sub

[jj98664]

Do you have a better recommendation to fill in specific areas of a word document? I'm open to all suggestions.

[gmaxey]

Better than content controls? No and I've already shown you the code to do that.. But, I would fill in bookmarks, formfields, table cells, just about anything before I would use and activeX control.

[SamT]

Greg,

How would you design his Invoice Template?

[gmaxey]

Sam,
I've not seen it, but I'm assuming that the 12 labels that need filling are standing in for blank space in his pre-printed invoice forms.

He could put a CC titled QTY in each of those spaces and then

For i = 1 to 12
wdDoc.SelectContentControlsByTitle("QTY").Item(lngIndex).Range.Text = "XYZ"
Next i

or he could just write to empty table cells.