Consulting

Results 1 to 11 of 11

Thread: Problem with copying text to the clipboard

  1. #1

    Unhappy Problem with copying text to the clipboard

    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.

  2. #2
    Moderator VBAX Wizard Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    5,133
    Location
    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.
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  3. #3
    Quote Originally Posted by Aussiebear View Post
    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.

  4. #4
    Moderator VBAX Wizard Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    5,133
    Location
    What exactly are you copying?
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  5. #5
    Quote Originally Posted by Aussiebear View Post
    What exactly are you copying?
    I have attached a List in my thread. That's what I want to copy.

  6. #6
    Moderator VBAX Wizard Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    5,133
    Location
    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?
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  7. #7
    Quote Originally Posted by Aussiebear View Post
    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.

  8. #8
    Moderator VBAX Wizard Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    5,133
    Location
    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?
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  9. #9
    Quote Originally Posted by Aussiebear View Post
    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.

  10. #10
    Moderator VBAX Wizard Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    5,133
    Location
    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.


    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  11. #11
    VBAX Newbie DocAElstein's Avatar
    Joined
    Oct 2023
    Location
    Hof, Germany
    Posts
    4
    Location
    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 )
    Attached Files Attached Files
    Last edited by DocAElstein; 10-12-2023 at 10:54 AM. Reason: Too many typos usually for me to mention

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •