Results 1 to 4 of 4

Thread: Creating a SoapClient30 object in VB .NET (Excel) (Converting code from VBA)

  1. #1

    Thread Starter
    New Member
    Join Date
    Jul 2015
    Posts
    3

    Creating a SoapClient30 object in VB .NET (Excel) (Converting code from VBA)

    I'm converting an Excel workbook with VBA to a template with managed code in Visual Studio 2013. The original workbook was a 2003 .xls file, but I'm working with 365.

    The original program had code to add the library that corresponded to the version of Excel the user was using during runtime, which I've updated to this and verified that it does add the reference:

    Code:
    Sub Add_Soap()
            Dim VBAEditor As Microsoft.Vbe.Interop.VBE
            Dim vbProj As Microsoft.Vbe.Interop.VBProject
            Dim chkRef As Microsoft.Vbe.Interop.Reference
            Dim BoolExists As Boolean
            Dim SoapLoc As String
            VBAEditor = Application.VBE
            vbProj = Application.ActiveWorkbook.VBProject
    
            '~~> Check if "Microsoft VBScript Regular Expressions 5.5" is already added
            For Each chkRef In vbProj.References
                If chkRef.Name = "MSOSOAPLib30" Then
                    BoolExists = True
                    vbProj.References.Remove(chkRef)
                    GoTo CleanUp
                End If
            Next
    
    CleanUp:
            If Application.Version = "12.0" Then
                SoapLoc = Environ("commonprogramfiles") & "\microsoft shared\OFFICE12\MSSOAP30.dll"
                vbProj.References.AddFromFile(SoapLoc)
            ElseIf Application.Version = "14.0" Then
                SoapLoc = Environ("commonprogramfiles") & "\microsoft shared\OFFICE14\MSSOAP30.dll"
                vbProj.References.AddFromFile(SoapLoc)
            ElseIf Application.Version = "15.0" Then
                SoapLoc = Environ("commonprogramfiles") & "\microsoft shared\OFFICE15\MSSOAP30.dll"
                vbProj.References.AddFromFile(SoapLoc)
            End If
    
            vbProj = Nothing
            VBAEditor = Nothing
    
        End Sub
    Then there's a class that instantiated a SoapClient:

    Code:
    Dim sc_PricerImportService As SoapClient30
    sc_PricerImportService = New SoapClient30
    Obviously this doesn't compile without the reference added at build time. I don't really understand how it compiled in VBA either, but it did work.

    I tried doing this instead:

    Code:
    Dim sc_PricerImportService As Object
    sc_PricerImportService = CreateObject("MSOSOAPLib30.SoapClient30")
    And I get this error on the latter line: "An exception of type 'System.Exception' occurred in Microsoft.VisualBasic.dll but was not handled in user code

    Additional information: Cannot create ActiveX component."

    I also tried looking for the soap .dll and adding it as a reference through the Project -> Add Reference... menu, but there doesn't seem to be a file called MSSOAP30.dll in the folder Add_Soap() is adding it from, and there's nothing involving "soap" in the COM list.

    One last thing I found was a mention of this regarding SOAP in VB .NET: "Consuming the above service is very easy in .NET. All you have to do is, create web-reference and point it to the WSDL file and you can access all the methods in the service through the intellisense feature."

    That's literally all it says about it though. Is that the route I need to go? How much of the code am I going to have to change to do that instead? There's quite a bit that relies on this, and I'd rather not sort through more of it than I have to. There are also several different URLs for different servers, but they should all behave the same way (the only difference is they might start with www2 or www6 instead of www). Any idea how best to handle that if I go the web service route?

  2. #2

    Thread Starter
    New Member
    Join Date
    Jul 2015
    Posts
    3

    Re: Creating a SoapClient30 object in VB .NET (Excel) (Converting code from VBA)

    I've been playing with the web service idea but I can't get the service to send me anything. There was this code when the SoapClient was initialized:

    Code:
            'Use the proxy server defined in Internet Explorer's LAN settings by
            'setting ProxyServer to <CURRENT_USER>
            sc_PricerImportService.ConnectorProperty("ProxyServer") = "<CURRENT_USER>"
    
            'Autodetect proxy settings if Internet Explorer is set to autodetect
            'by setting EnableAutoProxy to True
            sc_PricerImportService.ConnectorProperty("EnableAutoProxy") = True
            sc_PricerImportService.ConnectorProperty("Timeout") = 600000
            If Left(c_WSDL_URL, 5) = "https" Then
                sc_PricerImportService.ConnectorProperty("UseSSL") = True
                sc_PricerImportService.ConnectorProperty("EndPointURL") = c_WSDL_URL
            End If
    c_WSDL_URL is the URL of the WSDL file (in case that's not obvious). There is a proxy property for the web service, but its only members seem to be to bypass it, get the URI, and set credentials. I don't see any way to tell it to use SSL or anything like that. Does anybody know how to translate the above code to work with a web service? I think if I can get this part to work then the wrapper class around this object will work as before.

  3. #3
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Creating a SoapClient30 object in VB .NET (Excel) (Converting code from VBA)

    The original program had code to add the library that corresponded to the version of Excel the user was using
    well that is not quite as i see it, it actually checks if there is a reference to and removes it if there is

    you could try
    Code:
    sc_PricerImportService = CreateObject("MSSOAP.soapclient30")
    as it would appear your activex reference was not quite correct
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  4. #4

    Thread Starter
    New Member
    Join Date
    Jul 2015
    Posts
    3

    Re: Creating a SoapClient30 object in VB .NET (Excel) (Converting code from VBA)

    It removes a reference if one exists before adding the correct one, though in theory it should never be there when that function runs. The CleanUp runs either way (I checked). I don't know why they didn't just use an Exit For instead of a GoTo, as that's essentially what they're doing.

    I tried changing the line to what you suggested, but it still gives the same error:
    "An exception of type 'System.Exception' occurred in Microsoft.VisualBasic.dll but was not handled in user code

    Additional information: Cannot create ActiveX component."

    Any other ideas? I'd like to get the web service reference working, as that seems like a more version resilient solution.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width