Solved

Issue with the PasteSpecial method for Selection object

Posted on 2008-06-20
18
1,023 Views
Last Modified: 2010-04-21
Hello all, I want to write a macro in VBA to do the following:
A user has two MS Word documents open with text in both.  The user first copies information from document one onto the clipboard.  Then, the user goes to document two and places his cursor in a preferred location.  Upon running the macro, the information will paste into the second document as a hyperlink back to the first document where the original information was copied.  

I started with the attached code.  It only works some of the time.  Other times, I just get errors saying the clipboard is invalid or empty.

Any help is greatly appreciated!

Thanks.  


With Selection

.PasteSpecial Link:=True, DataType:=wdPasteHyperlink

End with

Open in new window

0
Comment
Question by:jpo645
  • 10
  • 8
18 Comments
 
LVL 37

Expert Comment

by:Joanne M. Orzech
ID: 21985333
Well, it sounds like there may be a problem in the copy process.  How is the copy being done?  Is there any way to automate the copy process as well, such as:

    Selection.Copy
    ActiveWindow.Next.Activate
    Selection.PasteSpecial Link:=True, DataType:=wdPasteHyperlink, Placement:= _
        wdInLine, DisplayAsIcon:=False


Your code looks and works fine for me.

0
 

Author Comment

by:jpo645
ID: 22058594
It only works half the time for me.  Do you know why that is?
0
 
LVL 37

Expert Comment

by:Joanne M. Orzech
ID: 22058733
jpo645

My first thought is that the copy process is not being correctly done... how is the copy process being performed?  Ctrl-C?  Edit, Copy?  Right-click Copy?  And how is the text to be copied being selected?  With the mouse?  Keystrokes?  Please advise.
0
 

Author Comment

by:jpo645
ID: 22088890
For the most part, the copying is being done by use of the keyboard shortcut Ctrl+C.  It's weird: I had something that was nearly working flawlessly yesterday, and I come back to it today, and it's giving the errors again.  I don't understand.    
0
 
LVL 37

Expert Comment

by:Joanne M. Orzech
ID: 22088978
Is this only one user?

Also, search for all instances of normal.dot on the hard drive and rename them.

Also, it may be that a third-party add-in is causing the problems.  I've asked the other MVPs and they have advised that some other products can "hijack" the clipboard.

If you want to test this theory, you can remove all files from the startup folder, and see if the problem continues.  Then you can put them back into the startup folder one by one until you find the culprit.

You also say that "you come back today and it's giving errors again"... were there updates, changes to the network overnight that may affect it?

Please keep us posted.
0
 

Author Comment

by:jpo645
ID: 22115093
This is a computer on an Air Force base -- it gets updated all the time and I have little control over that.  Although these updates may be causing the issue, it seems unlikely.  The macro I'm trying to make is  actually a recreation of an earlier macro from our old word 2003 toolbars; and, if you use those old toolbars in Word 2007 (though, it looks like crap), the macro works perfectly.  But when I try to port it over to 2007 (namely, by renaming the macro as an IRibbonControl) then I get the clipboard problem.  

So, given this porting issue, I decided I'd recreate it; the first day it was working fine, but I come back the next day (after rebooting the computer and whatnot) and clipboard problem returned.  If I make a new macro with my code, it will generally work until I flip my computer off for the night.  This makes almost no sense.

I will try to see if there are things in the startup folder that are causing the issue.  

I can change the wdPasteHyperlink to any of the other possible constants and it works fine.  It really doesn't like pasting hyperlinks!
0
 
LVL 37

Expert Comment

by:Joanne M. Orzech
ID: 22132136
Hmmm - very odd indeed.  You're right--rebooting shouldn't affect your clipboard or macro functionality at all.... other than to clear the clipboard.

So does the PC have both Office 2003 and Office 2007 loaded on it?
0
 
LVL 37

Expert Comment

by:Joanne M. Orzech
ID: 22132538
The only other thing I can find is that it says the document you are copying from MUST be saved before you can perform this task.  Is that the case in all instances?
0
 
LVL 37

Expert Comment

by:Joanne M. Orzech
ID: 22182651
You know, I've installed Office 2007 and ever since, I find that every now and then, my clipboard is empty, even though I JUST copied something.... I'm going to report it to Microsoft and ask them to look into it.  This appears to be what's happening to you as well.

JOrzech
MS Word MVP
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 

Author Comment

by:jpo645
ID: 22242791
It happens consistently though.  Very weird.
0
 
LVL 37

Expert Comment

by:Joanne M. Orzech
ID: 22262514
But you said:

"It only works some of the time.  Other times, I just get errors saying the clipboard is invalid or empty."

So does it happen EVERY time or just sometimes?


0
 

Author Comment

by:jpo645
ID: 22263011
It works a small minority of the time.  If I take the code and start from scratch, it will most likely work the first few times I run the pastespecial command.  However, if I close the VB session and exit Word, most likely, it will stop working thereafter.  Also, if I reboot the computer (which I always do at the end of the day, per our work policy) it will most assuredly stop working.  Sometimes, when I play around with the code, I can get it to work haphazardlybut not consistently: in fact, the only thing Ive found to be consistent is that it doesnt work.  But Im taking code almost directly from another Macro that does work (although, not in the way I would like it to), so I'm still really confused.  I will post the macro that does work when I geta hold of it.  
0
 
LVL 37

Expert Comment

by:Joanne M. Orzech
ID: 22263520
OK jpo

How are you making the selection?  Perhaps that's the issue.... because the code you posted is just to paste the text as a link.... what is the code that does the copying and how is the selection made?
0
 

Author Comment

by:jpo645
ID: 22265602
So the intended macro is supposed to make a circular refernce between two documents.  

You have two documents open.  In document 1, the user selects the text and copies it.  Typically, I select the text with my mouse and do a CTRL + C.  Then the user goes to the destination document and runs the macro in the spot where they want the link to go.  The macro sets up a bookmark in the destination document for the original document to link to, then goes back, using hyperlink.follow, and sets up a bookmark for the destination document to link to.  

Recently, I upgraded all our old macros to work for Office 2007 by allowing the subs to accept IRibbonControls and placing them as buttons on a custom tab. From the onset of this change, the macro stopped working.  And it stopped working specifically at the pastespecial method, posted above.   When I tried to rewrite the macro from scratch, I started simply with the pastespecial method to test.  And just the method by itself, without any other code, gives me the same error.  Which is why it is the only thing I posted.

That said, if you can think of another way to do this (and I'm sure you can) without even using pastespecial I'm certainly open to any ideas.  

Thanks for your help and patience.  
0
 

Author Comment

by:jpo645
ID: 22265648
So I think I solved part of the issue.  The documents I test this on are usually new documents that have not been saved.  As such, Word didn't know what to link to.  However, I am working from home now, and will see if the macro works tomorrow morning for this reason.
0
 
LVL 37

Accepted Solution

by:
Joanne M. Orzech earned 500 total points
ID: 22268474
Ok - that's one of the solutions I mentioned above...

"The only other thing I can find is that it says the document you are copying from MUST be saved before you can perform this task.  Is that the case in all instances?"

Keep me posted.
0
 

Author Closing Comment

by:jpo645
ID: 31469232
Sorry for the delay in my closing this, I figured it out thanks to your help!
0
 
LVL 37

Expert Comment

by:Joanne M. Orzech
ID: 22978310
So what was the problem???

Anyway, I'm very glad you got it resolved!  Thank you.
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
word 2013 3 60
Using columns on multiple pages on MS Word. 23 37
Word / Printing 8 51
recent docs in word and excel 3 15
Do you ever need to create a 20 page Word document for some testing purpose? Are you tired of copying & pasting old boring "lorem ipsum" text over and over again, increasing font size and line space in order to make the document 20+ pages long? Look…
It is often necessary in this forum and others to illustrate Word fields as text with the field delimiters replaced with the curly brackets that the delimiters resemble when field codes are being displayed on the document. This means that the text c…
This video walks the viewer through the process of creating envelopes and labels, with multiple names and addresses. Navigate to the “Start Mail Merge” button in the Mailings tab: Follow the step-by-step process until asked to find the address doc…
This video shows where to find the word count, how to display it, and what it breaks down to in Microsoft Word.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now