Get address of cell copied from excel via clipboard

Question

How to get the Address of the cells copied from excel via clipboard in Vb.net ?

In Vb6 exe by using Clipboard.GetText(vbCFLink) Will able to to get Output as

Excel|[excel path]Sheet1!R48C2:R57C3

How to get the Range of cell copied in vb.net. In vb.net and vb6 existing methods.

VB6                 VB.NET

vbCFBitmap       System.Windows.Forms.DataFormats.Bitmap

vbCFDIB          System.Windows.Forms.DataFormats.DIB

vbCFEMetafile    System.Windows.Forms.DataFormats.EnhancedMetafile
 
vbCFFiles        System.Windows.Forms.DataFormats.FileDrop

vbCFMetafile     System.Windows.Forms.DataFormats.MetafilePict

vbCFPalette      System.Windows.Forms.DataFormats.Palette

vbCFRTF          System.Windows.Forms.DataFormats.Rtf

vbCFText         System.Windows.Forms.DataFormats.Text

As per this link For vbCFLink - No equivalent in vb.net

I created a VB6 ocx/dll and used it to get the same result in VB.net appliation but this not feasible for production currently.

Is there any other method to get the Address range of the cells copied from excel via clipboard in Vb.net?

Answer 1

You can still do this with Clipboard.GetData(), you just have to read the stream yourself:

Using reader = New StreamReader(CType(Clipboard.GetData("Link"), Stream))
    str = reader.ReadToEnd().Replace(vbNullChar, "|"c)
End Using

(Excuse my VB, it's auto translated from C# so may be a little funny.)

You can examine the current clipboard and all it's formats using this handy utility from Nirsoft, and use GetData() to retreive them.

And with edits suggested by @dr.null, you can create a function to replicate the GetText function:

Function GetTextFromClipboard(type As String) As String
    If Not Clipboard.ContainsData(type) Then Return String.Empty
    Dim stream = TryCast(Clipboard.GetData(type), Stream)
    If stream Is Nothing Then Return String.Empty
    Using reader = New StreamReader(stream)
        Return reader.ReadToEnd().TrimEnd(ControlChars.NullChar).Replace(ControlChars.NullChar, "|"c)
    End Using
End Function

And use it for excel links:

Var str = GetTextFromClipboard("Link")