Despurado
06-17-2016, 05:45 PM
My VBA Level: Slightly above rank beginner
Issue: I'm using an Access form/data to fill form fields in a Word document. The script "A" below works great except for one Access memo field when the contents are > 255 characters. I found a solution, but I'm at a loss where/how to insert it in my script.
---------------------------------------------
A) My VBA Code:
Private Sub cmdPrintContract_Click()
'Print Word Contract for current client.
Dim appWord As Word.Application
Dim doc As Word.Document
'Avoid error 429, when Word isn’t open.
On Error GoTo errHandler
If appWord Is Nothing Then
Set appWord = CreateObject("Word.Application")
Else
End If
'Set appWord object variable to running instance of Word.
Set appWord = GetObject(, "Word.Application")
'Make Word visible to the user
appWord.Visible = True
'Open the word document
Set doc = appWord.Documents.Open("C:\TestFolder\wordcontractform.docm", , True)
With doc
.FormFields("fldUserName").Result = Me!UserName
.FormFields("fldUserAddress").Result = Me!UserAddress
.FormFields("fldUserCity").Result = Me!UserCity
.FormFields("fldUserState").Result = Me!UserState
.FormFields("fldUserZipCode").Result = Me!UserZipCode
.FormFields("fldDeliverables").Result = Me!Deliverables '<-------If text >255 char, then error msg: "Run-Time error 4609: string too long"
.Activate
.PrintPreview
.ClosePrintPreview
End With
Set doc = Nothing
Set appWord = Nothing
Exit Sub
errHandler:
Select Case Err.Number
Case 429
Set appWord = New Word.Application
Resume Next
Case Else
Msg.Box Err.Number & ": " & Err.Description
End Select
End Sub
-------------------------------------------------
B) The Generic solution (by Dave Rado)
Dim FmFld As FormField, Str1 As String
Str1 = (a long string > 256 characters)
Set FmFld = ActiveDocument.FormFields(1)
FmFld.Result = Str1
'You get an error: “String too long”
'Same if you use:
ActiveDocument.Formfields("Text1").Result = Str1
'You can get round this by using:
ActiveDocument.Unprotect
FmFld.Range.Fields(1).Result.Text = Str1
ActiveDocument.Protect Type:=wdAllowOnlyFormFields, NoReset:=True
'Or if you're referring to the formfield by name:
ActiveDocument.Unprotect
ActiveDocument.Bookmarks("Text1").Range.Fields(1).Result.Text = Str1
ActiveDocument.Protect Type:=wdAllowOnlyFormFields, NoReset:=True
---------------------------------------------------------------------------------------
The main Question: How/where to insert the relevant lines in "B" into "A"
Minor Question: In the line:
ActiveDocument.Bookmarks("Text1").Range.Fields(1).Result.Text = Str1
What is the purpose of .Range (I thought it was only for Excel files)
I REALLY need guidance right away. I'll be waiting by my computer (with a beer)
Thanks much!
Issue: I'm using an Access form/data to fill form fields in a Word document. The script "A" below works great except for one Access memo field when the contents are > 255 characters. I found a solution, but I'm at a loss where/how to insert it in my script.
---------------------------------------------
A) My VBA Code:
Private Sub cmdPrintContract_Click()
'Print Word Contract for current client.
Dim appWord As Word.Application
Dim doc As Word.Document
'Avoid error 429, when Word isn’t open.
On Error GoTo errHandler
If appWord Is Nothing Then
Set appWord = CreateObject("Word.Application")
Else
End If
'Set appWord object variable to running instance of Word.
Set appWord = GetObject(, "Word.Application")
'Make Word visible to the user
appWord.Visible = True
'Open the word document
Set doc = appWord.Documents.Open("C:\TestFolder\wordcontractform.docm", , True)
With doc
.FormFields("fldUserName").Result = Me!UserName
.FormFields("fldUserAddress").Result = Me!UserAddress
.FormFields("fldUserCity").Result = Me!UserCity
.FormFields("fldUserState").Result = Me!UserState
.FormFields("fldUserZipCode").Result = Me!UserZipCode
.FormFields("fldDeliverables").Result = Me!Deliverables '<-------If text >255 char, then error msg: "Run-Time error 4609: string too long"
.Activate
.PrintPreview
.ClosePrintPreview
End With
Set doc = Nothing
Set appWord = Nothing
Exit Sub
errHandler:
Select Case Err.Number
Case 429
Set appWord = New Word.Application
Resume Next
Case Else
Msg.Box Err.Number & ": " & Err.Description
End Select
End Sub
-------------------------------------------------
B) The Generic solution (by Dave Rado)
Dim FmFld As FormField, Str1 As String
Str1 = (a long string > 256 characters)
Set FmFld = ActiveDocument.FormFields(1)
FmFld.Result = Str1
'You get an error: “String too long”
'Same if you use:
ActiveDocument.Formfields("Text1").Result = Str1
'You can get round this by using:
ActiveDocument.Unprotect
FmFld.Range.Fields(1).Result.Text = Str1
ActiveDocument.Protect Type:=wdAllowOnlyFormFields, NoReset:=True
'Or if you're referring to the formfield by name:
ActiveDocument.Unprotect
ActiveDocument.Bookmarks("Text1").Range.Fields(1).Result.Text = Str1
ActiveDocument.Protect Type:=wdAllowOnlyFormFields, NoReset:=True
---------------------------------------------------------------------------------------
The main Question: How/where to insert the relevant lines in "B" into "A"
Minor Question: In the line:
ActiveDocument.Bookmarks("Text1").Range.Fields(1).Result.Text = Str1
What is the purpose of .Range (I thought it was only for Excel files)
I REALLY need guidance right away. I'll be waiting by my computer (with a beer)
Thanks much!