Thanks. I tried this one, unfortunately it errors out at line:
stext = MyData.GetText
when the clipboard is empty. I get the error:
Run0time error '-2147221404 (80040064)':
DataObject:GetText Invalid FORMATETC structure
I like this one because it seems to check for text, which is what I
want, I only want this to check to items I can paste in cells (text,
numbers, etc), I don't want a false positive when there are documents
or pictures on the clipboard.
Any other suggestions?
On Jan 27, 4:08 am, ankur <ankurpande...@gmail.com> wrote:
> hi xlcpa
>
> check this one, may be it help you
>
> Dim MyData As DataObject
> Sub C()
>
> Dim stext
> Set MyData = New DataObject
>
> ' Put something on the Clipboard
> Range("a1").Copy
> ' Kill 'marching ants'
> Application.CutCopyMode = False
> ' Get it back
> MyData.GetFromClipboard
> ' Put it in variable 'stext'
> stext = MyData.GetText
> ' Strip it of unprintable characters
> stext = Application.WorksheetFunction.Clean(stext)
> ' Check for length
> If Len(stext) <> 0 Then
> ' Not zero length, then paste it
> Range("b2").Value = stext
> End If
>
> End Sub
>
> Have A Nice Time & Enjoy Life
>
> Regards:
> CMA Ankur Pandey
> (Someone Different)
>
> I'm not the best but i'm not like the rest~~
>
> On Wed, Jan 27, 2010 at 10:23 AM, Dave Bonallack
>
>
>
> <davebonall...@hotmail.com> wrote:
> > Hi XLCPA,
> > I don't know a quick method of doing this, although there may be one.
> > However, to test for an empty clipboard, you could paste to an unused cell,
> > then test the cell.
> > To clear the clipboard you could Copy a blank cell, then use
> > Application.CutCopyMode = False
> > Just some ideas.
> > Regards - Dave.
>
> >> Date: Tue, 26 Jan 2010 14:52:31 -0800
> >> Subject: $$Excel-Macros$$ IF statement to test if Excel Clipboard is Empty
> >> From: jon.wester...@gmail.com
> >> To: excel-macros@googlegroups.com
>
> >> I need to write an IF statment in VBA to test if the Excel clipboard
> >> if empty. Please advise.
>
> >> Also, what is the best way to clear the clipboard in VBA, I'm using:
>
> >> Application.CutCopyMode = False
>
> >> But this doesn't always seem to work.
>
> >> thanks.
>
> >> --
>
> >> ----------------------------------------------------------------------------------
> >> Some important links for excel users:
> >> 1. Follow us on TWITTER for tips tricks and links :
> >>http://twitter.com/exceldailytip
> >> 2. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at
> >>http://www.excelitems.com
> >> 3. Excel tutorials athttp://www.excel-macros.blogspot.com
> >> 4. Learn VBA Macros athttp://www.quickvba.blogspot.com
> >> 5. Excel Tips and Tricks athttp://exceldailytip.blogspot.com
>
> >> To post to this group, send email to excel-macros@googlegroups.com
> >> If you find any spam message in the group, please send an email to:
> >> Ayush Jain @ jainayus...@gmail.com
> >> <><><><><><><><><><><><><><><><><><><><><><>
> >> HELP US GROW !!
>
> >> We reach over 6,700 subscribers worldwide and receive many nice notes
> >> about the learning and support from the group.Let friends and co-workers
> >> know they can subscribe to group at
> >>http://groups.google.com/group/excel-macros/subscribe
>
> > ________________________________
> > Sell your old one fast! Time for a new car?
>
> > --
> > ----------------------------------------------------------------------------------
> > Some important links for excel users:
> > 1. Follow us on TWITTER for tips tricks and links :
> >http://twitter.com/exceldailytip
> > 2. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at
> >http://www.excelitems.com
> > 3. Excel tutorials athttp://www.excel-macros.blogspot.com
> > 4. Learn VBA Macros athttp://www.quickvba.blogspot.com
> > 5. Excel Tips and Tricks athttp://exceldailytip.blogspot.com
>
> > To post to this group, send email to excel-macros@googlegroups.com
> > If you find any spam message in the group, please send an email to:
> > Ayush Jain @ jainayus...@gmail.com
> > <><><><><><><><><><><><><><><><><><><><><><>
> > HELP US GROW !!
>
> > We reach over 6,700 subscribers worldwide and receive many nice notes about
> > the learning and support from the group.Let friends and co-workers know they
> > can subscribe to group at
> >http://groups.google.com/group/excel-macros/subscribe- Hide quoted text -
>
> - Show quoted text -
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at
http://www.excelitems.com
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
If you find any spam message in the group, please send an email to:
Ayush Jain @ jainayus...@gmail.com
<><><><><><><><><><><><><><><><><><><><><><>
HELP US GROW !!
We reach over 6,700 subscribers worldwide and receive many nice notes about the
learning and support from the group.Let friends and co-workers know they can
subscribe to group at http://groups.google.com/group/excel-macros/subscribe
