Save VBA Variable to Clipboard

Hello All.. I'm trying to save a variable to the clipboard so that I can paste it to another area.

Background is that there's an online game I play that you can sell in game items on a forum. The forum can support certain tags to set up a table but trying to code a table for it pretty much makes me go crosseyed or want to drink a fifth of whiskey. So I'v generated some VBA to take the table I make in Excel and turn it into the code for the forum. I've learned after creating this code, that I cannot copy from the msgbox when it displays the code. I have tried various solutions I've found when googling but none of them have worked.

End result shoudl be that it saves the variable Msgcode to the clipboard so I can just jump over to the forum and paste it.... Please let me know what I'm missing.


Thanks in advance.

Vamosj

Please Login or Register  to view this content.

Added a couple of lines to your code:

Please Login or Register  to view this content.

Hello vamosj,

An easier way than using the DataObject is to copy MsgCode to a cell on the worksheet. You can then easily copy it to the clipboard. The changes are in bold text.

Please Login or Register  to view this content.

Thank you both for your reply

Originally Posted by tlafferty

Added a couple of lines to your code:[code]

This is the one I've found using Google but for some reason doesn't work. I get a Compile error: User-Defined type not defined whenever I try and Dim the Dataobject.


Leith - I was thinking about trying it but didn't figure it would work with my chr(13) in the coding but decided to give it a shot... It works. Thanks for your input.

Hello vamosj,

Glad to help a fellow squid.

Back again with oe more quick ? about this. When I go to copy this I'm getting the " at the beginning and end of the code so when I paste it, the " is showing up when I don't want it to. Is there any way to remove this?

Hello Janos,

Do you mean you when you paste it on the Excel worksheet or in Notepad?

When I paste it into notepad

Hello Janos,

I can't reproduce the problem. Can you post an sample Notepad file?

I'll just post it and wrap some code around it... This is a straight ctrl+v paste

Please Login or Register  to view this content.

See how when I paste it there is the " at the beginning and another " at the end. I know this is normal with any text value but I would like to remove it as the quotation marks do show up in the forum when the table is placed. The user now has to go in, locate, and delete the quotation marks.

Hell Janos,

Since I can not reproduce the problem using Ctrl+V, it is possible to remove the beginning and ending quotes within the file using VBA. To do so requires that the Notepad file first be saved. You can then use this macro to remove the quotes. You will need to change the file path and file name to what you are using.

Please Login or Register  to view this content.

Probably this code performs the same job:

Please Login or Register  to view this content.

Not looking to save or open a notepad file. What I'm trying to do is the individual using this starts the macro, the macro takes the table into excel, and converts it to a string of code that can be pasted into a forum. Let me go ahead and upload my file..

I tried pasting it to notepad and for some reason it doesn't have the " show up on it, but when I post it to the forum it does.

To run the macro, go to the table tab and hit ctrl+m

Hello Janos,

Okay, I see what you mean. The quotes being added by the forum are most likely due to HTML code tags that included in the text. Placing the code inside quotes prevents the HTML code from being executed. Nothing you can due about that.

Here is an image of what is happenning if I post it as is... You can see the " in the upper left hand corner. There is another after the table also..


http://imgur.com/tV8gu

so I guess this brings me back to my original question... Is there a way to save the variable to the clipboard other than placing it in a cell and do the ole automated copy and paste routine?

Thanks for sticking with me on this.

Hello Janos,

Does this site allow you to post HTML code? A lot of sites don't for security reasons.

So I went back to tlafferty's thread to try it again.... For some reason this time it worked which is really weird because I added the lines verbatum and I kept getting an object define error... This time it's working like a charm... The only thing I can think of is that it wa getting hung up after I already had an error the first time I tried it.. After closing the program and restarting it (a few days later I might add), it is now running like a champ.... Sometimes I hate computers. Especially when they make you look like the fool..

Well this I think this is fully solved now.... Thanks everyone for your inputs.

Originally Posted by tlafferty

Added a couple of lines to your code:

Please Login or Register  to view this content.

Hello Janos,

When I was a DS in the Navy, I had a rubber mallet in my tool box. Just for situations like this one.

Despite the mallet you probably overlooked my suggestion in post #12, which does everything (reading & writing, no clipboard needed) in 1 go.