+ Reply to Thread
Results 1 to 13 of 13

Get contents of string variable onto the clipboard

  1. #1
    Forum Contributor terrypin's Avatar
    Join Date
    01-06-2010
    Location
    East Grinstead, UK
    MS-Off Ver
    MS Office 365
    Posts
    533

    Get contents of string variable onto the clipboard

    I expected this to be very simple but so far I've not found a way to get the contents of a string variable onto the clipboard, ready for pasting externally. Hopefully my code extract clearly explains:

    Please Login or Register  to view this content.
    A bit later I found what seemed a solution and after minor mods added it to the above:

    Please Login or Register  to view this content.
    But although the message show the correct string, when I paste into my text editor I just get '??'.
    Last edited by terrypin; 01-11-2020 at 01:45 PM.
    Terry, East Grinstead, UK
    Excel 365, Win 10 Pro, i7 PC, 4.0 GHz

  2. #2
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Get contents of string variable onto the clipboard

    Hello terrypin,

    Which program are using for your text editing?
    Sincerely,
    Leith Ross

    Remember To Do the Following....

    1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.
    2. Thank those who have helped you by clicking the Star below the post.
    3. Please mark your post [SOLVED] if it has been answered satisfactorily.


    Old Scottish Proverb...
    Luathaid gu deanamh maille! (Rushing causes delays!)

  3. #3
    Forum Contributor terrypin's Avatar
    Join Date
    01-06-2010
    Location
    East Grinstead, UK
    MS-Off Ver
    MS Office 365
    Posts
    533

    Re: Get contents of string variable onto the clipboard

    Hi Leith,

    Whichever program I use it fails. With my regular editor TextPad it displays those two question marks. Notepad displays nothing. And its intended app, Everything, also displays nothing. Yet if the paste is made to any of these directly after
    Please Login or Register  to view this content.
    that unmodified string is correct.

    Until I find some valid code I'll resort to using debug.Print and copy/paste manually from there.

    It surprises me that such a frequently needed operation as 'copy to clipboard' (accomplished with a simple Ctrl+C on the keyboard) apparently doesn't have a simple VBA equivalent of a statement or two!

  4. #4
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Get contents of string variable onto the clipboard

    Hello terrypin,

    The lack of a generic copy command in VBA is annoying. Good news is there is workaround. Better news is you don't have to figure it out!

    It seems absurd that it takes some much to do what seems like a simple operation. The truth is a copy operation is quite complicated. This only the code for plain text. The code below with work on 32 bit and 64 bit versions of Windows and Office. The macro you will call to copy the text is CopyTextToClipboard

    Add a new VBA Module to your workbook. Copy and paste the code below into the new module.
    Please Login or Register  to view this content.
    Example of Calling the Macro
    Please Login or Register  to view this content.

  5. #5
    Forum Contributor terrypin's Avatar
    Join Date
    01-06-2010
    Location
    East Grinstead, UK
    MS-Off Ver
    MS Office 365
    Posts
    533

    Re: Get contents of string variable onto the clipboard

    Hi Leith,

    Many thanks, finally got that working!

    But am I right that your 96 (!) lines of code must be pasted into the same module as any macro that needs to use it? (With no Sub/End Sub). Not only that, but it must be at the very top of that module?

    I initially pasted it into a fresh module as you said but whatever test macro I tried located anywhere else I got this error: "Compile error: Expected variable or procedure, not module"

    As a novice I may be making some elementary mistake here! For instance, I assume that running it as usual with F5 is OK? At one stage during my experiments, the macro list popped up, so maybe that's another clue.

    Anyway, as you said, it's incredible that nearly a hundred lines of code is needed for a simple paste!
    Last edited by terrypin; 01-12-2020 at 12:28 PM.

  6. #6
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Get contents of string variable onto the clipboard

    Hello terrypin,

    To answer your first question, yes. Here is the reason why. The API code statements at the top of the module, the ones starting with Private Declare, are placed between Conditional Compilation statements: VBA7, Win64, #If, #If Else, #End If, and loaded based on your Windows and Office versions. Think of these as Dim statements for the API calls. These must be compiled before any of the other code that uses these calls will be compiled or an error will occur. So these statements must appear first at the top of the module.

    The only macro in the module code that is Public is CopyTextToClipboard. This can be called from anywhere in your code. All other calls are Private and can only be executed in the module where they appear.

  7. #7
    Forum Contributor terrypin's Avatar
    Join Date
    01-06-2010
    Location
    East Grinstead, UK
    MS-Off Ver
    MS Office 365
    Posts
    533

    Re: Get contents of string variable onto the clipboard

    Thanks Leith, understood.

    I suppose once I've pasted it in I can cheerfully forget about how complex it is!

    I'd welcome any comments you have about that first 'solution' I tried, which I got from here:
    https://stackoverflow.com/questions/...vba-excel-2013

    Does that much simpler code fail for you or others too?

  8. #8
    Valued Forum Contributor Haluk's Avatar
    Join Date
    02-14-2019
    Location
    Turkiye
    MS-Off Ver
    2010 - 64 Bit on Windows-11 (22 H2) 64 Bit
    Posts
    1,133

    Re: Get contents of string variable onto the clipboard

    I am not sure if I understood the problem, but the following first code copies a string into the clipboard and the second code gets the content of the copied string.

    Note that; Microsoft Forms 2.0 Object Library reference should be added to the project.

    Please Login or Register  to view this content.

  9. #9
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Get contents of string variable onto the clipboard

    Hello terrypin,

    I suppose once I've pasted it in I can cheerfully forget about how complex it is!
    You exactly correct about that.

  10. #10
    Forum Contributor terrypin's Avatar
    Join Date
    01-06-2010
    Location
    East Grinstead, UK
    MS-Off Ver
    MS Office 365
    Posts
    533

    Re: Get contents of string variable onto the clipboard

    Thanks Haluk, but I needed to paste it externally. See my initial post.

    Leigh’s code has now solved that, although as you see it’s astonishingly complex. Due, I gather, to some issue with Win 20. That’s presumably why the simpler version I’d been trying did not work.

  11. #11
    Valued Forum Contributor Haluk's Avatar
    Join Date
    02-14-2019
    Location
    Turkiye
    MS-Off Ver
    2010 - 64 Bit on Windows-11 (22 H2) 64 Bit
    Posts
    1,133

    Re: Get contents of string variable onto the clipboard

    The first code copies the string and then you can paste the copied string by Ctrl+V to anywhere...

    Second code is just a demo...

    Isn't this what you need?

    .

  12. #12
    Forum Contributor terrypin's Avatar
    Join Date
    01-06-2010
    Location
    East Grinstead, UK
    MS-Off Ver
    MS Office 365
    Posts
    533

    Re: Get contents of string variable onto the clipboard

    Hi Haluk,

    Yes and no! The first of your short code examples is indeed exactly what I want. But it doesn't work.

    It's the same method I described in my opening post. I just tried it again (with your unaltered code) but as before nothing gets pasted externally. And as I said in post #10 it doesn't work in Win 10 because of some obscure bug.

    I think this was the same in Win 8 so it's a pity that Microsoft have not fixed it by now. See more details here:
    https://wellsr.com/vba/2015/tutorial...d-paste-clear/

  13. #13
    Forum Contributor terrypin's Avatar
    Join Date
    01-06-2010
    Location
    East Grinstead, UK
    MS-Off Ver
    MS Office 365
    Posts
    533

    Re: Get contents of string variable onto the clipboard

    I also found this very simple method, although it has the snag shown:

    Please Login or Register  to view this content.
    My source was here:
    https://stackoverflow.com/questions/...g-to-clipboard

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Paste clipboard contents
    By enuenu in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-12-2018, 08:34 AM
  2. [SOLVED] How to copy the contents of a variable to clipboard?
    By RustyNail in forum Excel General
    Replies: 2
    Last Post: 03-29-2018, 02:55 PM
  3. Replies: 3
    Last Post: 05-15-2015, 07:27 PM
  4. [SOLVED] Listbox Contents to Clipboard
    By mikegs1 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-01-2014, 07:56 PM
  5. Clear sheet contents but retain Clipboard contents
    By nebb in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-25-2007, 01:42 PM
  6. [SOLVED] Keep contents in clipboard
    By Kenny in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-20-2005, 04:05 PM
  7. VBA: Answer YES to Contents of Clipboard
    By ajocius in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-23-2005, 10:50 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1