Programming Excel with VBA and .NET

Add Attachments to a List

Excel does not directly support attachments to lists; however, you can use the Lists Web Service AddAttachment method to add a file attachment to a row in a list, then use GetAttachmentCollection to retrieve attachments from within Excel.

For example, the following code attaches bitmaps of a pintle and a gudgeon to the Test List created earlier:

Sub AddAttachments(  )
    Dim ws As Worksheet, src As String, dest As String
    Dim lws As New clsws_Lists
    Set ws = ActiveSheet
    ' Requires web reference to SharePoint Lists.asmx
    src = ThisWorkbook.Path & "\pintle.bmp"
    dest = lws.wsm_AddAttachment("Test List", "1", "pintle.bmp", FileToByte(src))
    src = ThisWorkbook.Path & "\gudgeon.bmp"
    dest = lws.wsm_AddAttachment("Test List", "2", "gudgeon.bmp", FileToByte(src))
End Sub

The AddAttachment method’s last argument is an array of bytes containing the data to attach. To convert the image file to an array of bytes, the preceding code uses the following helper function:

Function FileToByte(fname As String) As Byte(  )
    Dim fnum As Integer
    fnum = FreeFile
    On Error GoTo FileErr
    Open fname For Binary Access Read As fnum
    On Error GoTo 0
    Dim byt(  ) As Byte
    ReDim byt(LOF(fnum) - 1)
    byt = InputB(LOF(fnum), 1)
    Close fnum
    FileToByte = byt
    Exit Function
FileErr:
    MsgBox "File error: " & Err.Description
End Function