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

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

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


"PPL" <(E-Mail Removed)> wrote in message news:jHgyj.28921$w94.21453@pd7urf2no...
> 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
>
>
>
>
>

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" wrote:

> 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
>
>
>
>
>
>

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)" <(E-Mail Removed)> wrote in
message news:(E-Mail Removed)...
> 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
>
>
> "PPL" <(E-Mail Removed)> wrote in message
> news:jHgyj.28921$w94.21453@pd7urf2no...
>> 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
>>
>>
>>
>>
>>
>

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



"PPL" <(E-Mail Removed)> wrote in message news:QMiyj.29226$w94.3439@pd7urf2no...
> 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)" <(E-Mail Removed)> wrote in
> message news:(E-Mail Removed)...
>> 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
>>
>>
>> "PPL" <(E-Mail Removed)> wrote in message
>> news:jHgyj.28921$w94.21453@pd7urf2no...
>>> 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
>>>
>>>
>>>
>>>
>>>
>>
>
>

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" <(E-Mail Removed)> wrote in message news:QMiyj.29226$w94.3439@pd7urf2no...
> 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)" <(E-Mail Removed)> wrote in
> message news:(E-Mail Removed)...
> > 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
> >
> >
> > "PPL" <(E-Mail Removed)> wrote in message
> > news:jHgyj.28921$w94.21453@pd7urf2no...
> >> 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
> >>
> >>
> >>
> >>
> >>
> >
>
>

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


"Peter T" <peter_t@discussions> wrote in message
news:%23pXUAw%(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:QMiyj.29226$w94.3439@pd7urf2no...
>> 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)" <(E-Mail Removed)> wrote
>> in
>> message news:(E-Mail Removed)...
>> > 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
>> >
>> >
>> > "PPL" <(E-Mail Removed)> wrote in message
>> > news:jHgyj.28921$w94.21453@pd7urf2no...
>> >> 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
>> >>
>> >>
>> >>
>> >>
>> >>
>> >
>>
>>
>
>

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


"PPL" <(E-Mail Removed)> wrote in message news:jHgyj.28921$w94.21453@pd7urf2no...
> 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
>
>
>
>
>