[RESOLVED] Copy File Link to Clipboard

[elino413]

Hi all,

I almost feel silly asking as I feel like the answer is staring me in the face, but I haven't been able to find anything on Google that actually helps my specific issue.

The origin of the issue is moot, but I have an Excel Addin with a button that I would like to use to copy a link to the workbook that is currently active. I am able to get the text to copy to the clipboard, however when I paste it in Word or an Email, it pastes as plain text, but I want a link. Is this possible? I've played with different DataFormats, but I don't think I quite grasp how to use that. Below is what I currently have:

    Private Sub btnCopyLink_Click(sender As System.Object, e As Microsoft.Office.Tools.Ribbon.RibbonControlEventArgs) Handles btnCopyLink.Click
        Dim objBook As Excel.Workbook = Globals.ThisAddIn.Application.ActiveWorkbook

        If MsgBox("Copy a link to " & objBook.FullName & " to the clipboard?", MsgBoxStyle.Question + MsgBoxStyle.YesNo, "Copy Link?") = MsgBoxResult.No Then Exit Sub

        My.Computer.Clipboard.Clear()
        My.Computer.Clipboard.SetText("file:///" & objBook.FullName)
    End Sub

[proneal]

'Demonstrates SetText, ContainsText, and GetText.
Public Function SwapClipboardHtmlText( _
    ByVal replacementHtmlText As String) As String
 
    Dim returnHtmlText As String = Nothing
 
    If (Clipboard.ContainsText(TextDataFormat.Html)) Then
        returnHtmlText = Clipboard.GetText(TextDataFormat.Html)
        Clipboard.SetText(replacementHtmlText, TextDataFormat.Html)
    End If
 
    Return returnHtmlText
 
End Function

from MSDN

[elino413]

'Demonstrates SetText, ContainsText, and GetText.
Public Function SwapClipboardHtmlText( _
    ByVal replacementHtmlText As String) As String
 
    Dim returnHtmlText As String = Nothing
 
    If (Clipboard.ContainsText(TextDataFormat.Html)) Then
        returnHtmlText = Clipboard.GetText(TextDataFormat.Html)
        Clipboard.SetText(replacementHtmlText, TextDataFormat.Html)
    End If
 
    Return returnHtmlText
 
End Function

from MSDN

Thank you for your response. I did see that on MSDN, however I guess I'm not sure how that helps me. I did try setting the text as HTML but I couldn't paste anything after.

[elino413]

So after playing with this I realized that apparently I'm just picky. I wanted the link to paste without the "file:///" before it, but from what I can tell there is no way to do that so I can suck it up.

Just in case anyone can find it useful, I have modified my sub to replace the Drive letter with the network UNC path:

    Private Sub btnCopyLink_Click(sender As System.Object, e As Microsoft.Office.Tools.Ribbon.RibbonControlEventArgs) Handles btnCopyLink.Click
        Dim objBook As Excel.Workbook = Globals.ThisAddIn.Application.ActiveWorkbook
        Dim sPath As String
        Dim oDrive As System.IO.DriveInfo

        sPath = objBook.FullName

        If Mid(sPath, 1, 1).ToUpper = "C" Then
            MsgBox("Links to spreadsheets that are not located in a network folder cannot be copied.", MsgBoxStyle.Critical + MsgBoxStyle.OkOnly, "Cannot Copy Link")
            Exit Sub
        End If

        For Each oDrive In My.Computer.FileSystem.Drives
            If InStr(sPath, oDrive.Name) <> 0 Then
                sPath = sPath.Replace(oDrive.Name, Microsoft.Win32.Registry.GetValue("HKEY_CURRENT_USER\Network\" & Mid(oDrive.Name, 1, 1), "RemotePath", Mid(oDrive.Name, 1, 2)) & "\")
                Exit For
            End If
        Next

        If MsgBox("Copy a link to " & sPath & " to the clipboard?", MsgBoxStyle.Question + MsgBoxStyle.YesNo, "Copy Link?") = MsgBoxResult.No Then Exit Sub

        My.Computer.Clipboard.Clear()
        My.Computer.Clipboard.SetText("file:///" & sPath)
    End Sub