How can I clear clipbord meory? 2

[JensKKK]

I am writing a report function and I use the clipbord to transport data (with in Excel or to Word). Ideally I would like to clear the clipbord memory after every paste transaction.

Any help appreciated.


Code

Application.ScreenUpdating = False
Do Until weiter = False
dummy = Sheets("3d rotate").Cells(1000 + ii, 1 + i)
If dummy <> "" Then
decoded_link = Sheets("3d rotate").Cells(2000 + ii, 1 + i)
Application.Run "transfer_column_diagram_data", i, ii, replicate, j, dummy
part1 = Sheets("3d rotate").Cells(13999 + decoded_link, 15) & Sheets("3d rotate").Cells(13999 + decoded_link, 11)
part2 = Sheets("3d rotate").Cells(13999 + decoded_link, 10)


Select Case replicate
Case 1
Sheets("3d rotate").ChartObjects("Chart 1").CopyPicture
Case 2
Sheets("3d rotate").ChartObjects("Chart 2").CopyPicture
Case 3
Sheets("3d rotate").ChartObjects("Chart 3").CopyPicture
Case 4
Sheets("3d rotate").ChartObjects("Chart 5").CopyPicture
End Select

Sheets("detailed results").Select
If Graphcount = 0 Then
Sheets("detailed results").Cells(5 + i + j, 2).Select
'ActiveSheet.Paste
Sheets("detailed results").Paste
Sheets("detailed results").Cells(4 + i + j, 2).Select ' = ii + 1 & " " & dummy
'ActiveSheet.Hyperlinks.Add
Sheets("detailed results").Hyperlinks.Add _
Anchor:=Selection, Address:=part1, TextToDisplay:=ii + 1 & " " & part2
Else
Sheets("detailed results").Cells(5 + i + j, 9).Select
'ActiveSheet.Paste
Sheets("detailed results").Paste
Sheets("detailed results").Cells(4 + i + j, 9).Select ' = ii + 1 & " " & dummy
Sheets("detailed results").Hyperlinks.Add _
Anchor:=Selection, Address:=part1, TextToDisplay:=ii + 1 & " " & part2
End If
Graphcount = Graphcount + 1

If Graphcount = 2 Then
Graphcount = 0
j = j + Y_distance
End If
ii = ii + 1
'msg = MsgBox("ii : " & ii & "maxhits " & Max_Hits)
Else
weiter = False
If ii = 0 Then
j = j + Y_distance
End If
j = j + 1
End If
If ii > Max_hits - 1 Then
j = j + Y_distance
weiter = False
End If
Loop
Application.ScreenUpdating = True

 

[mvidas]

Hi Jens,

Using MSForms' data object you can just put a blank string into the clipboard, effectively clearing it:

Sub ClearClipboard()
 Dim DatObj As DataObject
 Set DatObj = New DataObject
 DatObj.SetText ""
 DatObj.PutInClipboard
End Sub

Matt

 

[JensKKK]

Matt,

that is a very good idea.

Thanks very much!

 

[AHJ1]

Can you help to Access the data object in Access 2003?
I have tried this code and it fails with a User Defined Type not defined. I tried setting a reference to the ADO library. Is there a different reference that I need to set?

Alternatively, can you suggest how to do an API call to copy the contents of a RichTextBox to the clipboard so that I can strip out RTF codes?

I tried the RunCmd accmdcopy, but it copies the RichText formatting.

Many thanks,
Alan

Private Sub cmdCopyStrip_Click()
On Error GoTo err_copystrip
'Copy contents of hidden textbox
 Dim DatObj As DataObject
 Set DatObj = New DataObject
    With Me!txtcusRTF
        .Visible = True
        .SetFocus
        .SelStart = 0
        .SelLength = Len(Me!txtcusRTF.Text)
    End With
        'RunCommand acCmdCopy 
    DatObj.SetText (Me!txtcusRTF.Text)
    DatObj.PutInClipboard
    Me!txtcusRTF.Visible = False
    Me!txtPhraseStripped.SetFocus
    strStrippedText = ClipBoard_GetData
    Me!txtPhraseStripped = strStrippedText

    
Exit Sub
err_copystrip:
    ErrBox "stripping out RTF and pasting into table for searching in cmdCopyStrip_click"
End Sub

 

[mvidas]

Hi Alan,

The DataObject is not part of ADO (though the name would have you think it was), it is actually part of the MSForms library. Check the references for your project, and verify there is a reference to Microsoft Forms 2.0 Object library. It should be set by default, but it could have been removed for whatever reason.

Assuming you have the "Auto List Members" option checked in your VBE, you can verify it by typing:
Dim DatObj as MSForms.

And while typing the "." you should see the different MSForms objects available to you, like the DataObject.

Why are you copying this to the clipboard anyways?

Matt

 

[AHJ1]

I can't find the MsForms library. Perhaps it isn't registered. Can you tell me the name of the .DLL to which it points?

I want to copy to the clipboard to strip out RTF characters. If I were using Access2007 I would just use the PlainText() function, but I'm using Access2003, and the function is not available. I could also use regular expressions, but I'm not sure how to implement this is Access 2003.

The reason for stripping out the information is that I'm using an RTF control that is not active X to preclude problems with users on the network not being able to install the RTF control, and the CTRL+F Search function does not work in this control, so I'm copying the text to another control, stripping out the RTF. That field is searchable.

Thanks. I'm going to lunch now. Hope to hear from you.

Alan

 

[mvidas]

Hi again,

The library (at least on my machine, Windows 2000 and Office 2000) is C:\WINNT\System32\FM20.DLL. I would imagine on XP it would be at Windows instead of WINNT, I can verify that for you later if need be.
I do have Access 2003 installed on this machine as well (the original post was for Excel and I know VBA in that much more than Access), and I do not see the forms library in the list of references. Clicking browse and selecting ..\System32\FM20.DLL did work for me for adding the reference, so it should hopefully work for you.

I now understand your reasoning for wanting to do this. As a (poor) workaround, you could use outlook to create an RTF email, then change the format to Plain Text. That does seem like a bad crutch, but it should work.

I would be happy to give you instructions on using RegExp in VBA as well if you would like, if copying it to the clipboard won't work as you intend it to.

In any case, let me know if you need anything else, though we may want to move this to a new thread.

Matt

 

[AHJ1]

Hi,
I was able to reference the DLL. It gets placed at the very end of the reference list, and needs to be moved up into alphabetical order for easy reference.

Unfortunately, this procedure provides the same results as the 'RunCommand acCmdCopy. i.e. It copies all of the Rich Text Instructions.

I think automating Outlook is too much of a kludge. It's a shame I can't just automate notepad, that would work.

I'll make a separate post about Regular Expressions.

Thanks.

Alan