Create a Word Document with VBA

SamDsouza · #1 03-05-2020, 06:13 AM

Hello

I am getting the above error on below syntax. Though i'v e ran the macro from VBA excel to open the word document

Code:

     objDoc.Range(objDoc.Paragraphs(17).Range.Characters(6).Start, objDoc.Paragraphs(17).Range.Characters(timeLen).End).Font.Bold = True

What i want is to Bold a text Value but the error does not allow.
Please guide with me proper set references, document etc

Code:

Public Sub msWord_Structure()

Dim objWord As Object
Dim txtword As String, sh As Worksheet
Dim objDoc As Object, objSelection As Object
Dim objRange As Object
Dim objTable As Object, objTable2 As Object
Dim para As Object
Dim intRows As Integer
Dim intCols As Integer
  Dim ocell As Object
Dim mpStart As Long
Dim chqNobold As String

Dim txtHeader As String, txtForRtgs As String

chqNobold = txtChqNo.Text
txtHeader = "RTGS/ Request Form" & vbCrLf & vbCrLf
txtword =  Chr(144) & "RTGS                             " & Chr(144) & "" & vbCrLf & vbCrLf
txtForRtgs = "For RTGS"


On Error Resume Next
    Set objWord = GetObject(, "Word.Application")
      If Err Then
           Set objWord = CreateObject("Word.Application")
      End If
    On Error GoTo 0

    Set objDoc = objWord.Documents.Add
        objWord.Visible = True
        objWord.activedocument.Range.Font.Name = "Tahoma"
        objWord.activedocument.Range.Font.Size = "12"
        objWord.activedocument.Paragraphs.SpaceAfter = 0 'InchesToPoints(0.5)
    
 Set objRange = objDoc.Range
    With objRange
        .Collapse Direction:=1
        .Text = txtHeader
          .Font.Name = "Tahoma"
          .Font.Size = "16"
          .Font.Underline = True 'wdUnderlineSingle
          .Paragraphs.SpaceAfter = 0
          .ParagraphFormat.Alignment = 1 'this centers\'wdAlignParagraphCenter '3
    .Collapse Direction:=0
    End With

      
      With objRange
           .Collapse Direction:=1
          .Text = txtword
          .Font.Name = "Tahoma"
          .Font.Size = "12"
          .Paragraphs.SpaceAfter = 0
          .ParagraphFormat.Alignment = 3
          '.Collapse Direction:=wdCollapseEnd
          .Collapse Direction:=0
      End With
      
With objRange
           .Collapse Direction:=1
          .Text = txtForRtgs
          .Font.Name = "Tahoma"
          .Font.Size = "12"
          .Font.Underline = True 'wdUnderlineSingle
          .Paragraphs.SpaceAfter = 0
          .ParagraphFormat.Alignment = 3

      End With
      
            
            intRows = 4: intCols = 2
    Set objTable = objDoc.Tables.Add(objRange, intRows, intCols)
  
   With objTable
          .Columns(1).Width = 200
          .Columns(2).Width = 300

          .Borders.Enable = True
  
       .Rows.HorizontalPosition = 5  '100
       .Rows.VerticalPosition = 10
       .Columns(1).Width = 300
       .Columns(2).Width = 130
       
       .Range.Collapse Direction:=0
       .Borders.Enable = True
              
       Set ocell = .Cell(1, 1).Range
           ocell.Text = "Maximum Limit For  Transaction"
           ocell.End = .Cell(1, 2).Range.End
           ocell.Cells.Merge
           ocell.ParagraphFormat.Alignment = 1
           'ocell.ParagraphFormat.Alignment = wdAlignParagraphCenter
           ocell.End = ocell.End - 1
            
            
      Set ocell = .Cell(2, 1).Range
            ocell.End = ocell.End - 1
            ocell.Text = "IDBI Bank Customer"
            ocell.ParagraphFormat.Alignment = 3
            
      Set ocell = .Cell(2, 2).Range
            ocell.End = ocell.End - 1
            ocell.Text = "No Limit"
            ocell.ParagraphFormat.Alignment = 3
            
      Set ocell = .Cell(3, 1).Range
            ocell.End = ocell.End - 1
            ocell.Text = "Non IDBI Bank Customer & Indo Nepal  Remittance"
            ocell.ParagraphFormat.Alignment = 3
 
      Set ocell = .Cell(3, 2).Range
            ocell.End = ocell.End - 1
            ocell.Text = "Upto INR 50,000/-"
            ocell.ParagraphFormat.Alignment = 3
            
      Set ocell = .Cell(4, 1).Range
            ocell.End = ocell.End - 1
            ocell.Text = "Purpose of Remittance to Nepal - Family Maintenance only"
            ocell.ParagraphFormat.Alignment = 3
            
      Set ocell = .Cell(4, 2).Range
            ocell.End = ocell.End - 1
            ocell.Text = "Yes / No"
            ocell.ParagraphFormat.Alignment = 3
                     ' .Range.Collapse Direction:=wdCollapseEnd
            ocell.Collapse Direction:=0

End With

Dim timeLen As Integer, custNameLen As Integer, RefAcNoLen As Integer, chqNoLen As Integer, MyContactNosLen As Integer
timeLen = (6 + Len(txtTime.Text) + 1)
custNameLen = (16 + Len(cmbMyCustName.Text) + 1)
RefAcNoLen = (13 + Len(txtRefAcNo.Text) + 1)
chqNoLen = (10 + Len(txtChqNo.Text) + 1)
MyContactNosLen = (10 + Len(txtMyContactNos.Text) + 1)
     
     objDoc.Range(objDoc.Paragraphs(17).Range.Characters(6).Start, _
     objDoc.Paragraphs(17).Range.Characters(timeLen).End).Font.Bold = True



txtword = vbNewLine & "Time " & txtTime.Text & vbNewLine & vbNewLine & "Customer Name : " & cmbMyCustName.Text & vbNewLine & vbNewLine & _
"Account No. " & txtRefAcNo.Text & " Chq No. " & txtChqNo.Text & vbNewLine & vbNewLine & "Mobile No" & txtMyContactNos.Text & " Tel No." & _
 vbNewLine & vbNewLine & "Address of Remitter (Mandatory for Non IDBI Bank Customer)___________________________________" & vbNewLine & vbNewLine & _
"__________________________________________" & " Email ID ______________________________________________" & vbNewLine & vbNewLine & _
 vbNewLine & vbNewLine & " Incase of NonIDBI Bank Customer Amount of Cash Deposited _____________________________________" & _
 vbNewLine & vbNewLine '& " Beneficiary Details :"


Set objRange = objDoc.Range
 With objRange
          .Collapse Direction:=0 
          .Text = txtword
          .Font.Name = "Tahoma"
          .Font.Size = "12"
          .Paragraphs.SpaceAfter = 0
          .Collapse Direction:=0 ' wdCollapseStart

      End With

Thanks
SamD

eduzs · #2 03-05-2020, 04:37 PM

Maybe or there's no 17 paragraph or there's no this range of characters.
Put a Stop (breakpoint) in code at this line to see what's the value of timelen variable and see if it's a valid range of characters at paragraph 17.

macropod · #3 03-05-2020, 06:20 PM

My question is: Why you're using all this VBA code to write boilerplate content to a document instead of using to create a document from a template that already contains all of that content?

With a template you don't need anything between:
Set objDoc = objWord.Documents.Add - with a template reference
and:
txtword =
(and even most of what's in txtword you can do without), or any of the:
objRange
stuff at the end of your code. Moreover, with a template, the only variables you need are:
Dim objWord As Object, objDoc As Object, txtword As String

SamDsouza · #4 03-05-2020, 10:13 PM

Eduzs

Quote:

Put a Stop (breakpoint) in code at this line to see what's the value of timelen variable and see if it's a valid range of characters at paragraph 17.

How can i Stop or BreakPoint

the code is already as per #1 .Would request you to kindly paste in your Excel VBA and check what happens

Observation was like till Para 13 and 14 it executes Well. FYI Para 13 and 14 consits the data as per the Cell range "Purpose of Remittance to Nepal - Family Maintenance only"
& "Yes / No" respectively.
After Para 14 somehow it does not detect as Paragraph. the issue lies here.
What needs to be done.
Code is already there for your perusal and request you to revert back

Paul Sir,
I Did understand the irritating and Frustrating the boilerplate part.
though i could have created the Template and coded but this time i wanted to enjoy the
food out of the boilerplate
With your guidance Shall learn something new all together in different manner
By the way i checked "Time" was at paragraphs 17 with the following threadVBA Express : Word - Get Line and Paragraph Number

And with above only i put the paragraph as 17 as per coding in #1 but was unsuccessfull
Thanks
SamD

macropod · #5 03-05-2020, 11:32 PM

Quote:

Originally Posted by SamDsouza

I Did understand the irritating and Frustrating the boilerplate part.
though i could have created the Template and coded but this time i wanted to enjoy the
food out of the boilerplate
With your guidance Shall learn something new all together in different manner

Well, if you're going to code the boilerplate text, you might as well do it efficiently. Your code has numerous redundant lines. Try:

Code:

Public Sub msWord_Structure()
Dim wdApp As Object, wdDoc As Object, wdTbl As Object, wdRng As Object, chqNobold As String, StrTxt As String
Dim custNameLen As Long, RefAcNoLen As Long, chqNoLen As Long, MyContactNosLen As Long
Const wdCollapseEnd As Long = 0
Const wdAlignParagraphCenter As Long = 1
Const wdAlignParagraphJustify As Long = 3

chqNobold = txtChqNo.Text
custNameLen = (17 + Len(cmbMyCustName.Text))
RefAcNoLen = (14 + Len(txtRefAcNo.Text))
chqNoLen = (11 + Len(chqNobold))
MyContactNosLen = (11 + Len(txtMyContactNos.Text))

StrTxt = vbCr & "Time " & txtTime.Text & vbCr & vbCr & _
  "Customer Name : " & cmbMyCustName.Text & vbCr & vbCr & _
  "Account No. " & txtRefAcNo.Text & " Chq No. " & txtChqNo.Text & vbCr & vbCr & _
  "Mobile No" & txtMyContactNos.Text & " Tel No." & vbCr & vbCr & _
  "Address of Remitter (Mandatory for Non IDBI Bank Customer)___________________________________" & vbCr & vbCr & _
  "__________________________________________" & " Email ID ______________________________________________" & vbCr & vbCr & _
 vbCr & vbCr & " Incase of NonIDBI Bank Customer Amount of Cash Deposited _____________________________________" & vbCr & vbCr '& " Beneficiary Details :"

On Error Resume Next
Set wdApp = GetObject(, "Word.Application")
If Err Then Set wdApp = CreateObject("Word.Application")
On Error GoTo 0
wdApp.Visible = True

Set wdDoc = wdApp.Documents.Add
With wdDoc.Range
  .Font.Name = "Tahoma"
  .Font.Size = 12
  .Paragraphs.SpaceAfter = 0
  .ParagraphFormat.Alignment = wdAlignParagraphJustify
  .Text = "RTGS/ Request Form" & vbCrLf & vbCrLf & _
      Chr(144) & "RTGS                             " & Chr(144) & "" & _
      vbCrLf & vbCrLf & "For RTGS" & vbCr
  With .Paragraphs(1).Range
    .Font.Size = 16
    .Font.Underline = True
    .ParagraphFormat.Alignment = wdAlignParagraphCenter
  End With
  .Paragraphs.Last.Previous.Range.Font.Underline = True
  Set wdTbl = .Tables.Add(.Characters.Last, 4, 2)
  With wdTbl
    .Columns(1).Width = 300
    .Columns(2).Width = 130
    .Borders.Enable = True
    .Rows.HorizontalPosition = 5
    .Rows.VerticalPosition = 10
    With .Cell(1, 1)
      .Merge wdTbl.Cell(1, 2)
      .Range.Text = "Maximum Limit For Transaction"
      .Range.ParagraphFormat.Alignment = wdAlignParagraphCenter
    End With
    With .Cell(2, 1).Range
      .Text = "IDBI Bank Customer"
      .ParagraphFormat.Alignment = wdAlignParagraphJustify
    End With
    With .Cell(2, 2).Range
      .Text = "No Limit"
      .ParagraphFormat.Alignment = wdAlignParagraphJustify
    End With
    With .Cell(3, 1).Range
      .Text = "Non IDBI Bank Customer & Indo Nepal Remittance"
      .ParagraphFormat.Alignment = 3
    End With
    With .Cell(3, 2).Range
      .Text = "Up to INR 50,000/-"
      .ParagraphFormat.Alignment = wdAlignParagraphJustify
    End With
    With .Cell(4, 1).Range
      .Text = "Purpose of Remittance to Nepal - Family Maintenance only"
      .ParagraphFormat.Alignment = wdAlignParagraphJustify
    End With
    With .Cell(4, 2).Range
      .Text = "Yes / No"
      .ParagraphFormat.Alignment = wdAlignParagraphJustify
    End With
  End With
  .InsertAfter StrTxt
  Set wdRng = .Paragraphs(18).Range
  With wdRng
    .Start = .Start + 5
    .End = .End - 1
    .Font.Bold = True
  End With
End With
End Sub

Quote:

Originally Posted by SamDsouza

After Para 14 somehow it does not detect as Paragraph. the issue lies here.
…
By the way i checked "Time" was at paragraphs 17 with the following threadVBA Express : Word - Get Line and Paragraph Number

And with above only i put the paragraph as 17 as per coding in #1 but was unsuccessfull

Without wanting to place too fine a point on it, your code was trying to modify the paragraph before you'd created it (i.e. you hadn't written 'txtword' to the document)...

SamDsouza · #6 03-06-2020, 12:02 AM

Paul Sir,

appreciate your quick reply

Same Error Occurs in your code marked with Red

With wdRng
.Start = .Characters(6)
.End = .Characters(timeLen)
.Font.Bold = True
End With
End With

unfortunately the full structure has been modified with your coding. as in your coding the missing part is from txtHeader as per#1. Sorry for the inconvienence
By the Way Do i need to insert your coding somewhere or how its like ?
SamD

macropod · #7 03-06-2020, 12:08 AM

The code I supplied is a complete unit - you can't just take part of it and paste it into your own code. The error you're now getting suggests your paragraph 17 is empty. In other words, you've specified the wrong paragraph. Check what paragraph:
Set wdRng = .Paragraphs(17).Range
actually points to. For example:
Set wdRng = .Paragraphs(17).Range
MsgBox wdRng.Text
Exit Sub
Does the message box give the expected text?

Quote:

Originally Posted by SamDsouza

unfortunately the full structure has been modified with your coding. as in your coding the missing part is from txtHeader as per#1.

Not if you use the entire code I posted...

SamDsouza · #8 03-06-2020, 03:18 AM

Thank you Sir,
For the immediate response

If you could help me with the word "Time" falls in which paragraph.

Question 2 : What is the Criteria to be considered For a Paragraph number or its index number with Lines and Tables and cells inside the table

SamD

macropod · #9 03-06-2020, 04:09 AM

The paragraph containing:
"Time " & txtTime.Text
is #18.

I've changed this in the code. Try it now.

SamDsouza · #10 03-06-2020, 07:28 PM

Thank you Sir,

Where are the changes you made to the code ?

Quote:

Question 2 : What is the Criteria to be considered For a Paragraph number or its index number with Lines and Tables and cells inside the table

if you could throw some hilite on the above question

and also What if there is another text box reference in paragraph #18 for eg but goes in next line How to get its reference ?

Regds
SamD

macropod · #11 03-06-2020, 07:43 PM

Quote:

Originally Posted by SamDsouza

Thank you Sir,

Where are the changes you made to the code ?

Quite obviously, I've changed the paragraph reference from 17 to 18.

Quote:

Originally Posted by SamDsouza

Quote:

Question 2 : What is the Criteria to be considered For a Paragraph number or its index number with Lines and Tables and cells inside the table

if you could throw some hilite on the above question

Paragraphs within a table can be referenced by their cell address. Indexing is the same as elsewhere.

Quote:

Originally Posted by SamDsouza

and also What if there is another text box reference in paragraph #18 for eg but goes in next line How to get its reference ?

There is nothing in your code that refers to a textbox in the document, so I don't know what you mean. That said, if instead of:

Code:

StrTxt = vbCr & "Time " & txtTime.Text & vbCr & vbCr & _
  "Customer Name : " & cmbMyCustName.Text & vbCr & vbCr & _
  "Account No. " & txtRefAcNo.Text & " Chq No. " & txtChqNo.Text & vbCr & vbCr

you used something like:

Code:

StrTxt = vbCr & "Time : " & txtTime.Text & vbCr & vbCr & _
  "Customer Name : " & cmbMyCustName.Text & vbCr & vbCr & _
  "Account No. : " & txtRefAcNo.Text & " Chq No. : " & txtChqNo.Text & vbCr & vbCr

You could use the Instr and IntrRev functions to find the ':' characters for use as offsets. For example:

Code:

  With wdRng
    .Start = .Start + InStr(.Text, ":") + 1
    .End = .End - 1
    .Font.Bold = True
  End With

All of this coding would be quite unnecessary if you used a template...

SamDsouza · #12 03-08-2020, 04:02 AM

Thank you sir, for your coding. Lastly i would like to ask you how could i add a Footer note after the above range. FYI this should display in Left of footer note.
And a small image if i could insert at Right at Footer note.

i tried using active document but could not set its refernce

Quote:

All of this coding would be quite unnecessary if you used a template...

you are right. this point is well taken and will take care.
SamD

macropod · #13 03-08-2020, 04:27 AM

You could address the footer via code like:

Code:

Const wdHeaderFooterPrimary As Long = 1
…
With .Sections.First.Footers(wdHeaderFooterPrimary).Range
  'code that populates footer
End With

before the final 'End With'.

SamDsouza · #14 03-08-2020, 06:15 AM

Quote:

You could address the footer via code like:
Code:

Code:

Const wdHeaderFooterPrimary As Long = 1
…
With .Sections.First.Footers(wdHeaderFooterPrimary).Range
  'code that populates footer
End With

before the final 'End With'

I tried below

Code:

With .Sections.First.Footers(1).Range
  'code that populates footer
End With

i get error 438 "object doesn't support this property or method

Now what to do ?

SamD

macropod · #15 03-08-2020, 02:34 PM

That suggests you placed the code somewhere other than before the final 'End With'. For consistency with the approach I took elsewhere, I'd retain the wdHeaderFooterPrimary constant and insert:

Code:

Const wdHeaderFooterPrimary As Long = 1

up where the other Word constants are defined.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
How do I create an index of every word in a document?	jbengal	Word	5	11-23-2022 02:17 PM
Can I create word wizards with Word 2016 to automate document creation?	OkiDoki	Word	4	09-30-2016 11:41 AM
Word 2010 - How to create a fillable document from a boilerplate document	sheaters	Word	2	05-04-2016 01:57 PM
How to create a special TOC in word document?	gn4619	Word VBA	5	04-29-2015 04:25 PM
How to identify whether Word 2007 was used to create a document saved as Word 2003	noviceatwork	Word	0	03-08-2012 06:40 AM