How JustAnswer Works:

  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.

Ask swmcdonnell Your Own Question

swmcdonnell
swmcdonnell, Microsoft Office Expert
Category: Microsoft Office
Satisfied Customers: 233
Experience:  Steve uses Office and trains others in how to use Word, Excel, PowerPoint and Access.
47680681
Type Your Microsoft Office Question Here...
swmcdonnell is online now
A new question is answered every 9 seconds

I want to connect Microsoft excel to a webservice to

Customer Question

I want to connect Microsoft excel to a webservice to retrieve information based on a search term typed into a userform using vba. I would like the solution to work on excel 2010 and 2013. The webservice requires authentication in the form of a username and password. The source of the content is Training.gov.au. There is a sandbox environment to test out in and a sample app with source code. I have experience with vba and excel userforms
Submitted: 2 months ago.
Category: Microsoft Office
Customer: replied 2 months ago.
https://data.gov.au/dataset/training-gov-au-web-service-access-to-sandbox-environment here is the link to the support details on connecting to the webservice
Expert:  swmcdonnell replied 2 months ago.

Hi, my name is***** took a look at the link you provided, but you didn't specify what you were trying to get from the Web service, so let me point you in the right direction, since you're familar with VBA.

The easiest way is to use the WinHttpRequest object. For example:

Sub getInfo()

Dim request As Object

Set request = CreateObject("WinHttp.WinHttpRequest.5.1")

request.Open "GET" "<theurl>" "<param1>" "<param2>"

request.send

answer = request.responseText

End Sub

Customer: replied 2 months ago.
Hi Steve,I believe I did specify what I wanted to get from the web service. I need a userform with a text field that when you enter in a course code and press a command button you get a listbox that is populated with all the units in that course.The webservice is provided from training.gov.au which is a website that contains Training courses and unit information that are contained and related to the courses.The webservice requires authentication in the form of a username and password.Here is code used to achieve authentication in C# ***** a simple console program."using ConsoleApplication2.TgaOrg;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;namespace ConsoleApplication2
{
class Program
{
static void Main(string[] args)
{
OrganisationServiceClient proxy = new OrganisationServiceClient("OrganisationServiceBasicHttpEndpoint");
if (proxy.ClientCredentials != null)
{
proxy.ClientCredentials.UserName.UserName = "WebService.Read";
proxy.ClientCredentials.UserName.Password = "Asdf098";
}
DateTime serverTime = proxy.GetServerTime();
Console.WriteLine(serverTime);
Console.WriteLine("Press any key to close");
Console.ReadKey();
}
}
}
"
Expert:  swmcdonnell replied 2 months ago.

Hi there. Thanks for being more specific -- that's very helpful. (what you said previously is " want to connect Microsoft excel to a webservice to retrieve information based on a search term typed into a userform using vba." )

I guess I'm not sure what you want me to do or why you posted C# ***** here, when I thought you were doing it in Visual Basic? I provided some VB code for you to use to call the Web service. Did I miss something? Sorry if I did or am not understanding you well.

Customer: replied 2 months ago.
I too am sorry that you are not understanding me well. Thanks anyway! The sample video that comes with the tga pack which is available via the link sent shows a method of accessing the authentication via c# ***** the form of a console application. I want to apply this but in vba via an excel userform with the further requirements as outlined. Let me know if you can help.
Expert:  swmcdonnell replied 2 months ago.

OK, I'll opt out and see if someone else can help you.