Dabbler32
11-04-2015, 01:58 PM
Hello all. Okay, this one I can't figure out. Any help would be appreciated.
Two docs. Two codes. Very similar to each other. However, same code gives me error 4160 in one document, and for the life of me, can't figure out why. here are the two codes. The first one works fine. The second err's out on the line "Set obj1 = Documents(str7).Range.Duplicate"
Private Sub CommandButton1_Click()
Dim mystr, mystr2, dte1, dte2, dte3, dte4 As Date
Dim objword As Object
Dim obj1 As Object
str2 = VBA.Environ("Username")
str1 = "\\obufs01\common\Templates\Signatures\" & str2 & ".docx"
Set objword = GetObject(, "Word.Application")
objword.Visible = True
If IsDate(UserForm6.TextBox1.Value) = False Or Len(UserForm6.TextBox1.Value) < 10 Then
MsgBox ("Must enter a valid date being mm/dd/yyyy")
UserForm6.TextBox1.Value = Null
Cancel = True
If UserForm6.TextBox1 = vbNullString Then Exit Sub
End If
mystr = UserForm6.TextBox1
mystr2 = DateAdd("yyyy", 1, mystr)
ThisDocument.FormFields("EffectiveDate1").Result = mystr
ThisDocument.FormFields("EndDate1").Result = mystr2
dte1 = ThisDocument.FormFields("DOL1").Result
dte2 = ThisDocument.FormFields("DOL3").Result
dte3 = ThisDocument.FormFields("EffectiveDate1").Result
dte4 = ThisDocument.FormFields("EndDate1").Result
ThisDocument.FormFields("DOL1").Result = Format(dte1, "long date")
ThisDocument.FormFields("DOL3").Result = Format(dte2, "long date")
ThisDocument.FormFields("EffectiveDate1").Result = Format(dte3, "long date")
ThisDocument.FormFields("EndDate1").Result = Format(dte4, "long date")
On Error GoTo 101
objword.Documents.Open str1
On Error GoTo 0
ThisDocument.Unprotect
Set obj1 = Documents(str1).Range.Duplicate
ThisDocument.Bookmarks("SignatureLine").Range.FormattedText = obj1.FormattedText
objword.Documents(str1).Close SaveChanges:=wdDoNotSaveChanges
Set objword = Nothing
102
Unload Me
GoTo 1000
101
MsgBox "Sorry, I am unable to add your signature to the letter. Please see your administrator."
ThisDocument.Unprotect
GoTo 102
1000
ThisDocument.Protect Type:=wdAllowOnlyFormFields, NoReset:=True
UserForm7.Show
End Sub
Second document......been through it 1000 times. I don't get it.
Private Sub CommandButton1_Click()
Dim str1, str2, str3, str4, str5, str6, str7 As String
Dim dte1, dte2, dte3, dte4, dte5, dte6 As Date
Dim objWord As Object
Dim obj1 As Object
str1 = ThisDocument.FormFields("Insured1").Result
str2 = ThisDocument.FormFields("ClaimNumber1").Result
str3 = ThisDocument.FormFields("PolicyNumber1").Result
str4 = "U:\Letters Written\"
str5 = "U:\Letters%20Written\ROR%20Letters\"
str6 = VBA.Environ("Username")
str7 = "\\obufs01\common\Templates\Signatures\" & str6 & ".docx"
Set objWord = CreateObject("Word.Application")
objWord.Visible = True
102
If IsDate(UserForm6.TextBox1.Value) = False Or Len(UserForm6.TextBox1.Value) < 10 Then
MsgBox ("Must enter a valid date being mm/dd/yyyy")
UserForm6.TextBox1.Value = Null
Cancel = True
If UserForm6.TextBox1 = vbNullString Then Exit Sub
End If
dte5 = UserForm6.TextBox1
dte6 = DateAdd("yyyy", 1, dte5)
ThisDocument.FormFields("EffectiveDate1").Result = dte5
ThisDocument.FormFields("EndDate1").Result = dte6
dte1 = ThisDocument.FormFields("DOL1").Result
dte2 = ThisDocument.FormFields("DOL3").Result
dte3 = ThisDocument.FormFields("EffectiveDate1").Result
dte4 = ThisDocument.FormFields("EndDate1").Result
ThisDocument.FormFields("DOL1").Result = Format(dte1, "long date")
ThisDocument.FormFields("DOL3").Result = Format(dte2, "long date")
ThisDocument.FormFields("EffectiveDate1").Result = Format(dte3, "long date")
ThisDocument.FormFields("EndDate1").Result = Format(dte4, "long date")
On Error GoTo 101
objWord.Documents.Open str7
On Error GoTo 0
ThisDocument.Unprotect
Set obj1 = Documents(str7).Range.Duplicate
ThisDocument.Bookmarks("SignatureLine").Range.FormattedText = obj1.FormattedText
ThisDocument.Protect Type:=wdAllowOnlyFormFields, NoReset:=True
objWord.Documents(str7).Close SaveChanges:=wdDoNotSaveChanges
objWord.Visible = True
Set objWord = Nothing
Unload Me
If Dir(str4, vbDirectory) = "" Then MkDir Path:=str4
If Dir(str5, vbDirectory) = "" Then MkDir Path:=str5
ThisDocument.SaveAs FileName:=str5 & str1 & " Claim#" & str2 & " Policy#" & str3 & ".docx", FileFormat:=wdFormatXMLDocument, LockComments:=False, Password:=""
GoTo 1000
101
MsgBox "Sorry, I am unable to add your signature to the letter. Please see your administrator."
GoTo 102
1000
End Sub
Two docs. Two codes. Very similar to each other. However, same code gives me error 4160 in one document, and for the life of me, can't figure out why. here are the two codes. The first one works fine. The second err's out on the line "Set obj1 = Documents(str7).Range.Duplicate"
Private Sub CommandButton1_Click()
Dim mystr, mystr2, dte1, dte2, dte3, dte4 As Date
Dim objword As Object
Dim obj1 As Object
str2 = VBA.Environ("Username")
str1 = "\\obufs01\common\Templates\Signatures\" & str2 & ".docx"
Set objword = GetObject(, "Word.Application")
objword.Visible = True
If IsDate(UserForm6.TextBox1.Value) = False Or Len(UserForm6.TextBox1.Value) < 10 Then
MsgBox ("Must enter a valid date being mm/dd/yyyy")
UserForm6.TextBox1.Value = Null
Cancel = True
If UserForm6.TextBox1 = vbNullString Then Exit Sub
End If
mystr = UserForm6.TextBox1
mystr2 = DateAdd("yyyy", 1, mystr)
ThisDocument.FormFields("EffectiveDate1").Result = mystr
ThisDocument.FormFields("EndDate1").Result = mystr2
dte1 = ThisDocument.FormFields("DOL1").Result
dte2 = ThisDocument.FormFields("DOL3").Result
dte3 = ThisDocument.FormFields("EffectiveDate1").Result
dte4 = ThisDocument.FormFields("EndDate1").Result
ThisDocument.FormFields("DOL1").Result = Format(dte1, "long date")
ThisDocument.FormFields("DOL3").Result = Format(dte2, "long date")
ThisDocument.FormFields("EffectiveDate1").Result = Format(dte3, "long date")
ThisDocument.FormFields("EndDate1").Result = Format(dte4, "long date")
On Error GoTo 101
objword.Documents.Open str1
On Error GoTo 0
ThisDocument.Unprotect
Set obj1 = Documents(str1).Range.Duplicate
ThisDocument.Bookmarks("SignatureLine").Range.FormattedText = obj1.FormattedText
objword.Documents(str1).Close SaveChanges:=wdDoNotSaveChanges
Set objword = Nothing
102
Unload Me
GoTo 1000
101
MsgBox "Sorry, I am unable to add your signature to the letter. Please see your administrator."
ThisDocument.Unprotect
GoTo 102
1000
ThisDocument.Protect Type:=wdAllowOnlyFormFields, NoReset:=True
UserForm7.Show
End Sub
Second document......been through it 1000 times. I don't get it.
Private Sub CommandButton1_Click()
Dim str1, str2, str3, str4, str5, str6, str7 As String
Dim dte1, dte2, dte3, dte4, dte5, dte6 As Date
Dim objWord As Object
Dim obj1 As Object
str1 = ThisDocument.FormFields("Insured1").Result
str2 = ThisDocument.FormFields("ClaimNumber1").Result
str3 = ThisDocument.FormFields("PolicyNumber1").Result
str4 = "U:\Letters Written\"
str5 = "U:\Letters%20Written\ROR%20Letters\"
str6 = VBA.Environ("Username")
str7 = "\\obufs01\common\Templates\Signatures\" & str6 & ".docx"
Set objWord = CreateObject("Word.Application")
objWord.Visible = True
102
If IsDate(UserForm6.TextBox1.Value) = False Or Len(UserForm6.TextBox1.Value) < 10 Then
MsgBox ("Must enter a valid date being mm/dd/yyyy")
UserForm6.TextBox1.Value = Null
Cancel = True
If UserForm6.TextBox1 = vbNullString Then Exit Sub
End If
dte5 = UserForm6.TextBox1
dte6 = DateAdd("yyyy", 1, dte5)
ThisDocument.FormFields("EffectiveDate1").Result = dte5
ThisDocument.FormFields("EndDate1").Result = dte6
dte1 = ThisDocument.FormFields("DOL1").Result
dte2 = ThisDocument.FormFields("DOL3").Result
dte3 = ThisDocument.FormFields("EffectiveDate1").Result
dte4 = ThisDocument.FormFields("EndDate1").Result
ThisDocument.FormFields("DOL1").Result = Format(dte1, "long date")
ThisDocument.FormFields("DOL3").Result = Format(dte2, "long date")
ThisDocument.FormFields("EffectiveDate1").Result = Format(dte3, "long date")
ThisDocument.FormFields("EndDate1").Result = Format(dte4, "long date")
On Error GoTo 101
objWord.Documents.Open str7
On Error GoTo 0
ThisDocument.Unprotect
Set obj1 = Documents(str7).Range.Duplicate
ThisDocument.Bookmarks("SignatureLine").Range.FormattedText = obj1.FormattedText
ThisDocument.Protect Type:=wdAllowOnlyFormFields, NoReset:=True
objWord.Documents(str7).Close SaveChanges:=wdDoNotSaveChanges
objWord.Visible = True
Set objWord = Nothing
Unload Me
If Dir(str4, vbDirectory) = "" Then MkDir Path:=str4
If Dir(str5, vbDirectory) = "" Then MkDir Path:=str5
ThisDocument.SaveAs FileName:=str5 & str1 & " Claim#" & str2 & " Policy#" & str3 & ".docx", FileFormat:=wdFormatXMLDocument, LockComments:=False, Password:=""
GoTo 1000
101
MsgBox "Sorry, I am unable to add your signature to the letter. Please see your administrator."
GoTo 102
1000
End Sub