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