Clipboard manipulation
PPL
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
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
Rick Rothstein (MVP - VB)
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
"PPL" <p...@shaw.ca> wrote in message news:jHgyj.28921$w94.21453@pd7urf2no...
LeShark
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
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)" <rick.new...@NO.SPAMverizon.net> wrote in
message news:OHxJOm8e...@TK2MSFTNGP03.phx.gbl...
Rick Rothstein (MVP - VB)
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
"PPL" <p...@shaw.ca> wrote in message news:QMiyj.29226$w94.3439@pd7urf2no...
Peter T
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" <p...@shaw.ca> wrote in message news:QMiyj.29226$w94.3439@pd7urf2no...
PPL
Phil
"Peter T" <peter_t@discussions> wrote in message
news:%23pXUAw%23eIH...@TK2MSFTNGP03.phx.gbl...
PPL
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
"PPL" <p...@shaw.ca> wrote in message news:jHgyj.28921$w94.21453@pd7urf2no...