Get text from Win Clipboard into VBA variable

P

Paul Schrum

VBA in Excel 2007

I have data on the clipboard of a specific format:

Start: (') (915855.639280, 638485.145786)
End: (') (917162.295718, 637714.747829)

I would like to bring this from the windows clipboard into a variable
in which I can parse it (to extract and use the coordinates). I can't
find this when I search groups and whatnot. Can anyone help me with
this?

- Paul
Schrum
Raleigh, NC
 
P

Paul Schrum

Thanks for your response. When I tried the code on that page I got

Compile error:
user-defined type not defined

I think this means I have to set a Reference to a certain library, but
I can't figure out which one. Do you know how I get around this?
 
P

Paul Schrum

You may disregard my previous post (Thanks for . . . )

I had not read down in that web page. I see a module is available for
download that provides clipboard access functions. I can use those.

- Paul
 
D

Dave Peterson

Read the first paragraph on Chip's site.



Paul said:
Thanks for your response. When I tried the code on that page I got

Compile error:
user-defined type not defined

I think this means I have to set a Reference to a certain library, but
I can't figure out which one. Do you know how I get around this?
Click to expand...
 
C

Chip Pearson

It isn't clear whether you need help with the clipboard piece of the
pie or the text parsing piece. The code below does both. You'll need a
reference to the Forms library, where the DataObject is defined. In
VBA, go to the Tools menu and choose References. There, scroll down to
Microsoft Forms 2.0 Object Library and check that entry. Then use code
like the following:

Sub AAA()

Dim DataObj As MSForms.DataObject
Dim S As String
Dim T As String
Dim N As Long
Dim M As Long
Dim LineContent() As String
Dim Start1 As Double
Dim End1 As Double
Dim Start2 As Double
Dim End2 As Double
Dim Lines() As String


Set DataObj = New MSForms.DataObject

'>>> BEGIN TEST
' This is just a test to put something in the clipboard.
' Omit from final code.
T = "Start: (') (915855.639280, 638485.145786)" & vbCrLf & _
"End: (') (917162.295718, 637714.747829)"
DataObj.SetText T
DataObj.PutInClipboard
'<<< END TEST

''''''''''''''''''''''''''''''''''''''''''
' Get text from clipboard into variable S.
DataObj.GetFromClipboard
S = DataObj.GetText
' If all you need help on is the clipboard piece,
' the code ends here. The clipboard content is in
' the variable S.
''''''''''''''''''''''''''''''''''''''''''
''''''''''''''''''''''''''''''''''''''''''


''''''''''''''''''''''''''''''''''''''''''
' PARSING THE CONTENT OF THE DATA
''''''''''''''''''''''''''''''''''''''''''

' first single space everything
N = InStr(1, S, Space(2), vbBinaryCompare)
Do Until N = 0
S = Replace(S, Space(2), Space(1))
N = InStr(1, S, Space(2), vbBinaryCompare)
Loop

' break apart into lines
Lines = Split(S, vbCrLf)

' N = position of second open paren
N = InStr(InStr(1, Lines(0), "(", vbBinaryCompare) + 1, Lines(0), "(")
' get text within parentheses
S = Mid(Lines(0), N + 1, Len(Lines(0)) - N - 1)
LineContent = Split(S, ",")
' Start1 and Start2 are the values in the first line
' of text from the clipboard.
Start1 = CDbl(LineContent(0))
Start2 = CDbl(LineContent(1))


' this does the same as above but parse the second line of
' text in the clipboard.
N = InStr(InStr(1, Lines(1), "(", vbBinaryCompare) + 1, Lines(1), "(")
S = Mid(Lines(1), N + 1, Len(Lines(1)) - N - 1)
LineContent = Split(S, ",")
' End1 and End2 are the values in the second line of text
' in the clipboard.
End1 = CDbl(LineContent(0))
End2 = CDbl(LineContent(1))

' display the results
Debug.Print "Starts:", Start1, Start2
Debug.Print "Ends:", End1, End2

End Sub


You can find information and code for working with the clipboard at
www.cpearson.com/Excel/Clipboard.aspx . The rest of the code is just a
careful application of the standard text functions.

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]
 
R

Ron Rosenfeld

VBA in Excel 2007

I have data on the clipboard of a specific format:

Start: (') (915855.639280, 638485.145786)
End: (') (917162.295718, 637714.747829)

I would like to bring this from the windows clipboard into a variable
in which I can parse it (to extract and use the coordinates). I can't
find this when I search groups and whatnot. Can anyone help me with
this?

- Paul
Schrum
Raleigh, NC
Click to expand...

VBA does not have built-in support for handling the Windows Clipboard. But you
can do that using the Microsoft Forms library.

Here's an example that, if you copy your Start and End lines to the clipboard,
will place the for values into four string variables.

Note the first lines regarding setting various references in the
tools/references menu.

See also http://www.cpearson.com/Excel/Clipboard.aspx for more complete
information:

==================================================
'Set References (Tools/References)
' Microsoft Forms 2.0 Reference Library
' Microsoft VBScript Regular Expressions 5.5
Option Explicit
Sub ClipboardContents()
Dim CC As DataObject
Dim s As String
Dim s1 As String, s2 As String
Dim e1 As String, e2 As String
Dim re As RegExp, mc As MatchCollection
Const sPat As String = "\b\d+(\.\d+)?\b"

Set CC = New DataObject
CC.GetFromClipboard
s = CC.GetText

Set re = New RegExp
re.Global = True
re.Pattern = sPat

Set mc = re.Execute(s)
If mc.Count = 4 Then
s1 = mc(0)
s2 = mc(1)
e1 = mc(2)
e2 = mc(3)
Else
MsgBox ("Invalid Data on Clipboard")
End If

Debug.Print s1, s2
Debug.Print e1, e2

End Sub
========================================
--ron
 
P

Paul Schrum

Chip,

I am sorry that I was vague. Yes, I was specifically looking for a
way to get data from the clipboard. But looking at your code for
parsing the string confirmed what I thought I would need to do.

Thanks.

- Paul

It isn't clear whether you need help with the clipboard piece of the
pie or the text parsing piece. The code below does both. You'll need a
reference to the Forms library, where the DataObject is defined. In
VBA, go to the Tools menu and choose References. There, scroll down to
Microsoft Forms 2.0 Object Library and check that entry. Then use code
like the following:

Sub AAA()

Dim DataObj As MSForms.DataObject
Dim S As String
Dim T As String
Dim N As Long
Dim M As Long
Dim LineContent() As String
Dim Start1 As Double
Dim End1 As Double
Dim Start2 As Double
Dim End2 As Double
Dim Lines() As String

Set DataObj = New MSForms.DataObject

'>>> BEGIN TEST
' This is just a test to put something in the clipboard.
' Omit from final code.
T = "Start:  (') (915855.639280, 638485.145786)" & vbCrLf & _
        "End:    (') (917162.295718, 637714.747829)"
DataObj.SetText T
DataObj.PutInClipboard
'<<< END TEST

''''''''''''''''''''''''''''''''''''''''''
' Get text from clipboard into variable S.
DataObj.GetFromClipboard
S = DataObj.GetText
' If all you need help on is the clipboard piece,
' the code ends here. The clipboard content is in
' the variable S.
''''''''''''''''''''''''''''''''''''''''''
''''''''''''''''''''''''''''''''''''''''''

''''''''''''''''''''''''''''''''''''''''''
' PARSING THE CONTENT OF THE DATA
''''''''''''''''''''''''''''''''''''''''''

' first single space everything
N = InStr(1, S, Space(2), vbBinaryCompare)
Do Until N = 0
    S = Replace(S, Space(2), Space(1))
    N = InStr(1, S, Space(2), vbBinaryCompare)
Loop

' break apart into lines
Lines = Split(S, vbCrLf)

' N = position of second open paren
N = InStr(InStr(1, Lines(0), "(", vbBinaryCompare) + 1, Lines(0), "(")
' get text within parentheses
S = Mid(Lines(0), N + 1, Len(Lines(0)) - N - 1)
LineContent = Split(S, ",")
' Start1 and Start2 are the values in the first line
' of text from the clipboard.
Start1 = CDbl(LineContent(0))
Start2 = CDbl(LineContent(1))

' this does the same as above but parse the second line of
' text in the clipboard.
N = InStr(InStr(1, Lines(1), "(", vbBinaryCompare) + 1, Lines(1), "(")
S = Mid(Lines(1), N + 1, Len(Lines(1)) - N - 1)
LineContent = Split(S, ",")
' End1 and End2 are the values in the second line of text
' in the clipboard.
End1 = CDbl(LineContent(0))
End2 = CDbl(LineContent(1))

' display the results
Debug.Print "Starts:", Start1, Start2
Debug.Print "Ends:", End1, End2

End Sub

You can find information and code for working with the clipboard atwww.cpearson.com/Excel/Clipboard.aspx. The rest of the code is just a
careful application of the standard text functions.

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLCwww.cpearson.com
[email on web site]

VBA in Excel 2007
Click to expand...
I have data on the clipboard of a specific format:
Click to expand...
Start:  (') (915855.639280, 638485.145786)
End:    (') (917162.295718, 637714.747829)
Click to expand...
I would like to bring this from the windows clipboard into a variable
in which I can parse it (to extract and use the coordinates).  I can't
find this when I search groups and whatnot.  Can anyone help me with
this?
Click to expand...
- Paul
Schrum
Raleigh, NC- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...
 
P

Paul Schrum

Yes, I did find it. It was in the link of the first response to my
original posting. I simply read that web page too fast and did not
notice some of the key information there.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

VBA equivalent for XLL? 1
Pasting a cell into bookmarked table from excel vba 1
Display bug in Word: tables disappearing on copy and paste of text with bullet points into table 2
Get text from clipboard to string? 8
Putting a variable value into the Clipboard 2
text from clipboard -- strange behaviour 0
Copy from windows clipboard in VBA 3
Convert text from clipboard and put in cells... 2

Top