K
Ken Loomis
Is there a way to determine how many rows are stored on the clipboard?
Ken Loomis
Ken Loomis
K
keepITcool
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 >
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 >
D
Dana DeLouis
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:
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. :>)
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:
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. :>)
K
Ken Loomis
Thanks for figuring that out, keepITcool.
keepITcool said:
K
keepITcool
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 >
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 >
D
Dana DeLouis
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
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
K
keepITcool
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 >
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 >
K
keepITcool
continued from other post in this thread..
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>
Dana DeLouis said:
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>
F
Frank Kabel
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!
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!
D
Dana DeLouis
Ok. Great. Thanks for the feedback, especially on another language version
like German! That's good to know. :>)
Thanks.[/QUOTE]
like German! That's good to know. :>)
Thanks.[/QUOTE]
K
keepITcool
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 >
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 >
J
Jim May
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,
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,
K
keepITcool
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 >
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 >