auto refresh connections wipes clipboard

[merlin777]

I use auto refresh connections, set to every minute. It seems to clear my clipboard so if I copy something and a refresh occurs I can't then paste.

Is there a way around this?

 

[Worf]

You could schedule the macro below to run every minute, see the link on how to do it:

http://www.globaliconnect.com/excel...rvals-or-a-specified-time&catid=79&Itemid=475

Sub Clip()
Dim dClip As MSForms.DataObject, sClip$, cn As WorkbookConnection


Set dClip = New MSForms.DataObject
dClip.GetFromClipboard                      ' save clipboard
sClip = dClip.GetText
ActiveSheet.Protect
ActiveSheet.Unprotect                       ' wipes clipboard
For Each cn In ActiveWorkbook.Connections
    cn.Refresh
Next
Set dClip = New MSForms.DataObject
dClip.SetText sClip
dClip.PutInClipboard                        ' restore it
[a1].Activate
ActiveSheet.Paste
End Sub

 

[merlin777]

You could schedule the macro below to run every minute, see the link on how to do it:

http://www.globaliconnect.com/excel...rvals-or-a-specified-time&catid=79&Itemid=475

Sub Clip()
Dim dClip As MSForms.DataObject, sClip$, cn As WorkbookConnection


Set dClip = New MSForms.DataObject
dClip.GetFromClipboard                      ' save clipboard
sClip = dClip.GetText
ActiveSheet.Protect
ActiveSheet.Unprotect                       ' wipes clipboard
For Each cn In ActiveWorkbook.Connections
    cn.Refresh
Next
Set dClip = New MSForms.DataObject
dClip.SetText sClip
dClip.PutInClipboard                        ' restore it
[a1].Activate
ActiveSheet.Paste
End Sub

thanks, mate - i'll give that a try. Do I need to deselect auto refresh too?

 

[Worf]

Yes, disable auto refresh, as it will be done by the code. That post was just an example, use the version below.

Sub Clip()
Dim dClip As MSForms.DataObject, sClip$, cn As WorkbookConnection
Set dClip = New MSForms.DataObject
dClip.GetFromClipboard                      ' save clipboard
sClip = dClip.GetText
For Each cn In ActiveWorkbook.Connections   ' stuff that wipes clipboard
    cn.Refresh
Next
Set dClip = New MSForms.DataObject
dClip.SetText sClip
dClip.PutInClipboard                        ' restore the clipboard
End Sub