Thread: 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

Sub Createcode()

Range("A1").Select
i = 0
C = 0

Do
If ActiveCell.Offset(0, i).Value = "" Then GoTo A
i = i + 1
Loop

A:
Do
If ActiveCell.Offset(C, 0).Value = "" Then GoTo B
C = C + 1
Loop


B:
i = i - 1
C = C - 1
Ti = 0
 
CD = ""
MsgCode = CD

MsgBox ("Your code has been generated.  Just go to where you want to post it and select Paste.")

End Sub

Added a couple of lines to your code:

Sub Createcode()
Dim DataObj As New MSForms.DataObject

Range("A1").Select
i = 0
C = 0

Do
If ActiveCell.Offset(0, i).Value = "" Then GoTo A
i = i + 1
Loop

A:
Do
If ActiveCell.Offset(C, 0).Value = "" Then GoTo B
C = C + 1
Loop


B:
i = i - 1
C = C - 1
Ti = 0
 
CD = ""
MsgCode = CD

MsgBox ("Your code has been generated.  Just go to where you want to post it and select Paste.")
DataObj.SetText CD
DataObj.PutInClipBoard
End Sub

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.

Sub Createcode()

Range("A1").Select
i = 0
C = 0

Do
If ActiveCell.Offset(0, i).Value = "" Then GoTo A
i = i + 1
Loop

A:
Do
If ActiveCell.Offset(C, 0).Value = "" Then GoTo B
C = C + 1
Loop


B:
i = i - 1
C = C - 1
Ti = 0
 
CD = ""

' Change the cell to one that the user will not be using
Range("AA1") = CD
Range("AA1").Copy

MsgCode = CD

MsgBox ("Your code has been generated.  Just go to where you want to post it and select Paste.")

End Sub

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

""

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.

Sub RemoveQuotes()

  Dim Filename As String
  Dim Filepath As String
  Dim FSO As Object
  Dim Text As String
  Dim TextFile As Object


      Filename = "Table Code.txt"
      Filepath = "C:\Documents and Settings\Janos\My Documents"

          Set FSO = CreateObject("Scripting.FileSystemObject")

          Set TextFile = FSO.OpenTextFile(Filepath & "\" & Filename, 1, False, 0)
              Text = TextFile.ReadAll
          TextFile.Close

          Set TextFile = FSO.OpenTextFile(Filepath & "\" & Filename, 2, False, 0)
              Text = Mid(Text, 2, Len(Text) - 2)
              TextFile.Write Text
          TextFile.Close

End Sub

Probably this code performs the same job:

Sub snb()
 sn=sheets(1).cells(1).currentregion
 for j=1 to ubound(sn)
  c01=c01 & join(application.index(sn,j),"|") & vbcrLf
 next
 
 open "E:\files.txt" for output as 1
  print 1, c01 
 close
End Sub

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