Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Word > Word VBA

Reply
 
LinkBack Thread Tools Display Modes
  #1  
Old 07-15-2017, 05:50 AM
StephenRay StephenRay is offline Windows 7 64bit Office 2010 64bit
Novice
 
Join Date: Jan 2012
Location: Overland Park, Kansas
Posts: 12
StephenRay is on a distinguished road
Default Error with Clipboard - Getting Spaces Out of Clipboard

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
Reply With Quote
  #2  
Old 07-15-2017, 06:29 AM
gmaxey gmaxey is offline Windows 7 32bit Office 2016
Word MVP 2003-2009
 
Join Date: May 2010
Location: Marble, NC
Posts: 723
gmaxey will become famous soon enough
Default

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
__________________
Greg Maxey
Please visit my web site at http://www.gregmaxey.com/
Reply With Quote
  #3  
Old 07-15-2017, 10:11 AM
StephenRay StephenRay is offline Windows 7 64bit Office 2010 64bit
Novice
 
Join Date: Jan 2012
Location: Overland Park, Kansas
Posts: 12
StephenRay is on a distinguished road
Default

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!
Reply With Quote
  #4  
Old 07-15-2017, 07:09 PM
gmaxey gmaxey is offline Windows 7 32bit Office 2016
Word MVP 2003-2009
 
Join Date: May 2010
Location: Marble, NC
Posts: 723
gmaxey will become famous soon enough
Default

Actually I don't suppose you do if you are simply going to paste into the same document.
__________________
Greg Maxey
Please visit my web site at http://www.gregmaxey.com/
Reply With Quote
  #5  
Old 07-16-2017, 04:29 AM
StephenRay StephenRay is offline Windows 7 64bit Office 2010 64bit
Novice
 
Join Date: Jan 2012
Location: Overland Park, Kansas
Posts: 12
StephenRay is on a distinguished road
Default

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.
Reply With Quote
  #6  
Old 07-16-2017, 06:49 AM
gmaxey gmaxey is offline Windows 7 32bit Office 2016
Word MVP 2003-2009
 
Join Date: May 2010
Location: Marble, NC
Posts: 723
gmaxey will become famous soon enough
Default

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.
__________________
Greg Maxey
Please visit my web site at http://www.gregmaxey.com/
Reply With Quote
  #7  
Old 07-16-2017, 08:42 AM
StephenRay StephenRay is offline Windows 7 64bit Office 2010 64bit
Novice
 
Join Date: Jan 2012
Location: Overland Park, Kansas
Posts: 12
StephenRay is on a distinguished road
Default

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!
Reply With Quote
  #8  
Old 07-17-2017, 01:21 AM
gmayor's Avatar
gmayor gmayor is offline Windows 10 Office 2016
Expert
 
Join Date: Aug 2014
Posts: 2,092
gmayor is a jewel in the roughgmayor is a jewel in the roughgmayor is a jewel in the roughgmayor is a jewel in the rough
Default

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.
__________________
Graham Mayor - MS MVP (Word)
Visit my web site for more programming tips and ready made processes www.gmayor.com
Reply With Quote
  #9  
Old 07-17-2017, 03:57 AM
gmaxey gmaxey is offline Windows 7 32bit Office 2016
Word MVP 2003-2009
 
Join Date: May 2010
Location: Marble, NC
Posts: 723
gmaxey will become famous soon enough
Default

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.
__________________
Greg Maxey
Please visit my web site at http://www.gregmaxey.com/
Reply With Quote
  #10  
Old 07-17-2017, 05:24 AM
StephenRay StephenRay is offline Windows 7 64bit Office 2010 64bit
Novice
 
Join Date: Jan 2012
Location: Overland Park, Kansas
Posts: 12
StephenRay is on a distinguished road
Default

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
Reply With Quote
  #11  
Old 07-17-2017, 05:26 AM
StephenRay StephenRay is offline Windows 7 64bit Office 2010 64bit
Novice
 
Join Date: Jan 2012
Location: Overland Park, Kansas
Posts: 12
StephenRay is on a distinguished road
Default

GMAXEY, Thank you for your help. I am pasting in your code to my Macros this morning. This saves me lots of time!
Reply With Quote
  #12  
Old 07-17-2017, 12:49 PM
gmaxey gmaxey is offline Windows 7 32bit Office 2016
Word MVP 2003-2009
 
Join Date: May 2010
Location: Marble, NC
Posts: 723
gmaxey will become famous soon enough
Default

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.
__________________
Greg Maxey
Please visit my web site at http://www.gregmaxey.com/
Reply With Quote
  #13  
Old 07-17-2017, 09:46 PM
gmayor's Avatar
gmayor gmayor is offline Windows 10 Office 2016
Expert
 
Join Date: Aug 2014
Posts: 2,092
gmayor is a jewel in the roughgmayor is a jewel in the roughgmayor is a jewel in the roughgmayor is a jewel in the rough
Default

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
__________________
Graham Mayor - MS MVP (Word)
Visit my web site for more programming tips and ready made processes www.gmayor.com
Reply With Quote
  #14  
Old 07-18-2017, 07:05 AM
StephenRay StephenRay is offline Windows 7 64bit Office 2010 64bit
Novice
 
Join Date: Jan 2012
Location: Overland Park, Kansas
Posts: 12
StephenRay is on a distinguished road
Default

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.
Reply With Quote
Reply
Please reply to this thread with any new information or opinions.

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
clipboard error freezes workbook bkcell Excel 4 06-18-2017 08:00 PM
Clipboard error Win10 Pro 64 bkcell Windows 0 06-15-2017 03:08 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


All times are GMT -7. The time now is 01:31 AM.


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc.
MSOfficeForums.com is not affiliated with Microsoft