-
Jul 30th, 2015, 11:51 AM
#1
Thread Starter
New Member
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?
-
Jul 30th, 2015, 01:29 PM
#2
Thread Starter
New Member
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.
-
Jul 30th, 2015, 04:28 PM
#3
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
-
Jul 31st, 2015, 01:54 PM
#4
Thread Starter
New Member
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|