Combine text string with clipboard contents

  • I'm working on a macro that uses the contents of the clipboard. The text string has many fields in quotations, and Excel is stripping the first two quotes when I paste the data into the spreadsheet.


    For example, if the text string in the clipboard is:
    "Sample One" or "Sample Two" or "Sample Three"


    The user starts with this text string in their clipboard, then fires the macro. When the text string hits the spreadsheet (via ActiveSheet.Paste), the value changes to this:
    Sample One or "Sample Two" or "Sample Three" - the first two sets of quotes are stripped.


    My macro uses the quotes to convert this into a table of the three text strings in quotes. I'm wondering if there's a way to get at the contents of the clipboard without pasting it onto the spreadsheet? If so, I could put an apostrophe in front of the text string and make this thing work.


    Thanks in advance,


    James

  • Re: Combine text string with clipboard contents


    To get text from the Clipboard...


    This needs a reference to msForms.DataObject. You can either use the Tools/References menu to go look for it or simply add a userform (if you don't have any in the project already) and remove the userform immediately. If you already have userforms then the reference will exist.


    You need to add error handling - this will throw an error if the Clipboard does not contain any text items...