[Solved] Clipboard Usage by excel

  • Hi,
    From the copy and paste that excel(i work with excel 2000) implements ,whenever a copy statement is given the excel copies onto common windows clipboard and pastes from it.Excel also maintains the 12 selections in the private clipboard(office clipboard).


    My queries are:-

    1)From VB will i be able to access this set of selections.


    2)Is this selection clipboard common or distinct for each instance of excel.(what i mean here is the instance generated from vb to access excel without using references i.e createobject function is used.)



    The reason for the above queries is that if two operations of copy (when programatically this copy macro is called)is done the latest copying action will replace the contents in the common windows clipboard.My aim is to prevent this.Although this is possible through api support from vb (i.e to look for an open clipboard ,this will take time in this loop check),i wanted to know whether this can be avoided ,if it can done from excel itself



    With Regards
    Gopal

  • Hi gopalsb,


    Some interesting points on Office Clipboard.

    • You cannot paste a formula to the Office Clipboard.
    • The contents of the Office Clipboard are deleted when the current session ends. If you have one Office program running, the contents of the Office Clipboard are deleted when you close that program. If you have multiple Office programs running, the contents of the Office Clipboard are deleted after you close the last Office program.
    • There is no Visual Basic for Applications object model for the Office Clipboard, so there is no way to programmatically manipulate it. (it's possible this has changed in XP)


    Hope this is helpful.


    Cheers
    Andy

  • Hi Andy and gopalsb,


    Some notes on XP - -


    You can paste a formula fo the Clipboard in XP --- not directly from the cell - you have to highlight it in the formula toolbar and Copy. Don't know if that works in other versions.


    XP does have a limited object model with such things as:


    DisplayClipboardWindow Property
    ClipboardFormats Property
    PutInClipboard Method
    GetFromClipboard Method


    so some manipulation in VBA is possible.



    Regards


    ......Ralph

  • All,


    I use the following code to launch the clipboard, insert an image as well as format it a bit.


    You may be able to use it to manipulate the clipboard.


    Sub ViewClipboardCurve()
    Dim mycell As Range
    On Error Resume Next
    MyAppID = Shell("C:\WINDOWS\CLIPBRD.EXE", 1)
    MyAppID = Shell("C:\Marketing\Quote Package\Programs\CLIPBRD.EXE", 1)
    On Error GoTo ErrorMessage
    Set mycell = Application.InputBox(prompt:="This will insert the contents of the Clipboard Viewer into the worksheet as an image." & vbCr & vbCr & "Please click on the worksheet to select the location to place the curve." & vbCr & vbCr & "Click the Cancel button if you do not wish to proceed", Title:="Curve Location", Type:=8)
    Application.Range(mycell.Address).Activate
    Application.ScreenUpdating = False
    ActiveSheet.PasteSpecial Format:="Picture (Enhanced Metafile)", Link:=False, DisplayAsIcon:=False
    Selection.ShapeRange.IncrementLeft 10
    Selection.ShapeRange.IncrementTop 13
    Selection.ShapeRange.Fill.Visible = msoTrue
    Selection.ShapeRange.Fill.Solid
    Selection.ShapeRange.Fill.ForeColor.SchemeColor = 9
    Selection.ShapeRange.Fill.Transparency = 0#
    Selection.ShapeRange.Line.Weight = 0.75
    Selection.ShapeRange.Line.DashStyle = msoLineSolid
    Selection.ShapeRange.Line.Style = msoLineSingle
    Selection.ShapeRange.Line.Transparency = 0#
    Selection.ShapeRange.Line.Visible = msoTrue
    Selection.ShapeRange.Line.ForeColor.SchemeColor = 8
    Selection.ShapeRange.Line.BackColor.RGB = RGB(255, 255, 255)
    Selection.ShapeRange.LockAspectRatio = msoFalse
    Selection.ShapeRange.Height = 808.5
    Selection.ShapeRange.Width = 570#
    Application.Range(mycell.Address).Activate
    Exit Sub
    ErrorMessage:
    MsgBox ("INSERT FAILED - Clipboard does not contain a valid curve image."), vbCritical, "WSG-eQuote"
    MsgBox ("Please ensure that you copy your curve from Perfcurve and try again." & vbCr & vbCr & "This can be done by right mouse clicking on the selected curve, Copy > To Clipboard."), vbInformation, "WSG-eQuote"
    End Sub
    Sub ClipboardViewerClose()
    On Error GoTo error
    AppActivate "Clipboard Viewer"
    SendKeys "%{F4}", True
    error:
    Exit Sub
    End Sub


    Also worth noting is that in my version of Clipboard you can save the information copied to the clipboard as a *.clp file.


    If you have specific data you need pasted in from the clipboard then you can programatically send it to the clipboard & possibly save it as a file? In theory you should ten be able to launch Clipboard, Load the file with you data and paste it into the workbook.


    Just some thoughts that may prompt an alternative workaround.


    Allcare


    AJW

  • Hi rem1224 and AJW,


    Yes in XL2000 you can get the formula by copying whilst editing the formula.


    the help in XL2000 tells me that
    DisplayClipboardWindow is a MAC (Office98) command.


    The others are supported and with VBA you can manipulate the clipboard.


    BUT my understanding is that gopalsb question was about the Office clipboard. The one the contains multiple clips, up to 12.


    Quote

    Office 2000 includes a new clipboard called the Office Clipboard. You can use this clipboard to collect and paste multiple items. For example, you can copy a drawing object in Microsoft Excel, switch to Microsoft PowerPoint and copy a bulleted list, switch to Microsoft Internet Explorer and copy a page of text, and then switch to Microsoft Word and paste the collection of copied items.


    NOTE: You can copy items while using any program that provides copy and cut functionality, but you can only paste items into Word, Excel, PowerPoint, Microsoft Access, or Microsoft Outlook.


    Cheers
    Andy


  • Hi Andy & Others,


    Quote: "The reason for the above queries is that if two operations of copy (when programatically this copy macro is called)is done the latest copying action will replace the contents in the common windows clipboard.My aim is to prevent this."


    Specifically: " if two operations of copy (when programatically this copy macro is called)is done the latest copying action will replace the contents in the common windows clipboard.My aim is to prevent this."


    My reading was that gop wanted to stop the common Windows Clipboard contents being replaced.


    I don't know that this can be done so I was suggesting a possible work around to capture the contents before being replaced as a *.clp file and then reload this file to the Clipboard when needed.


    I'm still stuck in Office 97 so I know diddly about the Office specific Clipboard included in 2000+ and XP.


    Hope this clarifies my response.


    Cheers


    AJW

  • Hi Andy/rem1224 /Ajw,


    rem1224,
    The excel version will be xl2000.so probably the object manipulation is not an option.


    Andy,
    I was referring to office cilpboard , to check out whether programmatically we will be able to control and allocate the 12 selections.Since this is not possible ,may be we have to drop this idea.


    AJW,
    Thanks for the idea.Probably what i want is some thing more than just saving the already existing contents in the clipboard.( Probably i will use the api support from vb and a discussion of it should not go beyond this forum.anyway i will post the method that i have taken )


    Thanks for the suggestions and ideas.


    Regards
    Gopal

  • Hi Andy/rem1224/ajw


    As i had earlier stated ,my aim was to serialize the access to the clipboard and i achieved it by using & checking a mutex variable (whenever access to the clipboard was required).If the clipboard was in use this variable(kernle object) would be set and reset after the clipboard was used up by the application that set the mutex variable.


    Regards
    Gopal

  • Gopalsh,


    Thanks for the update, could you give us an example of how you achieved this with VBA? It would help us unlearned ones to further understand the outcome.


    AJW

  • Hi AJW,


    Primarily i wanted to achieve this from vb :) .The api's that were used are:-


    waitforsingleobject
    CloseHandle
    ReleaseMutex
    CreateMutex
    OpenMutex


    when i used it a function which copied to the clipboard,i used it in the following way.


    In the start of the function which did the copying


    'check for mutex for clipboard before copying
    lngMutex = OpenMutex(SYNCHRONIZE, 0, "Clipboard Access")

    'mutex is yet to be created
    If lngMutex = 0 Then
    lngMutex = CreateMutex(ByVal 0&, 1, "Clipboard Access")
    Else 'wait indefintely
    Call WaitForSingleObject(lngMutex, INFINITE)
    End If



    Towards the end of the function


    'release ownership of mutex
    Call ReleaseMutex(lngMutex)

    'close the handle to mutex
    Call CloseHandle(lngMutex)



    This will serialize the access to the clipboard.


    HTH
    Gopal

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!