#1
|
|||
|
|||
Create a Word Document with VBA
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 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 SamD |
#2
|
|||
|
|||
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.
__________________
Backup your original file before doing any modification. |
#3
|
||||
|
||||
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
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#4
|
|||
|
|||
Eduzs
Quote:
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 |
#5
|
||||
|
||||
Quote:
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:
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#6
|
|||
|
|||
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 |
#7
|
||||
|
||||
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? Not if you use the entire code I posted...
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#8
|
|||
|
|||
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 |
#9
|
||||
|
||||
The paragraph containing:
"Time " & txtTime.Text is #18. I've changed this in the code. Try it now.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#10
|
|||
|
|||
Thank you Sir,
Where are the changes you made to the code ? Quote:
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 |
#11
|
||||
|
||||
Quite obviously, I've changed the paragraph reference from 17 to 18.
Quote:
Quote:
Code:
StrTxt = vbCr & "Time " & txtTime.Text & vbCr & vbCr & _ "Customer Name : " & cmbMyCustName.Text & vbCr & vbCr & _ "Account No. " & txtRefAcNo.Text & " Chq No. " & txtChqNo.Text & vbCr & vbCr Code:
StrTxt = vbCr & "Time : " & txtTime.Text & vbCr & vbCr & _ "Customer Name : " & cmbMyCustName.Text & vbCr & vbCr & _ "Account No. : " & txtRefAcNo.Text & " Chq No. : " & txtChqNo.Text & vbCr & vbCr Code:
With wdRng .Start = .Start + InStr(.Text, ":") + 1 .End = .End - 1 .Font.Bold = True End With
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#12
|
|||
|
|||
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:
SamD |
#13
|
||||
|
||||
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
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#14
|
|||
|
|||
Quote:
Code:
With .Sections.First.Footers(1).Range 'code that populates footer End With Now what to do ? SamD |
#15
|
||||
|
||||
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
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
Thread Tools | |
Display Modes | |
|
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 |