Problem with copying text to the clipboard

[yanyan9896]

Hi everyone,


I'm new to VBA and I'm facing an issue with a macro that involves copying text from a Microsoft Word document based on data from an Excel file. I would appreciate your help in resolving this problem.


I want the macro to do the following:


1. Browse the specified Excel sheet
2. Find the text according to that specified Excel sheet
3. Copy them accordingly to the clipboard


I've attached the Excel file (which is a list of names) here.
LIST.xlsx

Here's the code I have so far. But it cannot work:

Sub CopyTextToClipboard()


Dim xlApp As Object
Dim xlWB As Object
Dim xlWS As Object
Dim rng As Range


Set xlApp = CreateObject("Excel.Application")
Set xlWB = xlApp.Workbooks.Open("C:\Users\Desktop\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

I would be grateful for any guidance or suggestions to improve this code and make it work as intended. Thank you in advance for your assistance.

[Aussiebear]

Welcome to VBAX yanyan9896. In your code you haven't suggested where it needs to be pasted. Its like getting in a car to go somewhere, you know where you are leaving from but not to where you are headed.

[yanyan9896]

Welcome to VBAX yanyan9896. In your code you haven't suggested where it needs to be pasted. Its like getting in a car to go somewhere, you know where you are leaving from but not to where you are headed.

Thanks for your reply. This macro is used for a Microsoft Word Document. I expect the macro can copy names according to the Excel file (which is the names in the attached List) to the clipboard. Then the macro will paste what copied into a designated Excel sheet.

Since I received no response after I have asked it for many times in different forum by telling the whole story, I decided to just asking how to use macro to copy specific texts according to an Excel file to the clipboard. Then I will try my best to figure out the remaining part. That's why I don't suggest where it needs to be pasted.

I deeply apologize for the inconvenience caused.

[Aussiebear]

What exactly are you copying?

[yanyan9896]

What exactly are you copying?

I have attached a List in my thread. That's what I want to copy.

[Aussiebear]

I understand that you have a list of names in an Excel spreadsheet. What I don't understand yet is how are these names defined in your unnamed word document? Are they in a table, or scattered around the document?

[yanyan9896]

I understand that you have a list of names in an Excel spreadsheet. What I don't understand yet is how are these names defined in your unnamed word document? Are they in a table, or scattered around the document?

Thank you for your reply. The names are scattered around the document.

[Aussiebear]

Okay. Since it can't be that you are copying the names from the document since you already have the names in a List...... what are we trying to copy?

[yanyan9896]

Okay. Since it can't be that you are copying the names from the document since you already have the names in a List...... what are we trying to copy?

Please help me to close this thread. I give up asking for that macro. I have explained clearly many times, and you keep asking and asking. And I didn't receive any useful response.

Again, thanks for your enthusiasm.

[Aussiebear]

I can understand your frustration, but for a moment think of what you are asking from us.

.I'm facing an issue with a macro that involves copying text from a Microsoft Word document based on data from an Excel file.

At no stage have you indicated the name of the word document. Neither did you mention how the "text" is selected other than its based on a range of names from a spreadsheet page called "List". These names are scattered throughout the document.

I have attached a List in my thread. That's what I want to copy.

Since you already have a list of Names in a range on a worksheet why do you want to copy them from a word document to a worksheet given that you already have the ones in a list?

Since you are requesting something that is quite circular (from the information you have currently provided), it's very difficult to see where this can be solved. If as you have indicated that you have asked this same issue in another forum and you received no assistance, could it be that you may not be explaining the issue in a manner that gains assistance. I am inclined to agree that this thread probably needs to be closed but I will give you one more opportunity to describe the issue in full. When replying, please post an example of the word document, tell us how you determine if, how, and why the names get selected to the clipboard. Maybe then we will understand what it is that you would like done.

[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 )