ET15
03-22-2019, 05:20 PM
I have a repetitive task that I am tryingto automate. The user drafts a chunk of text in Word, which often totals 3,000+characters. This text needs to be inputinto a website that has fields via Internet Explorer (IE). These fields can only hold approximately 230characters including spaces. Constraint: users cannot type directly into the IEfields.
Ideally, I’d like to use MS WORD andVBA to read the chunk of text and split it into 230 character mini-chunks, butnot split whole words in the process. Then, add those mini-chunks to the clipboardand programmatically deliver them to the fields in the IE web page.
I can’t figure out how to parse thebig chunk into 230 character mini-chunks while simultaneously not splitting wholewords. So, I created Text Form Fields and set the maximum lengths to 230 characters.The user types in Text Form Field1 until it is full and then resumes typing inText Form Field 2 until it is full and then resumes typing in Text Form Field 3and so on. This resolves the word split problem because the user can transitionto a new Text Form Field without splitting words.
I can create command button 1 andhave it copy the Text Form Field 1 content to the clipboard and then sendkey theText Form Field 1 content in the clipboard to the first field in IE and sendkeya tab to move the insertion point to the next empty field in IE.
I can create command button 2 andhave it copy the Text Form Field 2 content to the clipboard and then sendkey theText Form Field 1 content in the clipboard to the second field in IE andsendkey a tab to move the insertion point to the next empty field in IE…and soon until the Nth Text Form Field and Nth command button facilitates moving themini-chunks to the IE fields. Constraint: I cannot change the character limit imposedby the IE website.
This is clunky because it requires theuser to manually split up the big chunk of text by typing in multiple Text FormFields and then click on multiple command buttons to get all the separate TextForm Fields added to the clipboard and programmatically delivered to the IEwebpage fields.
I can’t figure out how to parse thebig chunk programmatically. Also, apparently the system clipboard only cancopy/paste one mini-chunk at a time. It is my understanding the OfficeClipboard can store 24 unique copy instances, but that VBA cannot accessthe Office Clipboard. It would be great if the text could be parsedprogrammatically. It would be great if there was a way iterate through thecopy/paste of the separate Text Form Fields with a single-click.
Is there a way?
Private Sub SendToXYZButton1_Click()
Dim strText As String
Selection.GoTo What:=wdGoToBookmark,Name:="XYZremarks1"
strText = Selection.Text
If Len(strText) = 0 Then
MsgBox "Sorry, no XYZ Remark(s)was detected in the ""Drafting Field""." & vbCr& vbCr & "The XYZ Remark(s) was not sent to XYZ. Please complete your XYZ Remark(s) and click""Send to XYZ"" again!", vbExclamation, "EmptyDrafting Field- Not Sent to XYZ"
Exit Sub
End If
If InStr(strText, "*") > 0 Then
MsgBox "Sorry, one or moreasterisks (*) was detected in the ""Drafting Field,"" whichindicates one or more drop-down menus or date controls was not yetcompleted." & vbCr & vbCr & "The XYZ Remark(s) was notsent to XYZ. Please complete your XYZRemark(s) and click ""Send to XYZ"" again!",vbExclamation, "Unfinished Statement(s)- Not Sent to XYZ"
Exit Sub
ElseIf InStr(strText, "---") > 0 Then
MsgBox "Sorry, one or moredrop-down menus was improperly completed." & vbCr & vbCr &"The XYZ Remark(s) was not sent to XYZ. Please complete your XYZ Remark(s) and click ""Send toXYZ"" again!", vbExclamation, "Unfinished Statement(s)- NotSent to XYZ"
Exit Sub
ElseIf InStr(strText, "[") > 0 Or InStr(strText,"]") > 0 Then
MsgBox "Sorry, one or more[freehand text fields] was not yet completed." & vbCr & vbCr &"The XYZ Remark(s) was not sent to XYZ. Please complete your XYZ Remark(s) and click ""Send toXYZ"" again!", vbExclamation, "Unfinished Statement(s)- NotSent to XYZ"
Exit Sub
End If
'RunSpellcheck
Selection.GoTo What:=wdGoToBookmark,Name:="XYZremarks1"
strText = Selection.Text
Dim myDO As DataObject
Set myDO = New DataObject
myDO.SetText Selection.Text
myDO.PutInClipboard
AppActivate ("XYZ, Details Page - Internet Explorer")
'SendKeys "^V" Explorer versus Chrome is very picky about caseof the "v"
'Restores numlock key to on, BUT irrespective of previous state.
If Selection.Information(wdNumLock) = False Then
SendKeys ("^v")
SendKeys ("{tab}")
SendKeys ("{tab}")
Else
SendKeys ("^v")
SendKeys ("{tab}")
SendKeys ("{tab}")
SendKeys "{NUMLOCK}%s"
End If
MsgBox "Please inspect and confirm that your completed XYZRemark(s) correctly populated into the XYZ ""XYZRemark(s)"" drafting field(s).", vbOKOnly Or vbExclamation,"Warning! Data Sent to XYZ- Please Confirm!"
Exit Sub
End Sub
Ideally, I’d like to use MS WORD andVBA to read the chunk of text and split it into 230 character mini-chunks, butnot split whole words in the process. Then, add those mini-chunks to the clipboardand programmatically deliver them to the fields in the IE web page.
I can’t figure out how to parse thebig chunk into 230 character mini-chunks while simultaneously not splitting wholewords. So, I created Text Form Fields and set the maximum lengths to 230 characters.The user types in Text Form Field1 until it is full and then resumes typing inText Form Field 2 until it is full and then resumes typing in Text Form Field 3and so on. This resolves the word split problem because the user can transitionto a new Text Form Field without splitting words.
I can create command button 1 andhave it copy the Text Form Field 1 content to the clipboard and then sendkey theText Form Field 1 content in the clipboard to the first field in IE and sendkeya tab to move the insertion point to the next empty field in IE.
I can create command button 2 andhave it copy the Text Form Field 2 content to the clipboard and then sendkey theText Form Field 1 content in the clipboard to the second field in IE andsendkey a tab to move the insertion point to the next empty field in IE…and soon until the Nth Text Form Field and Nth command button facilitates moving themini-chunks to the IE fields. Constraint: I cannot change the character limit imposedby the IE website.
This is clunky because it requires theuser to manually split up the big chunk of text by typing in multiple Text FormFields and then click on multiple command buttons to get all the separate TextForm Fields added to the clipboard and programmatically delivered to the IEwebpage fields.
I can’t figure out how to parse thebig chunk programmatically. Also, apparently the system clipboard only cancopy/paste one mini-chunk at a time. It is my understanding the OfficeClipboard can store 24 unique copy instances, but that VBA cannot accessthe Office Clipboard. It would be great if the text could be parsedprogrammatically. It would be great if there was a way iterate through thecopy/paste of the separate Text Form Fields with a single-click.
Is there a way?
Private Sub SendToXYZButton1_Click()
Dim strText As String
Selection.GoTo What:=wdGoToBookmark,Name:="XYZremarks1"
strText = Selection.Text
If Len(strText) = 0 Then
MsgBox "Sorry, no XYZ Remark(s)was detected in the ""Drafting Field""." & vbCr& vbCr & "The XYZ Remark(s) was not sent to XYZ. Please complete your XYZ Remark(s) and click""Send to XYZ"" again!", vbExclamation, "EmptyDrafting Field- Not Sent to XYZ"
Exit Sub
End If
If InStr(strText, "*") > 0 Then
MsgBox "Sorry, one or moreasterisks (*) was detected in the ""Drafting Field,"" whichindicates one or more drop-down menus or date controls was not yetcompleted." & vbCr & vbCr & "The XYZ Remark(s) was notsent to XYZ. Please complete your XYZRemark(s) and click ""Send to XYZ"" again!",vbExclamation, "Unfinished Statement(s)- Not Sent to XYZ"
Exit Sub
ElseIf InStr(strText, "---") > 0 Then
MsgBox "Sorry, one or moredrop-down menus was improperly completed." & vbCr & vbCr &"The XYZ Remark(s) was not sent to XYZ. Please complete your XYZ Remark(s) and click ""Send toXYZ"" again!", vbExclamation, "Unfinished Statement(s)- NotSent to XYZ"
Exit Sub
ElseIf InStr(strText, "[") > 0 Or InStr(strText,"]") > 0 Then
MsgBox "Sorry, one or more[freehand text fields] was not yet completed." & vbCr & vbCr &"The XYZ Remark(s) was not sent to XYZ. Please complete your XYZ Remark(s) and click ""Send toXYZ"" again!", vbExclamation, "Unfinished Statement(s)- NotSent to XYZ"
Exit Sub
End If
'RunSpellcheck
Selection.GoTo What:=wdGoToBookmark,Name:="XYZremarks1"
strText = Selection.Text
Dim myDO As DataObject
Set myDO = New DataObject
myDO.SetText Selection.Text
myDO.PutInClipboard
AppActivate ("XYZ, Details Page - Internet Explorer")
'SendKeys "^V" Explorer versus Chrome is very picky about caseof the "v"
'Restores numlock key to on, BUT irrespective of previous state.
If Selection.Information(wdNumLock) = False Then
SendKeys ("^v")
SendKeys ("{tab}")
SendKeys ("{tab}")
Else
SendKeys ("^v")
SendKeys ("{tab}")
SendKeys ("{tab}")
SendKeys "{NUMLOCK}%s"
End If
MsgBox "Please inspect and confirm that your completed XYZRemark(s) correctly populated into the XYZ ""XYZRemark(s)"" drafting field(s).", vbOKOnly Or vbExclamation,"Warning! Data Sent to XYZ- Please Confirm!"
Exit Sub
End Sub