Partager via

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

  • Article

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.

Comments

  • Anonymous
    May 22, 2008
    Corpo: Olá pessoal, Durante a participação no MVP Summit 2008 tive a oportunidade de conversar com vários

  • Anonymous
    July 11, 2010
    This is exactly what I'm looking for! (I don't want to fool around with .net and just access Excel Web Services from VBA in Office. But this example does not include any ready to use Add-In for Office (Excel or PPT). There is no information how to create and how to distribute such an Add-In. Can I somehow bundle the add-in inside a PPT file and have other people use that PPT file and contained Add-In ?

  • Anonymous
    July 11, 2010
    This is exactly what I'm looking for! (I don't want to fool around with .net and just access Excel Web Services from VBA in Office.) But this example does not include any ready to use Add-In for Office (Excel or PPT). There is no information how to create and how to distribute such an Add-In. Can I somehow bundle the add-in inside a PPT file and have other people use that PPT file and contained Add-In ?