Error with Clipboard

StephenRay · #1 07-15-2017, 05:50 AM

Hello!
I just started learning VBA.
I use a Macro to do something very simple, actually I have about ten to find different things.
But they all do the same thing:
The macro finds some text in a Word document, highlights it , then copies the selection.
So then, the clipboard is ready for me to paste it somewhere.
That is all. But it is very useful and saves me lots of time because I paste in another application as I build a document in another non Office application. I do this all day long, and my various Macros make me twice as productive.

I added some code to get the leading and trailing spaces out.
Sometimes the copied text has leading and/or trailing spaces. Sometimes there are no spaces in the copied text.
But sometimes I get an error with the clipboard. Sometimes it works great.
I cannot isolate any cause or condition as to when it might happen, or why.
It is intermittent. In addition, wouldn’t you know it, early this morning it is working perfectly.
But yesterday I was getting:
2147221040 (800401D0) - OpenClipboard Failed
Whenever the error stops the macro, the clipboard has the copied text in it, but with spaces. I know this is true because I tried a paste and the text is in there!

Here’s the Macro, below.
Sub Macro18()
' Macro18 Macro
'
'This part clears the clipboard
Dim clipboard As MSForms.DataObject
Set clipboard = New MSForms.DataObject
clipboard.SetText ""
clipboard.PutInClipboard

'This part gets a person's name from a Word document to the clipboard.
Selection.EndKey Unit:=wdStory
Selection.MoveUp Unit:=wdLine, Count:=4
Selection.EndKey Unit:=wdLine, Extend:=wdExtend
Selection.MoveLeft Unit:=wdCharacter, Count:=1, Extend:=wdExtend
Selection.Copy

'Get the spaces out!
Dim MyDataz As DataObject
Dim strClipz As String
Set MyDataz = New DataObject

Set MyDataz = New DataObject
'The next line is highlighted in Debug as causing an error sometimes
MyDataz.GetFromClipboard
strClipz = MyDataz.GetText
strClipz = Trim(strClipz)

Set MyDataz = New DataObject

MyDataz.SetText strClipz
'The next line is highlighted in Debug as causing an error sometimes
MyDataz.PutInClipboard

End Sub

gmaxey · #2 07-15-2017, 06:29 AM

I'm not sure what one of your documents looks like when you initiate this code but why don't you take the spaces out before you ever put it in the clipboard?

Code:

Sub Macro18()
Dim oClipboard As DataObject
Dim oRng As Range
  'This part gets a person's name from a Word document to the oClipboard.
  Selection.EndKey Unit:=wdStory
  Selection.MoveUp Unit:=wdLine, Count:=4
  Selection.EndKey Unit:=wdLine, Extend:=wdExtend
  Selection.MoveLeft Unit:=wdCharacter, Count:=1, Extend:=wdExtend
  Set oRng = Selection.Range
  oRng.Text = Trim(oRng.Text)
  oRng.Copy
  Undo 1 'Put the spaces back in the text.
lbl_Exit:
  Exit Sub
End Sub

StephenRay · #3 07-15-2017, 10:11 AM

Greg, Thanks for your reply. Good idea! I didn't know it was possible. I will try it on Monday at work. In the mean time, I will have to study what you wrote with a good book, "Mastering VBA for Microsoft Office 2016" by Richard Mansfield.
But I get the idea, I think. You made what was highlighted/selected - a variable, oRng. Then you trim it. Then you copy it into the clipboard. Finally you put any spaces back into the highlighted text on the document.
But I don't see any use for the first statement:
Dim oClipboard As DataObject
Is it necessary?
Thank you so much!

gmaxey · #4 07-15-2017, 07:09 PM

Actually I don't suppose you do if you are simply going to paste into the same document.

StephenRay · #5 07-16-2017, 04:29 AM

Greg,
I couldn't wait until tomorrow to try it, so I tried it early this morning. It works.
But I have a long way to go to understand subroutines. To make it work, I had to take out these three lines:

Undo 1 'Put the spaces back in the text.
lbl_Exit:
Exit Sub

My good book doesn't mention Undo but various places in the internet show it. I am sure I can get that to work later after a little more studying. For right now, it doesn't matter if the spaces are taken out of my original document.

I am an old COBOL programmer and I have already made some pretty advanced use of nested if statements in my MACROS. And I know about subroutines in COBOL, but I still have to learn how to do it in VBA.

By the way, I am pasting to another document in another application that is not Microsoft Office. I build a document, item by item, with about 29 other similar Macros. And it is really important to take the spaces out. It is a real load off of my time if I can depend on the Macro to do it before I paste it.

Thanks Greg, I am going to paste this routine in my other Macros tomorrow morning.

gmaxey · #6 07-16-2017, 06:49 AM

Undo simply puts the spaces back in the existing text. The content of the clipboard (without the spaces) should not be affected (it isn't here). Without that step if your source document is changed permanently if you save it.

If you don't save it then I don't suppose it matters.

StephenRay · #7 07-16-2017, 08:42 AM

Greg, Yes, the Undo is something I want to get working. I will work with it and get back to you.
I spent years in drudgery before I figured out to get these various Macros working. For each unit of work, I used to spend 30 minutes of hard work, highlighting, copying, pasting. -Quick, fast work too, as fast as I could go.
Now, I can complete one unit of work in 8 minutes if there is no research I must do. Not only does this save time, I am much less fatigued. And with less work too! And I have one Macro that does a slick SaveAs for the document. So each unit of work I do is documented.
I should share this with co-workers, since it is such a time savings. But one advisor tells me not to tell anyone. Especially not to tell my boss!

gmayor · #8 07-17-2017, 01:21 AM

You will probably find that the line should be

Code:

ActiveDocument.Undo 1

or you will get an error message. You can also achieve the same thing, without Undo, by moving the start and end of the range.
There may also be a moire elegant way of extracting the name from the document, should you wish to share an example document.

gmaxey · #9 07-17-2017, 03:57 AM

Stephen,

You didn't say that you were getting an error. The reason for the error is that I threw that code together in the ThisDocument module of the project which doesn't care about ActiveDocument (because it is the activedocuement). In a standard module modified as Graham advises will also work.

StephenRay · #10 07-17-2017, 05:24 AM

Gmayor, Thank you for your attention. Yes, that works like a champ. I tested it. This is excellent information for me to know. I may need it someday. As for my Document. It is an unorganized mess that grew as I developed my Macros.

I build this document by copying one whole page from a non Office application and executing a Macro. The Macro puts everything in its place and than other macros get information from the same place every time.

Then I build another non Office document by retrieving, (copy & paste) pieces of the following document. I work for the Federal Government, USDA, and all the identifying information is changed so I don't get in trouble.

Someday I want to investigate something called Shell, maybe that will help me further because the final document is in Microsoft Active Directory.

CRQ 1234567 Joe Jones Add 6:44:40 AM Federal None
REQ000000123456 BA$$wordaaaa
Joe Jones
RD
DC
6900 HEADQUARTERS FACILITY
105944 HEADQUARTERS
1400 Independence Ave SW
County: DISTRICT OF COLUMBIA
Washington, District Of Columbia 20250-0002
United States
United States Department of Agriculture
CRQ 1234567 - User removed from Remedy System
P RD RD
Joe Jones - RD, Washington, DC
CRQ 1234567 - User, Joe Jones , for RD at site 105944 HEADQUARTERS has been removed from Remedy.
******************
CRQ 1234567 - User has been added to Remedy System

CRQ User Company Site+
CRQ 1234567 - User, Joe Jones , for RD at site 105944 HEADQUARTERS has been added to Remedy.
XXXX-CTS-TSD-NCR-XXXX-Notifications@usda.gov
SMTP:Joe.Jones@washington.falcon.usda.gov
CRQ000000999999 Joe Jones@washinton.falcon.usda.gov
Joe Jones – RD - 6900 HEADQUARTERS FACILITY – 105944 HEADQUARTERS - District Of Columbia
The AD account has been disabled and moved to the de-provisioned accounts folder. The user’s mailbox has been hidden. Account will be delete 14 business days from today. An email will be sent via Remedy to the local TSD group email distribution list.

XXXX-FVS-TSD-STATE@ZZZZ.usda.gov SMTP:Joe.Jones.falcon@XXX.washington.usda.gov
Joe Jones
000-000-0000
usdaPersonGUID
999X99XS-TV56-91BN-9210-ZX459197954245CS
eAuthID
999999999999999999999

StephenRay · #11 07-17-2017, 05:26 AM

GMAXEY, Thank you for your help. I am pasting in your code to my Macros this morning. This saves me lots of time!

gmaxey · #12 07-17-2017, 12:49 PM

In that mash of text starting with CQR 123 ....
and ending with ... 999, what piece is suppose to be extracted?

When running the code provided the result is an empty string following 000-000-0000.

gmayor · #13 07-17-2017, 09:46 PM

I get the same result as Greg with your macro from your document.

Given the document format and assuming that you want the name Joe Jones (which appears several times in the document) then find a unique reference point that is always fixed and close to the text you want to find e.g. usdaPersonGUID is two paragraphs below one instance of the required name, so using that one, the following macro will copy Joe Jones to the clipboard free from spaces and without change to the document.

If all the information you want is in the document, then if there are fixed references in the document that are the same each time the document is created, then you should be able to extract all the items you require in one macro and write them to the appropriate places in the new document without using the clipboard, but for that we would need to know where in the new document you want to place the data.

Or you could write all the required data from each such document to an Excel worksheet, or a Word table and use mail merge to create all the documents. It can certainly be simpler than running lots of macros.

Code:

Sub Macro18()
'Graham Mayor - http://www.gmayor.com - Last updated - 18 Jul 2017
Dim oClipboard As DataObject
Dim oRng As Range
    'set a range to the document
    Set oRng = ActiveDocument.Range
    With oRng.Find
        'Look for a fixed reference point in the range
        Do While .Execute(FindText:="usdaPersonGUID")
            'having found the reference shown above, move back the found text start two paragraphs to the name
            oRng.MoveStart wdParagraph, -2
            'set the end of the range to the end of that paragraph
            'and remove the paragraph break from the range
            oRng.End = oRng.Paragraphs(1).Range.End - 1
            'stop looking
            Exit Do
        Loop
    End With
    'remove trailing spaces from the range
    oRng.MoveEndWhile Chr(32), wdBackward
    'remove leading spaces from the range
    oRng.MoveStartWhile Chr(32)
    'Copy the range to the clipboard
    oRng.Copy
lbl_Exit:
    Exit Sub
End Sub

StephenRay · #14 07-18-2017, 07:05 AM

My friends, Greg, Graham, It looks like the last four lines of the document are only two lines in my original. Pasting it here seems to have changed it. I didn't notice it earlier.
Normally it would highlight and copied "Joe Jones" - four lines up from the bottom.
And yes, it is a Mash of text, isn't it? It kind of grew as I increased my Macros.
I know it is a monster. But it serves me well, because I know where everything is, more importantly, my Macros know where everything is.
Nevertheless, I know I should start over from scratch to organize it.
All this has been a learning experience.
I have been referring to my final document. Actually, the final document is Microsoft Active Directory. I am adding records, every day. I know I must see if Power Shell can help me with that. Then it will be an easy matter to assemble all the different items, (name, address, agency, etc.), - into something that PowerShell can understand.

Right now, I retrieve one piece of information at a time from the document, and paste it in Active Directory. About ten of us do it the same way, except I am the only one using Macros. We all type or paste all the information in different GUI screens in Active Directory.

I will study your code, thank you very much. You have been a big help. Now I need to go learn PowerShell too.

You know, I believe that VBA is more difficult than COBOL.

orange · #15 09-27-2017, 01:07 PM

Stephen,
Do you have any update to this --especially re Powershell?

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Clipboard error Win10 Pro 64	bkcell	Windows	1	03-24-2018 04:20 AM
clipboard error freezes workbook	bkcell	Excel	4	06-18-2017 08:00 PM
Do you want to keep your clipboard?	Cam-Bean	Word	0	06-14-2017 02:55 AM
Getting the clipboard value: Dataobject error	arjfca	Word VBA	0	12-04-2014 01:51 AM
Get clipboard Image	janith	Word	2	05-27-2012 07:33 AM