Programming Excel with VBA and .NET

Share a List

Once a list exists on a worksheet, you can share that list using the Publish method. The first argument of the Publish method is a three-element string array containing the address of the SharePoint server, a unique name for the list, and an optional description of the list. For example, the following code publishes the list created in the preceding section:

Sub ShareList(  )
    Dim ws As Worksheet, lst As ListObject
    Dim str As String, dest(2) As Variant
    Set ws = ActiveSheet
    Set lst = ws.ListObjects("Test List")
    dest(0) = "http://www.excelworkshop.com"
    dest(1) = "Test List"
    dest(2) = "A description goes here..."
    str = lst.Publish(dest, True)
    MsgBox "Your list has been shared. You can view it at: " & str
End Sub

The Publish method returns a string containing the address of the published list. The preceding code displays that address in a message box, but you may want to navigate to that address or include a link to it somewhere on the sheet. To add a hyperlink to the list on the SharePoint server, add a hyperlink to a range as shown here:

' Add link instead of showing message box.
Dim lnk As Hyperlink
Set lnk = ws.Hyperlinks.Add([F1], str)

After adding the hyperlink, you can display the web page for the list by using the Follow method as shown here:

' Display the shared list in the browser.
lnk.Follow

To navigate to the list without adding a hyperlink, use the Workbook object’s FollowHyperlink method:

' Or use the FollowHyperlink method.
ThisWorkbook.FollowHyperlink str

The ListObject ...