Hi, I found this on the net. Seems to work.
Regards - Dave.
> Date: Wed, 27 Jan 2010 08:15:05 -0800
> Subject: Re: $$Excel-Macros$$ IF statement to test if Excel Clipboard is Empty
> From: jon.wester...@gmail.com
> To: excel-macros@googlegroups.com
>
> 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
_________________________________________________________________
Time for a new car? Sell your old one fast!
http://clk.atdmt.com/NMN/go/157637060/direct/01/
--
----------------------------------------------------------------------------------
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
Copy of cccEx.xls
Description: Binary data
