Get text from Win Clipboard into VBA variable
 

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

Get text from Win Clipboard into VBA variable
 

http://www.cpearson.com/excel/clipboard.aspx
--
Gary''s Student - gsnu201001


"Paul Schrum" wrote:

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
.

Get text from Win Clipboard into VBA variable
 

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?


On Feb 11, 3:57*pm, Gary''s Student
wrote:
http://www.cpearson.com/excel/clipboard.aspx
--
Gary''s Student - gsnu201001



"Paul Schrum" wrote:
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
.- Hide quoted text -

- Show quoted text -

Get text from Win Clipboard into VBA variable
 

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

On Feb 11, 3:57*pm, Gary''s Student
wrote:
http://www.cpearson.com/excel/clipboard.aspx
--
Gary''s Student - gsnu201001



"Paul Schrum" wrote:
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
.- Hide quoted text -

- Show quoted text -

Get text from Win Clipboard into VBA variable
 

Read the first paragraph on Chip's site.



Paul Schrum wrote:

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?

On Feb 11, 3:57 pm, Gary''s Student
wrote:
http://www.cpearson.com/excel/clipboard.aspx
--
Gary''s Student - gsnu201001



"Paul Schrum" wrote:
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
.- Hide quoted text -

- Show quoted text -

--

Dave Peterson

Get text from Win Clipboard into VBA variable
 

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]






On Thu, 11 Feb 2010 11:46:14 -0800 (PST), Paul Schrum
wrote:

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

Get text from Win Clipboard into VBA variable
 

On Thu, 11 Feb 2010 11:46:14 -0800 (PST), Paul Schrum
wrote:

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

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

Get text from Win Clipboard into VBA variable
 

Thank you. I do not have that Library.

On Feb 11, 4:23*pm, Dave Peterson wrote:
Read the first paragraph on Chip's site.





Paul Schrum wrote:

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?

On Feb 11, 3:57 pm, Gary''s Student
wrote:
http://www.cpearson.com/excel/clipboard.aspx
--
Gary''s Student - gsnu201001

"Paul Schrum" wrote:
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
.- Hide quoted text -

- Show quoted text -

--

Dave Peterson- Hide quoted text -

- Show quoted text -

Get text from Win Clipboard into VBA variable
 

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

On Feb 11, 4:27*pm, Chip Pearson wrote:
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]

On Thu, 11 Feb 2010 11:46:14 -0800 (PST), Paul Schrum



wrote:
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- Hide quoted text -

- Show quoted text -

Get text from Win Clipboard into VBA variable
 

Did you find it?

I'm guessing that you did.

Paul Schrum wrote:

Thank you. I do not have that Library.

On Feb 11, 4:23 pm, Dave Peterson wrote:
Read the first paragraph on Chip's site.





Paul Schrum wrote:

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?

On Feb 11, 3:57 pm, Gary''s Student
wrote:
http://www.cpearson.com/excel/clipboard.aspx
--
Gary''s Student - gsnu201001

"Paul Schrum" wrote:
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
.- Hide quoted text -

- Show quoted text -

--

Dave Peterson- Hide quoted text -

- Show quoted text -

--

Dave Peterson

Get text from Win Clipboard into VBA variable
 

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.

On Feb 11, 6:32*pm, Dave Peterson wrote:
Did you find it?

I'm guessing that you did.





Paul Schrum wrote:

Thank you. *I do not have that Library.

On Feb 11, 4:23 pm, Dave Peterson wrote:
Read the first paragraph on Chip's site.

Paul Schrum wrote:

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?

On Feb 11, 3:57 pm, Gary''s Student
wrote:
http://www.cpearson.com/excel/clipboard.aspx
--
Gary''s Student - gsnu201001

"Paul Schrum" wrote:
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
.- Hide quoted text -

- Show quoted text -

--

Dave Peterson- Hide quoted text -

- Show quoted text -

--

Dave Peterson