Automation (inserting text fields with fixed positions and filling them)

[a_ahmed]

Hi guys,

I am hoping someone could help me out with this.

I am basically trying to automate printing of cheques and I am reading the data from an excel workbook. I got all that figured out. I also was able to insert plain text content controls and to insert a bookmark and then access that field via bookmark in VBA

The thing is, I setup one page with everything placed in place via insertion and mouse positioning... but I want to try to do this programatically. WHat's my best option to do this? It has to be irrelavent of margins or document text, because the fields are positional, I used frames to be able to move them around and had the text control inside the frame control. Otherwise it moves with the text cursor which I then can't precisely position.

I can detect number of rows in the excel workbook, that will be how many cheques will need to be printed which will be how many pages to generate.

Each page will be identical except the fields will hold information based on the row I extract in the excel workbook.

I hope I am making sense so far, can someone give me a move in the right direction? I have now a document with the position of everything how I want, but.. that's only good for one cheque... one page...

I basically want all of these fields to be absolutely positioned where I want them precisely and if I can do that through code that would be fantastic (ideally that's what I need). I also want to be able to reference them somehow so I can plug in the variables I have stored in the code.

I load the excel file and then read the excel sheet store the data in a variable, and concurrently generate the pages and the content on the pages. That's the idea and direction.

I am very familiar with Excel VBA, not so much with Word VBA

[a_ahmed]

And should I be using a textbox instead of these controls (aren't they legacy?).

I can see that I can set a textbox borderless, with absolute position (horizontal/vertical) and absolute widht/height too. I can also set it to position in front of all text so no effect from text or other objects.

I can set it to ignore overlap and moving with text, instead I can make it relative to page only. This sounds all ideal I would think better than these controls?

[a_ahmed]

It seems like textboxes are more versatile however I am not sure how to tag one...

For Each shp In ActiveDocument.Shapes
aStr = "My name is " & shp.Name
aStr = aStr & vbNewLine & "My EditID is " & shp.EditID
ma = MsgBox(aStr, vbOKOnly)
Next

With this code I'm able to identify the textboxes but the names are the same unique IDs not very helpful... and this will be an issue if I am doing multiple it wont be just one [age

[gmayor]

If your data is in a worksheet, it would make sense to use mail merge to create the documents. The problem you are likely to encounter is that each bank has its own cheque format, so layout can be an issue. A borderless table is perhaps the best way to position the various elements.

[gmaxey]

I don't know that much about mail merge so Graham may very well be correct. However, you can certainly do the with titled Content controls. Start with a rich text CC titled "Template" Inside it place any text you need and your frames. Inside the frames add titled plain text CCs and position the frames as appropriate.

You can work out the code for your loop but basically you will simply fill the template with the first check data the replicate the template and repeat the process for as many checks as you need:


Sub ScratchMacro()
'A basic Word macro coded by Greg Maxey
Dim oRng As Word.Range
Dim oCCNewCheckTemplate As ContentControl, oCC As ContentControl
Dim lngIndex As Long
'Ensure your template richtext CC has ay least one empty paragraph after it.
For lngIndex = 1 To HoweverManyChecksYouHave
If lngIdex = 1 Then
Set oCCNewCheckTemplate = ActiveDocument.SelectContentControlsByTitle("Template").Item(1)
For Each oCC In oCCNewCheckTemplate.Range.ContentControls
Select Case oCC.Title
Case "Payee": oCC.Range.Text = "$100.00" 'Write payee amount to CCc
Case "Date": 'Write date to CC.
'Etc.
End Select
Next oCC
Else
Set oRng = ActiveDocument.Range
oRng.Collapse wdCollapseEnd
oRng.InsertBreak wdPageBreak
oRng.InsertAfter vbCr
oRng.MoveEnd wdParagraph, -1
Set oCCNewCheckTemplate = oRng.ContentControls.Add(wdContentControlRichText, oRng)
oCCNewCheckTemplate.Range.FormattedText = ActiveDocument.SelectContentControlsByTitle("Template").Item(1).Range.FormattedText
For Each oCC In oCCNewCheckTemplate.Range.ContentControls
Select Case oCC.Title
Case "Payee": oCC.Range.Text = "$100.00" 'Write payee amount to CCc
Case "Date": 'Write date to CC.
'Etc.
End Select
Next oCC
End If
Next lngIndex
lbl_Exit:
Exit Sub
End Sub

[a_ahmed]

Okay this is the approach I took:



Public Sub CreateFields(PayLeft, NameAndAddress, ChequeNumber, FormattedDate, PayRight)
'Formatted Pay Value (Left)
ActiveDocument.Shapes.AddTextbox(msoTextOrientationHorizontal, 1, 1, 1, 1).Name = "PayLeft"
With ActiveDocument.Shapes("PayLeft")
.Line.Visible = msoFalse
.Fill.Visible = msoFalse
.RelativeHorizontalPosition = wdRelativeHorizontalPositionPage
.RelativeVerticalPosition = wdRelativeVerticalPositionPage
.Left = Application.InchesToPoints(0.66)
.Top = Application.InchesToPoints(1.44)
.Width = Application.InchesToPoints(5.47)
.Height = Application.InchesToPoints(0.29)
With .TextFrame.TextRange
.Text = PayLeft
With .Font
.Name = "Calibri"
.Size = 11
End With
End With
End With

'Formatted Named and Full Address
ActiveDocument.Shapes.AddTextbox(msoTextOrientationHorizontal, 1, 1, 1, 1).Name = "NameAndAddress"
With ActiveDocument.Shapes("NameAndAddress")
.Line.Visible = msoFalse
.Fill.Visible = msoFalse
.RelativeHorizontalPosition = wdRelativeHorizontalPositionPage
.RelativeVerticalPosition = wdRelativeVerticalPositionPage
.Left = Application.InchesToPoints(0.8)
.Top = Application.InchesToPoints(1.9)
.Width = Application.InchesToPoints(3.27)
.Height = Application.InchesToPoints(0.76)
With .TextFrame.TextRange
.Text = NameAndAddress
With .Font
.Name = "Calibri"
.Size = 11
End With
With .ParagraphFormat
.LineSpacingRule = wdLineSpaceSingle
.SpaceAfter = 0
End With
End With
End With

'CHEQUE NO / DATE (Middle)
ActiveDocument.Shapes.AddTextbox(msoTextOrientationHorizontal, 1, 1, 1, 1).Name = "ChequeNoDate"
With ActiveDocument.Shapes("ChequeNoDate")
.Line.Visible = msoFalse
.Fill.Visible = msoFalse
.RelativeHorizontalPosition = wdRelativeHorizontalPositionPage
.RelativeVerticalPosition = wdRelativeVerticalPositionPage
.Left = Application.InchesToPoints(5.22)
.Top = Application.InchesToPoints(0.78)
.Width = Application.InchesToPoints(1.04)
.Height = Application.InchesToPoints(0.43)
With .TextFrame.TextRange
.Text = "CHEQUE NO." & vbNewLine & "DATE"
.ParagraphFormat.Alignment = wdAlignParagraphLeft
With .Font
.Name = "Calibri"
.Size = 10
End With
With .ParagraphFormat
.LineSpacingRule = wdLineSpaceSingle
.SpaceAfter = 0
End With
End With
End With

'Check Number (Right)
ActiveDocument.Shapes.AddTextbox(msoTextOrientationHorizontal, 1, 1, 1, 1).Name = "CheckNum"
With ActiveDocument.Shapes("CheckNum")
.Line.Visible = msoFalse
.Fill.Visible = msoFalse
.RelativeHorizontalPosition = wdRelativeHorizontalPositionPage
.RelativeVerticalPosition = wdRelativeVerticalPositionPage
.Left = Application.InchesToPoints(6.77)
.Top = Application.InchesToPoints(0.78)
.Width = Application.InchesToPoints(1)
.Height = Application.InchesToPoints(0.29)
With .TextFrame.TextRange
.Text = ChequeNumber
.ParagraphFormat.Alignment = wdAlignParagraphRight
With .Font
.Name = "Calibri"
.Size = 12
End With
End With
End With

'Formatted Date (Right)
ActiveDocument.Shapes.AddTextbox(msoTextOrientationHorizontal, 1, 1, 1, 1).Name = "FormattedDate"
With ActiveDocument.Shapes("FormattedDate")
.Line.Visible = msoFalse
.Fill.Visible = msoFalse
.RelativeHorizontalPosition = wdRelativeHorizontalPositionPage
.RelativeVerticalPosition = wdRelativeVerticalPositionPage
.Left = Application.InchesToPoints(6.16)
.Top = Application.InchesToPoints(0.98)
.Width = Application.InchesToPoints(1.6)
.Height = Application.InchesToPoints(0.58)
With .TextFrame.TextRange
.Text = FormattedDate & vbNewLine & "D D M M Y Y Y Y"
.ParagraphFormat.Alignment = wdAlignParagraphRight
With .Font
.Name = "Calibri"
.Size = 11
End With
With .ParagraphFormat
.LineSpacingRule = wdLineSpaceSingle
.SpaceAfter = 0
End With
End With
End With

'Formatted Pay Value (Right)
ActiveDocument.Shapes.AddTextbox(msoTextOrientationHorizontal, 1, 1, 1, 1).Name = "PayRight"
With ActiveDocument.Shapes("PayRight")
.Line.Visible = msoFalse
.Fill.Visible = msoFalse
.RelativeHorizontalPosition = wdRelativeHorizontalPositionPage
.RelativeVerticalPosition = wdRelativeVerticalPositionPage
.Left = Application.InchesToPoints(5.76)
.Top = Application.InchesToPoints(1.39)
.Width = Application.InchesToPoints(2)
.Height = Application.InchesToPoints(0.29)
With .TextFrame.TextRange
.Text = PayRight
.ParagraphFormat.Alignment = wdAlignParagraphRight
With .Font
.Name = "Calibri"
.Size = 11
End With
End With
End With
End Sub


Then as I'm reading I fill in the variables feeding from the excel sheet BUT, they all generate on the same page...

I tried adding at the end of the loop



CreateFields strLeftPay, strFullName & vbNewLine & strFullAddress, "001161", strFinalDate, strRightPay
Set oRng = ActiveDocument.Range
oRng.Collapse wdCollapseEnd
oRng.InsertBreak wdPageBreak
oRng.InsertAfter vbCr
oRng.MoveEnd wdParagraph, -1


But all the textboxes were created on the first page... as I go through the loop... the pages are created, so the loop is
say For R=2 to LastRow (excel spreadsheet, skipping header)

[a_ahmed]

Okay so I am googling and it's talking about anchors and how everything defaults to the first page anchor but I'm not sure how to really figure this out where do I set the anchor and what kind of line... I want to add that property of the 'anchor' being the second page at the start.. basically to do the same process on each subsequent page rather than all being created on the first page.

[gmaxey]

The richtext content control approach is much easier.

[a_ahmed]

The richtext content control approach is much easier.

Can you tell me how I could figure out this anchor part. What is happening is the textbox is anchoring to the first page, it's the last property in the parameters which I didn't even use but now I am not sure what to put in that parameter either...

I can't figure out how to anchor to the second page for example.. if I figure this out, I'm completely done with the whole thing.. I've got the rest of the code all done.. I'm literally just stuck on this... The textbox approach allows me to fine tune everything very easily with all parameters, positions, heights/widths. The cheques' fields will have specific fonts, sizes, placement, etc... I got it all down, minus how to anchor to the second page, i can pass page numbers through the subroutine and loops.. that's easy... but what parameters and function do I use for anchoring it...

I would really appreciate the help to get this last part done... if I figure out just how to anchor on second page.. I'm done.. I got the algorithm and loops an the rest all done...

[gmaxey]

Sub Test()
Dim oRng As Word.Range
CreateFields "Two Hundred Twenty Five", "Gregory K. Maxey", "2012", "04/07/2016", "225.00"
Set oRng = ActiveDocument.Range
oRng.Collapse wdCollapseEnd
oRng.InsertBreak wdPageBreak
End Sub
Public Sub CreateFields(PayLeft, NameAndAddress, ChequeNumber, FormattedDate, PayRight)
Dim oShp As Shape
'Formatted Pay Value (Left)
'ActiveDocument.Paragraphs.Last.Range.Select
Set oShp = ActiveDocument.Shapes.AddTextbox(msoTextOrientationHorizontal, 1, 1, 1, 1, ActiveDocument.Paragraphs.Last.Range) '.Name = "PayLeft"
With oShp
.Line.Visible = msoFalse
.Fill.Visible = msoFalse
.RelativeHorizontalPosition = wdRelativeHorizontalPositionPage
.RelativeVerticalPosition = wdRelativeVerticalPositionPage
.Left = Application.InchesToPoints(0.66)
.Top = Application.InchesToPoints(1.44)
.Width = Application.InchesToPoints(5.47)
.Height = Application.InchesToPoints(0.29)
With .TextFrame.TextRange
.Text = PayLeft
With .Font
.Name = "Calibri"
.Size = 11
End With
End With
End With

'Formatted Named and Full Address
Set oShp = ActiveDocument.Shapes.AddTextbox(msoTextOrientationHorizontal, 1, 1, 1, 1, ActiveDocument.Paragraphs.Last.Range) '.Name = "PayLeft"
With oShp ' ActiveDocument.Shapes("NameAndAddress")
.Line.Visible = msoFalse
.Fill.Visible = msoFalse
.RelativeHorizontalPosition = wdRelativeHorizontalPositionPage
.RelativeVerticalPosition = wdRelativeVerticalPositionPage
.Left = Application.InchesToPoints(0.8)
.Top = Application.InchesToPoints(1.9)
.Width = Application.InchesToPoints(3.27)
.Height = Application.InchesToPoints(0.76)
With .TextFrame.TextRange
.Text = NameAndAddress
With .Font
.Name = "Calibri"
.Size = 11
End With
With .ParagraphFormat
.LineSpacingRule = wdLineSpaceSingle
.SpaceAfter = 0
End With
End With
End With

'CHEQUE NO / DATE (Middle)
Set oShp = ActiveDocument.Shapes.AddTextbox(msoTextOrientationHorizontal, 1, 1, 1, 1, ActiveDocument.Paragraphs.Last.Range) '.Name = "PayLeft"
With oShp 'ActiveDocument.Shapes("ChequeNoDate")
.Line.Visible = msoFalse
.Fill.Visible = msoFalse
.RelativeHorizontalPosition = wdRelativeHorizontalPositionPage
.RelativeVerticalPosition = wdRelativeVerticalPositionPage
.Left = Application.InchesToPoints(5.22)
.Top = Application.InchesToPoints(0.78)
.Width = Application.InchesToPoints(1.04)
.Height = Application.InchesToPoints(0.43)
With .TextFrame.TextRange
.Text = "CHEQUE NO." & vbNewLine & "DATE"
.ParagraphFormat.Alignment = wdAlignParagraphLeft
With .Font
.Name = "Calibri"
.Size = 10
End With
With .ParagraphFormat
.LineSpacingRule = wdLineSpaceSingle
.SpaceAfter = 0
End With
End With
End With

'Check Number (Right)
Set oShp = ActiveDocument.Shapes.AddTextbox(msoTextOrientationHorizontal, 1, 1, 1, 1, ActiveDocument.Paragraphs.Last.Range) '.Name = "CheckNum"
With oShp 'ActiveDocument.Shapes("CheckNum")
.Line.Visible = msoFalse
.Fill.Visible = msoFalse
.RelativeHorizontalPosition = wdRelativeHorizontalPositionPage
.RelativeVerticalPosition = wdRelativeVerticalPositionPage
.Left = Application.InchesToPoints(6.77)
.Top = Application.InchesToPoints(0.78)
.Width = Application.InchesToPoints(1)
.Height = Application.InchesToPoints(0.29)
With .TextFrame.TextRange
.Text = ChequeNumber
.ParagraphFormat.Alignment = wdAlignParagraphRight
With .Font
.Name = "Calibri"
.Size = 12
End With
End With
End With

'Formatted Date (Right)
Set oShp = ActiveDocument.Shapes.AddTextbox(msoTextOrientationHorizontal, 1, 1, 1, 1, ActiveDocument.Paragraphs.Last.Range) '.Name = "PayLeft"
With oShp 'ActiveDocument.Shapes("FormattedDate")
.Line.Visible = msoFalse
.Fill.Visible = msoFalse
.RelativeHorizontalPosition = wdRelativeHorizontalPositionPage
.RelativeVerticalPosition = wdRelativeVerticalPositionPage
.Left = Application.InchesToPoints(6.16)
.Top = Application.InchesToPoints(0.98)
.Width = Application.InchesToPoints(1.6)
.Height = Application.InchesToPoints(0.58)
With .TextFrame.TextRange
.Text = FormattedDate & vbNewLine & "D D M M Y Y Y Y"
.ParagraphFormat.Alignment = wdAlignParagraphRight
With .Font
.Name = "Calibri"
.Size = 11
End With
With .ParagraphFormat
.LineSpacingRule = wdLineSpaceSingle
.SpaceAfter = 0
End With
End With
End With

'Formatted Pay Value (Right)
Set oShp = ActiveDocument.Shapes.AddTextbox(msoTextOrientationHorizontal, 1, 1, 1, 1, ActiveDocument.Paragraphs.Last.Range) '.Name = "PayLeft"
With oShp 'ActiveDocument.Shapes("PayRight")
.Line.Visible = msoFalse
.Fill.Visible = msoFalse
.RelativeHorizontalPosition = wdRelativeHorizontalPositionPage
.RelativeVerticalPosition = wdRelativeVerticalPositionPage
.Left = Application.InchesToPoints(5.76)
.Top = Application.InchesToPoints(1.39)
.Width = Application.InchesToPoints(2)
.Height = Application.InchesToPoints(0.29)

With .TextFrame.TextRange
.Text = PayRight
.ParagraphFormat.Alignment = wdAlignParagraphRight
With .Font
.Name = "Calibri"
.Size = 11
End With
End With
End With
End Sub

[a_ahmed]

God bless my friend :) That worked as expected!

I need to learn more about word VBA honsetly, i'm new to it and not familiar with how 'ranges' work in Word and the differences between Excel VBA which I'm familiar with. There doesn't seem to be as much on Word VBA online as with Excel VBA :/

[gmaxey]

Look up "Anchor" in the Help files. There were two issues with your method. 1) You did not define an anchor so the default anchor of the first paragraph on page 1 was used automatically and 2) Even after defining the anchor as the last paragraph of the document, you would have created the five new shapes with each iteration, but would process the original first fived named shapes each time.