Problem with copying text to the clipboard

[DocAElstein]

Hi
I feel everybody’s pain, Lol! We all been there,
I think it’s the age old problem – when you know what you want, and what you are doing, almost any explanation you give seems OK and fully understandable, as you understand it yourself, and you get very frustrated when anyone else can’t understand what you are on about.
But the fact is that almost everyone misses things out when they try to explain something to someone, - which makes it difficult for anyone to follow. (I guess that is why being a good teacher is so difficult. The worst teachers are often the ones that know their subject area the best. Sometimes the less clever people make good teachers as they have to explain it in full detail or they lose the thread and confuse themselves, Lol.)

You need to try to make it very clear things that are too obvious to you to want to explain, otherwise we have to assume, and that adds to uncertainties etc. etc..

For example. I guess the code given is to be run from Microsoft Word? If it is someone is probably going to want to kill me for being such an idiot because it’s so obvious? Well it’s not obvious to me. I run coding like this from Excel – sometimes it's useful to have a second instance of Excel open and not visible.
Also this … But it cannot work: … is difficult to understand what is meant by that.


Anyways,
The macro from the first post, errors if I run it from word VBA, but not if it is run from Excel VBA. I think I have one idea why that might be



I can get the list from the excel file, by doing a Ctrl+V, in other words I can the list from the clipboard, for example I can paste it here at vbaexpress like I just did here : http://www.vbaexpress.com/forum/show...l=1#post421370
So I geuss that means I have copied that text to the clipboard before, or else the paste would not have done that. ( Well, actually, that statement about copying text to the clipboard is a bit debatable. Actually I have told the computer about an Excel range I want to get something from. When I come to paste, the Clipboard will decide what its going to do. We humans just have to hope it does what we want. I doubt any human alive knows anymore how the microsoft windows clipboard works, and certainly no one at microsoft seems to know. )

This is how I did it.
I download both the Excel file given by the OP and the word document I upload here, both attached to this post. They can go anywhere as long as both are in the same folder. (Alternatively the Excel file can go to a specific folder, then change my macro version back to like in the original with a specific hardcoded path )

I run the following macro which is in that uploaded Word document
This macro is almost the same as the one from the first post, but I had to change it very slightly as shown in purple

 Sub CopyTextToClipboard()  '  www.vbaexpress.com/forum/showthread.php?71153-Problem-with-copying-text-to-the-clipboard&p=421312&viewfull=1#post421312
Dim xlApp As Object
Dim xlWB As Object
Dim xlWS As Object
Dim Rng As Object ' Variant ' Range
 Set xlApp = CreateObject("Excel.Application")
 'Set xlWB = xlApp.Workbooks.Open("C:\Users\Desktop\List.xlsx")
 Set xlWB = xlApp.Workbooks.Open(ThisDocument.Path & "\List.xlsx")
 Set xlWS = xlWB.Worksheets("LIST")
 Set Rng = xlWS.Range("A2:A126")
 Rng.Copy
 xlWB.Close False
 xlApp.Quit

 Set Rng = Nothing
 Set xlWS = Nothing
 Set xlWB = Nothing
 Set xlApp = Nothing
End Sub

A few other minor changes are not important. The problem I have with the original macro is that word VBA does not know what an Excel Range object is. It knows what a Word Range object is, but it gets pretty upset if I try to Set one to a Excel Range object, - it gives me a type mismatch error. I don’t know much Word VBA, but I expect a Word Range object is a different thing to a Excel Range object, - a quick Google suggests that.
Microsoft seem to annoyingly use often in VBA the same word in coding for different things. Ho hum, that's life,

I don’t know if that takes anyone any further?


Alan

( Edit: I do see now written … This macro is used for a Microsoft Word Document .. if my assumption is correct then better would be to say This macro is run from a Microsoft Word Document )