Using VBA to answer Excel pop up questions

[maclachlan]

I need some help automatically answering a couple of Excel questions when they pop up with the following code.
I get 2 messages:
1. There is a large amout of information on the clipboard. Do you want to be able to paste this information into another program later?.... The answer to this questions has to be yes or the rest of the code won't work.
2. Data on the clipboard is not the same size and shape as the selected area. Do you want to paste the data anyway? - I need the user to select yes, I'm not sure why this warning is coming up as the copy and paste ranges are the same. I really want the entire sheet to be copied but I can't get the code right.
Thanks to everybody that has helped me this far.

Private Sub CommandButton1_Click()
        ChDrive "h:\"
           ChDir "h:\"
          Filt = "Excel Files (*.csv), *.csv"
           FilterIndex = 5
           Title = "Please select a different File"
           Filename = Application.GetOpenFilename(FileFilter:=Filt, _
            FilterIndex:=FilterIndex, Title:=Title)
          If Filename = False Then
            Response = MsgBox("No File was selected", vbOKOnly & vbCritical, "Selection Error")
            Exit Sub
        End If
          Response = MsgBox("You selected " & Filename, vbInformation, "Proceed")
           Workbooks.Open Filename


ActiveWorkbook.Sheets(1).Range("a1:bm1000").Copy
 ActiveWorkbook.Close SaveChanges:=False

 ActiveSheet.Paste Destination:=Worksheets("GMAC").Range("a1:bm1000")


Sheets("GMAC").Select
    Selection.AutoFilter
    Selection.AutoFilter Field:=12, Criteria1:="<>0", Operator:=xlAnd
    Sheets("Instructions").Select
End Sub

[/code]

 

[Ivan F Moala]

Hi Erik - does Application.CutCopyMode = False

do the same thing or just remove the dotted lines?

Sorry to hijack your thread maclachlan

There appears to be some confusion.......

Using office gives the user 2 Clipboards
1) OS (Operating Sytem) clipboard
2) Office Clipboard (All office applications eg Excel)

Excel 2000 onwards has it's own Clipboard object (Toolbars - clipboard) where it stores up to
12 items Xl2000 and 24 for XL2003 (Xl2002 ??). If the number of items copied exceeds this number then
the last item copied gets replaced and each subsequent item copied is replaced working backwards.

The OS Clipboard is where data is stored when you select "Edit > Copy", press CTRL-C,
or "Right Click" and select "Copy" from the pop up context menu. When you "Cut" you're
ALSO putting that data on the OS Clipboard AND the Office clipboard. The
difference is that the data is also deleted from the source document (If you paste). It is in
effect a "Copy" and "Delete". The OS Clipboard can only have one chuck of data at a tiime
and can handle many more diff data types then Excel eg Files from Explorer UI. Copying
something else overwrites it. You can infact create your own clipboard format using the
API RegisterClipboardFormat from the user32.dll.
Note, the data is stored in RAM so copying large amounts of data will take up more RAM.
The same holds true for Excel which is why you get a prompt to save the data or clear it
when you do large amounts of copy and paste without clearing.

That is the diff between the OS clipboard and office in that Office handles more items
via its own Clipboard toolbar AND also uses BOTH.

Setting CutCopyMode = False clears the last copied item indicated by the "marching ants"
and clears the "Paste" commandbutton AS WELL AS clearing the OS Clipboard, to which
that API Call clears also. It does NOT, however clear the Office Clipboard (Toolbars - Clipboard)
and is the strength of the ofice clipboard as it holds the data between MS Office applications
eg Word, Excel, Powerpoint, Outlook etc.

So the Application.cutcopymode=false will CLEAR the clipboard (operating system) BUT leave the items
in the Clipboard toolbar and clear the "Paste" commandbutton indicator to show it is empty.

The API will also clear it (OS clipboard) AND leaves the item in the Clipboard tooolbar, so yes
cutcopy mode will do the same as the API.

PS Tom Xl2003 > xlClipboardFormatBIFF

To clear items in the Excel Clipboard

 

[erik.van.geit]

I'll call some other guys: see if they know.

Tom & Ivan,

thank you for the assist
the conclusion

cutcopy mode will do the same as the API.

best regards,
Erik