Is there a way to determine how many rows are stored on the clipboard?

Ken Loomis

not easy...

This worked in xlXP and xl2003, but didnt work in xl97

usage would be:
numRows = Clipboard_RangeSize(0)



Option Explicit

Const CF_SYLK = 4
Const CF_DSPTEXT = &H81

Declare Function OpenClipboard Lib "user32" (ByVal Hwnd As Long) As Long
Declare Function CloseClipboard Lib "user32" () As Long

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

'Declare Function GlobalAlloc Lib "kernel32" (ByVal wFlags&, ByVal
dwBytes As Long) As Long
Declare Function GlobalLock Lib "kernel32" (ByVal hMem As Long) As Long
Declare Function GlobalUnlock Lib "kernel32" (ByVal hMem As Long) As
Long

Declare Function GlobalSize Lib "kernel32" (ByVal hMem As Long) As Long
Declare Function lstrcpy Lib "kernel32" (ByVal lpString1 As Any, ByVal
lpString2 As Any) As Long
Declare Function lstrlen Lib "kernel32" Alias "lstrlenA" (ByVal lpString
As String) As Long

Function ClipBoard_RangeSize()
Dim lhCB&, lpCB&, lRet&, lSize&, sText$
Dim aTmp, sTmp$, nRow&, nCol&

If IsClipboardFormatAvailable(CF_SYLK) Then
If OpenClipboard(0&) < 0 Then
lhCB = GetClipboardData(CF_DSPTEXT)
If lhCB < 0 Then
lpCB = GlobalLock(lhCB)
If lpCB < 0 Then
lSize = GlobalSize(lpCB)
sText = Space$(lSize)
lRet = lstrcpy(sText, lpCB)
lRet = GlobalUnlock(lhCB)
sText = Left(sText, InStr(1, sText, Chr$(0), 0) - 1)
End If
End If
CloseClipboard
End If
aTmp = Split(sText, " ")
If UBound(aTmp) 2 Then
sTmp = aTmp(UBound(aTmp) - 2)
nRow = Left(sTmp, Len(sTmp) - 1)
sTmp = aTmp(UBound(aTmp))
nCol = Left(sTmp, Len(sTmp) - 1)
End If
End If

ClipBoard_RangeSize = Array(nRow, nCol)

End Function




keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"Ken Loomis" wrote:

Is there a way to determine how many rows are stored on the clipboard?

Ken Loomis

Thanks keepITcool !! Very nice. I've always wanted to grab that string
from the clipboard. Great!! :)
I made a small change by using Val, which will just take the leading
numbers.

If sText Like "Copy *R x *C" Then
v = Split(sText, Space(1))
nRow = Val(v(1))
nCol = Val(v(3))
End If

Instead of:
sTmp = aTmp(UBound(aTmp) - 2)
nRow = Left(sTmp, Len(sTmp) - 1)
sTmp = aTmp(UBound(aTmp))
nCol = Left(sTmp, Len(sTmp) - 1)


Just for my own curiosity, I would be curious to learn if this would work on
the Mac. Just guessing, but my gut feeling is that it would not work on the
Mac. I think the Mac actually puts Excel's copied data on the Windows
Clipboard. Any feedback would be appreciated.

Anyway, thanks again. :)
--
Dana DeLouis
Win XP & Office 2003


"keepITcool" wrote in message
...
not easy...

This worked in xlXP and xl2003, but didnt work in xl97

usage would be:
numRows = Clipboard_RangeSize(0)



Option Explicit

Const CF_SYLK = 4
Const CF_DSPTEXT = &H81

Declare Function OpenClipboard Lib "user32" (ByVal Hwnd As Long) As Long
Declare Function CloseClipboard Lib "user32" () As Long

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

'Declare Function GlobalAlloc Lib "kernel32" (ByVal wFlags&, ByVal
dwBytes As Long) As Long
Declare Function GlobalLock Lib "kernel32" (ByVal hMem As Long) As Long
Declare Function GlobalUnlock Lib "kernel32" (ByVal hMem As Long) As
Long

Declare Function GlobalSize Lib "kernel32" (ByVal hMem As Long) As Long
Declare Function lstrcpy Lib "kernel32" (ByVal lpString1 As Any, ByVal
lpString2 As Any) As Long
Declare Function lstrlen Lib "kernel32" Alias "lstrlenA" (ByVal lpString
As String) As Long

Function ClipBoard_RangeSize()
Dim lhCB&, lpCB&, lRet&, lSize&, sText$
Dim aTmp, sTmp$, nRow&, nCol&

If IsClipboardFormatAvailable(CF_SYLK) Then
If OpenClipboard(0&) < 0 Then
lhCB = GetClipboardData(CF_DSPTEXT)
If lhCB < 0 Then
lpCB = GlobalLock(lhCB)
If lpCB < 0 Then
lSize = GlobalSize(lpCB)
sText = Space$(lSize)
lRet = lstrcpy(sText, lpCB)
lRet = GlobalUnlock(lhCB)
sText = Left(sText, InStr(1, sText, Chr$(0), 0) - 1)
End If
End If
CloseClipboard
End If
aTmp = Split(sText, " ")
If UBound(aTmp) 2 Then
sTmp = aTmp(UBound(aTmp) - 2)
nRow = Left(sTmp, Len(sTmp) - 1)
sTmp = aTmp(UBound(aTmp))
nCol = Left(sTmp, Len(sTmp) - 1)
End If
End If

ClipBoard_RangeSize = Array(nRow, nCol)

End Function




keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"Ken Loomis" wrote:

Is there a way to determine how many rows are stored on the clipboard?

Ken Loomis

Thanks for figuring that out, keepITcool.


"keepITcool" wrote in message
...
not easy...

This worked in xlXP and xl2003, but didnt work in xl97

usage would be:
numRows = Clipboard_RangeSize(0)



Option Explicit

Const CF_SYLK = 4
Const CF_DSPTEXT = &H81

Declare Function OpenClipboard Lib "user32" (ByVal Hwnd As Long) As Long
Declare Function CloseClipboard Lib "user32" () As Long

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

'Declare Function GlobalAlloc Lib "kernel32" (ByVal wFlags&, ByVal
dwBytes As Long) As Long
Declare Function GlobalLock Lib "kernel32" (ByVal hMem As Long) As Long
Declare Function GlobalUnlock Lib "kernel32" (ByVal hMem As Long) As
Long

Declare Function GlobalSize Lib "kernel32" (ByVal hMem As Long) As Long
Declare Function lstrcpy Lib "kernel32" (ByVal lpString1 As Any, ByVal
lpString2 As Any) As Long
Declare Function lstrlen Lib "kernel32" Alias "lstrlenA" (ByVal lpString
As String) As Long

Function ClipBoard_RangeSize()
Dim lhCB&, lpCB&, lRet&, lSize&, sText$
Dim aTmp, sTmp$, nRow&, nCol&

If IsClipboardFormatAvailable(CF_SYLK) Then
If OpenClipboard(0&) < 0 Then
lhCB = GetClipboardData(CF_DSPTEXT)
If lhCB < 0 Then
lpCB = GlobalLock(lhCB)
If lpCB < 0 Then
lSize = GlobalSize(lpCB)
sText = Space$(lSize)
lRet = lstrcpy(sText, lpCB)
lRet = GlobalUnlock(lhCB)
sText = Left(sText, InStr(1, sText, Chr$(0), 0) - 1)
End If
End If
CloseClipboard
End If
aTmp = Split(sText, " ")
If UBound(aTmp) 2 Then
sTmp = aTmp(UBound(aTmp) - 2)
nRow = Left(sTmp, Len(sTmp) - 1)
sTmp = aTmp(UBound(aTmp))
nCol = Left(sTmp, Len(sTmp) - 1)
End If
End If

ClipBoard_RangeSize = Array(nRow, nCol)

End Function




keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"Ken Loomis" wrote:

Is there a way to determine how many rows are stored on the clipboard?

Ken Loomis

As it can be Cut 12R x 5C as well plus I'm unsure what would appear in
non english Excel...

I DO test there's a SYLK format on the clipboard indicating a range
object..

Could use application.Clipboardformats(xlClipboardFormatBIFF ) as
alternative...?


keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"Dana DeLouis" wrote:

Thanks keepITcool !! Very nice. I've always wanted to grab that
string from the clipboard. Great!! :)
I made a small change by using Val, which will just take the leading
numbers.

If sText Like "Copy *R x *C" Then
v = Split(sText, Space(1))
nRow = Val(v(1))
nCol = Val(v(3))
End If

Instead of:
sTmp = aTmp(UBound(aTmp) - 2)
nRow = Left(sTmp, Len(sTmp) - 1)
sTmp = aTmp(UBound(aTmp))
nCol = Left(sTmp, Len(sTmp) - 1)


Just for my own curiosity, I would be curious to learn if this would
work on the Mac. Just guessing, but my gut feeling is that it would
not work on the Mac. I think the Mac actually puts Excel's copied
data on the Windows Clipboard. Any feedback would be appreciated.

Anyway, thanks again. :)

Thanks "keepITcool." Didn't think about that. I'm just messing around with
an alternative ending ...

'// Your code here... then.
Select Case Application.CutCopyMode
Case xlCopy, xlCut
v = Split(sText, Space(1))
ClipBoard_RangeSize = Array(Val(v(1)), Val(v(3)))
Case Else
ClipBoard_RangeSize = Array(0, 0)
End Select
End Function


--
Dana DeLouis
Win XP & Office 2003


"keepITcool" wrote in message
...

As it can be Cut 12R x 5C as well plus I'm unsure what would appear in
non english Excel...

I DO test there's a SYLK format on the clipboard indicating a range
object..

Could use application.Clipboardformats(xlClipboardFormatBIFF ) as
alternative...?


keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"Dana DeLouis" wrote:

Thanks keepITcool !! Very nice. I've always wanted to grab that
string from the clipboard. Great!! :)
I made a small change by using Val, which will just take the leading
numbers.

If sText Like "Copy *R x *C" Then
v = Split(sText, Space(1))
nRow = Val(v(1))
nCol = Val(v(3))
End If

Instead of:
sTmp = aTmp(UBound(aTmp) - 2)
nRow = Left(sTmp, Len(sTmp) - 1)
sTmp = aTmp(UBound(aTmp))
nCol = Left(sTmp, Len(sTmp) - 1)


Just for my own curiosity, I would be curious to learn if this would
work on the Mac. Just guessing, but my gut feeling is that it would
not work on the Mac. I think the Mac actually puts Excel's copied
data on the Windows Clipboard. Any feedback would be appreciated.

Anyway, thanks again. :)

i've checked on a multilanguage (German) excel 2003
and the text IS localized..

Kopiert 65536Z x 1S
Schneidet aus 65536Z x 1S

so my code's use of ubound iso hardcoding the position in the split
was not for nothing :)


i think it's best adjusted to:

If sTextLike "* *#? x *#?" Then
aTmp = Split(sText, Space$(1))
nRow = Val(aTmp(UBound(aTmp) - 2))
nCol = Val(aTmp(UBound(aTmp)))
End If

keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"Dana DeLouis" wrote:

Thanks keepITcool !! Very nice. I've always wanted to grab that string
from the clipboard. Great!! :)
I made a small change by using Val, which will just take the leading
numbers.

If sText Like "Copy *R x *C" Then
v = Split(sText, Space(1))
nRow = Val(v(1))
nCol = Val(v(3))
End If

continued from other post in this thread..

"Dana DeLouis" wrote:

Thanks "keepITcool." Didn't think about that. I'm just messing
around with an alternative ending ...

'// Your code here... then.
Select Case Application.CutCopyMode
Case xlCopy, xlCut
v = Split(sText, Space(1))
ClipBoard_RangeSize = Array(Val(v(1)), Val(v(3)))
Case Else
ClipBoard_RangeSize = Array(0, 0)
End Select
End Function


hmm...


the current instance's cutcopymode can be false,
when the copied range comes from another excel instance

<g

testing for the CT_SYLK does test there's a range object.

see my alternative below, where i also object to
hardcoding the position in the split's result

<g



keepITcool wrote:

i've checked on a multilanguage (German) excel 2003
and the text IS localized..

Kopiert 65536Z x 1S
Schneidet aus 65536Z x 1S

so my code's use of ubound iso hardcoding the position in the split
was not for nothing :)


i think it's best adjusted to:

If sTextLike "* *#? x *#?" Then
aTmp = Split(sText, Space$(1))
nRow = Val(aTmp(UBound(aTmp) - 2))
nCol = Val(aTmp(UBound(aTmp)))
End If

keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"Dana DeLouis" wrote:

Thanks keepITcool !! Very nice. I've always wanted to grab that
string from the clipboard. Great!! :)
I made a small change by using Val, which will just take the leading
numbers.

If sText Like "Copy *R x *C" Then
v = Split(sText, Space(1))
nRow = Val(v(1))
nCol = Val(v(3))
End If

Hi
just as a follow-up: It won't work in a non-English version :-)
I would get for example
12Z5Z
instead of
12R5C

But I definetely agree with Dana: Very nice procedure!


--
Regards
Frank Kabel
Frankfurt, Germany

"keepITcool" schrieb im Newsbeitrag
...

As it can be Cut 12R x 5C as well plus I'm unsure what would appear
in
non english Excel...

I DO test there's a SYLK format on the clipboard indicating a range
object..

Could use application.Clipboardformats(xlClipboardFormatBIFF ) as
alternative...?


keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"Dana DeLouis" wrote:

Thanks keepITcool !! Very nice. I've always wanted to grab that
string from the clipboard. Great!! :)
I made a small change by using Val, which will just take the
leading
numbers.

If sText Like "Copy *R x *C" Then
v = Split(sText, Space(1))
nRow = Val(v(1))
nCol = Val(v(3))
End If

Instead of:
sTmp = aTmp(UBound(aTmp) - 2)
nRow = Left(sTmp, Len(sTmp) - 1)
sTmp = aTmp(UBound(aTmp))
nCol = Left(sTmp, Len(sTmp) - 1)


Just for my own curiosity, I would be curious to learn if this
would
work on the Mac. Just guessing, but my gut feeling is that it
would
not work on the Mac. I think the Mac actually puts Excel's copied
data on the Windows Clipboard. Any feedback would be appreciated.

Anyway, thanks again. :)

Ok. Great. Thanks for the feedback, especially on another language version
like German! That's good to know. :)
Thanks.
--
Dana DeLouis


the current instance's cutcopymode can be false,
when the copied range comes from another excel instance

<g

testing for the CT_SYLK does test there's a range object.

see my alternative below, where i also object to
hardcoding the position in the split's result

<g



keepITcool wrote:

i've checked on a multilanguage (German) excel 2003
and the text IS localized..

Kopiert 65536Z x 1S
Schneidet aus 65536Z x 1S

so my code's use of ubound iso hardcoding the position in the split
was not for nothing :)

Frank

can you please state the exact strings and the excel version(s)
you're using?

using multilingual german xl2003 i get

Kopiert 65536Z x 1S
Schneidet aus 65536Z x 1S

are the spaces and the x missing in your version?

see my latest alternative in another branch of this thread.
please read and respond there.



keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"Frank Kabel" wrote:

Hi
just as a follow-up: It won't work in a non-English version :-)
I would get for example
12Z5Z
instead of
12R5C

But I definetely agree with Dana: Very nice procedure!


--
Regards
Frank Kabel
Frankfurt, Germany

Iv'e copied your entire code into a standard module.

I've added:

Sub RowsInClpBd()
numRows = ClipBoard_RangeSize(0)
MsgBox "There are " & numRows & " in the Clipboard"
End Sub

Now I select 50 rows - single column in another workbook and Ctl+C
If I activate the Current WB (with you code) and run ROwsInClpBb (In
StepMode)
In StepMode It works;

Runnig normally (not in stepmode) I get There are 0 in the Clipboard.

Any suggestions?
TIA,




"keepITcool" wrote in message
...
not easy...

This worked in xlXP and xl2003, but didnt work in xl97

usage would be:
numRows = Clipboard_RangeSize(0)



Option Explicit

Const CF_SYLK = 4
Const CF_DSPTEXT = &H81

Declare Function OpenClipboard Lib "user32" (ByVal Hwnd As Long) As Long
Declare Function CloseClipboard Lib "user32" () As Long

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

'Declare Function GlobalAlloc Lib "kernel32" (ByVal wFlags&, ByVal
dwBytes As Long) As Long
Declare Function GlobalLock Lib "kernel32" (ByVal hMem As Long) As Long
Declare Function GlobalUnlock Lib "kernel32" (ByVal hMem As Long) As
Long

Declare Function GlobalSize Lib "kernel32" (ByVal hMem As Long) As Long
Declare Function lstrcpy Lib "kernel32" (ByVal lpString1 As Any, ByVal
lpString2 As Any) As Long
Declare Function lstrlen Lib "kernel32" Alias "lstrlenA" (ByVal lpString
As String) As Long

Function ClipBoard_RangeSize()
Dim lhCB&, lpCB&, lRet&, lSize&, sText$
Dim aTmp, sTmp$, nRow&, nCol&

If IsClipboardFormatAvailable(CF_SYLK) Then
If OpenClipboard(0&) < 0 Then
lhCB = GetClipboardData(CF_DSPTEXT)
If lhCB < 0 Then
lpCB = GlobalLock(lhCB)
If lpCB < 0 Then
lSize = GlobalSize(lpCB)
sText = Space$(lSize)
lRet = lstrcpy(sText, lpCB)
lRet = GlobalUnlock(lhCB)
sText = Left(sText, InStr(1, sText, Chr$(0), 0) - 1)
End If
End If
CloseClipboard
End If
aTmp = Split(sText, " ")
If UBound(aTmp) 2 Then
sTmp = aTmp(UBound(aTmp) - 2)
nRow = Left(sTmp, Len(sTmp) - 1)
sTmp = aTmp(UBound(aTmp))
nCol = Left(sTmp, Len(sTmp) - 1)
End If
End If

ClipBoard_RangeSize = Array(nRow, nCol)

End Function




keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"Ken Loomis" wrote:

Is there a way to determine how many rows are stored on the clipboard?

Ken Loomis

Jim..

tried to reproduce.. but it works for me.


I think what happens is that you call the sub via the macro dialog.
Invoking the macro dialog (altF8) destroys the cutcopymode, and clears
the clipboard regardless of which macro you run. (or cancel)

If you run your sub from a button (or directly from the VBE with f5)
if works ok.

one tip: in VBE options
set : Require variable declaration= ON

(all new codemodules will have OPTION EXPLICIT as the first line.
and you''ll learn to declare your variables)



keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"Jim May" wrote:

Iv'e copied your entire code into a standard module.

I've added:

Sub RowsInClpBd()
numRows = ClipBoard_RangeSize(0)
MsgBox "There are " & numRows & " in the Clipboard"
End Sub

Now I select 50 rows - single column in another workbook and Ctl+C
If I activate the Current WB (with you code) and run ROwsInClpBb (In
StepMode)
In StepMode It works;

Runnig normally (not in stepmode) I get There are 0 in the Clipboard.

Any suggestions?
TIA,