Clipboard manipulation

[PPL]

Has anyone any ideas please on how to paste the contents of the clipboard
one character at a time into an excel sheet row by row
I'm looking for a way to count the character positions of selected text?

TIA

Phil

 

[Jim Cone]

Phil,
If you want to just find the character position then something like this...
'--
Sub WhereAreThey()
Dim strMyText As String
Dim strNeed As String
Dim N As Long

strMyText = Selection.Cells(1, 1)
strNeed = "abcd"

For N = 1 To Len(strMyText)
If InStr(1, strNeed, Mid$(strMyText, N, 1)) > 0 Then
MsgBox Mid$(strMyText, N, 1) & " at position " & N
End If
Next
End Sub
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)


"PPL"
wrote in message
Has anyone any ideas please on how to paste the contents of the clipboard
one character at a time into an excel sheet row by row
I'm looking for a way to count the character positions of selected text?

TIA
Phil

 

[Rick Rothstein \(MVP - VB\)]

Explain what you are trying to ultimately accomplish in more detail (that
is, what do you want to see or have at the end of the process)... my guess
from your initial post is you can probably do what you want without
involving the clipboard (and, depending on what you want for a final
outcome, maybe without putting anything into the worksheet either), but
without knowing what your desired final usage is, it is kind of hard to say.
Don't fix on the solution you "think" you need to do; rather, tell us the
final outcome you want to see (minus any temporary stuff you imagine you
need).

Rick

 

[LeShark]

Assuming you have a string of some kind in the clipboard, why not just copy
it into a cell and then run a macro to extract it one character at a time and
post it into the "next" row

CM

 

[PPL]

Hi Rick,
I'd be delighted to give you more details. Thank you for taking an interest
I appreciate it.
I am working with an in-house (partly) developed Document Management
program. I have no control over the program development but do have to work
with the limited results it produces.

I need to compile a report based on document statistics fields displayed in
a search results screen. I cannot print or export the report.
I found however that by copying the report window to the clipboard and
pasting the results as text, I am able to identify the location of the
various fields.

The number of characters and the positions of the fields never varies.
I want to quickly identify the character position of the starts of each of
these fields so that I can extract them and place them into an Excel
Spreadsheet. I haven't counted the length of the string on the clipboard but
I suspect that there are some 500 characters

As a one off exercise, by copying the contents (character by character into
column A of a sheet I can quickly identify the start and end position of all
my fields.
For example: In the string "The quick brown fox"
The word fox starts in cell A17

I can use that info with VBA InStr commands against the contents of the
clipboard to extract text based on start positions and length & place them
in a structured spreadsheet.

Hope that is a little clearer and I'm sorry ofr the earlier brevity.

Thanks again

Phil

 

[Rick Rothstein \(MVP - VB\)]

Okay, give this idea a try. Go into the VB editor and insert a Module into
your project. Then Copy/Paste the code following my signature into its code
window. Now, in your own code, you can retrieve the text from the clipboard
and assign it to a String variable, like this...

MyStrVar = ClipboardText

Once you have done this, you can use VBA's normal String functions to parse
it. For example, if the clipboard contained "The quick brown fox", you could
find the location of "fox" this way...

MsgBox """fox"" located at character position " & InStr(MyStrVar, "fox")

Rick

Private Declare Function GetDesktopWindow Lib "user32" () As Long

Private Declare Function GetClipboardData Lib "user32" _
(ByVal wFormat As Long) As Long

Private Declare Function CloseClipboard Lib "user32" () As Long

Private Declare Function OpenClipboard Lib "user32" _
(ByVal hwnd As Long) As Long

Private Declare Function lstrlen Lib "kernel32" Alias "lstrlenA" _
(ByVal lpString As Long) As Long

Private Declare Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" _
(pDst As Any, pSrc As Long, ByVal ByteLen As Long)

Private Const CF_TEXT = 1

Public Function ClipboardText() As String
Dim StrPtr As Long
Dim Length As Long
OpenClipboard GetDesktopWindow()
StrPtr = GetClipboardData(CF_TEXT)
If StrPtr <> 0 Then
Length = lstrlen(StrPtr)
If Length > 0 Then
ClipboardText = Space$(Length)
CopyMemory ByVal ClipboardText, ByVal StrPtr, Length
End If
End If
CloseClipboard
End Function

 

[Peter T]

Another one, a bit less code -

First add a Userform (rt click over the project), just to add the MS Forms
2.0 object library to Tools - Ref's. The Userform can now be deleted.

Function GetClipboardText() As String
Dim dObj As DataObject
Set dObj = New DataObject

dObj.GetFromClipboard
GetClipboardText = dObj.GetText
End Function

Sub test3()
MsgBox GetClipboardText
End Sub

Regards,
Peter T

 

[PPL]

Thanks Rick & Peter for your suggestions. I appreicate your help.
Phil

 

[PPL]

After some fiddling around & using ideas from various places, this is what I
ended up with

Sub ClipboardToVariable()
'To get the text on clipboard into a string variable and to output the
result character by character
'into Column A of a worksheet

Dim MyData As DataObject
Dim strClip As String
Dim strMyText As String
Dim strNeed As String
Dim N As Long

Set MyData = New DataObject
MyData.GetFromClipboard
strClip = MyData.GetText
MsgBox strClip
strMyText = strClip
'strNeed = "Main"
x = 0
For N = 1 To Len(strMyText)
x = x + 1
MyChar = Mid(strMyText, x, 1)
ActiveCell.FormulaR1C1 = MyChar
ActiveCell.Offset(rowOffset:=1, columnOffset:=0).Activate

Next
End Sub