COM Library for Excel Web Services - Use EWS from VBA!

In the ODC 2008, I gave a demo of how to use Excel Web Services from a VBA client - the demo was basically just a managed library that wrapped a generated Web Services proxy.

Since Microsoft no longer seems to supply a SOAP toolkit for office, this is the easiest way of achieving access to Excel Web Services.

The library gives access to all of the important methods exposed by EWS and will allow users to read data/set data and otherwise manipulate an Excel Services session. The following VBA example shows how to use the library:

Sub DoIt()

    Dim Es As New EwsCom.ExcelServicesSession

    ' Open the workbook.

    Es.Open "https://bluemonster/_vti_bin/ExcelService.asmx", "https://bluemonster/Test/Shared Documents/Wow.xlsx"

    Debug.Print "Session is:" & Es.SessionId

    ' Get a single cell result.

    Dim R

    R = Es.GetCell("Range2", True)

    Debug.Print "GetCell of Range2 got:" & R

    ' Set a single cell back.

    Es.SetCell "Range2", "My New Value"

    ' Get a range and fill the workbook with the results gotten from it.

    Dim MyRange As EwsCom.ExcelServicesRange

    Set MyRange = Es.GetRange("Range1", False)

    For Row = 0 To MyRange.RowCount - 1

        For Column = 0 To MyRange.ColumnCount - 1

            ActiveCell.Offset(Row, Column).Value2 = MyRange(Column, Row)

        Next

    Next

    ' Save the session workbook into a local file.

    Es.SaveWorkbook WorkbookType_FullWorkbook, "c:\temp\MyFile.xlsx"

    ' Close the session.

    Es.Close

End Sub

As you can see, the major difference between the way the library works and the EWS works is that the Session ID is wrapped so that it doesn't need to be used over and over again - as well as that annoying status array that's usually ignored.

I have published the library on MSDN Code Samples - it's called EwsCom - enjoy.