Method:API Q&A

Re: Importing data into Excel using API

v.cameron — Tue, 15 Aug 2017 15:13:44 GMT

This is huge - thanks , Peter for working on this!

Re: Importing data into Excel using API

Method_Peter — Tue, 15 Aug 2017 14:04:57 GMT

The VBA/Excel samples have been updated on method.me/api. The samples are no longer dependant on the WebServices Toolkit that Microsoft no longer distributes. The updated samples construct the SOAP request as an XML document directly (using an actively distributed XML library from Microsoft that is readilly available and already part of the previous samples).

Re: Importing data into Excel using API

Method_Peter — Fri, 04 Aug 2017 21:23:03 GMT

Hi Peter,

I've re-written one of the VBA/Excel samples without the need for the Web Services Toolkit. We will update the samples on the website but it may take a little time. Feel free to email me directly (p.dyer@method.me) and I can share something with you prior to updating the existing samples.

Regards,

Peter

Re: Importing data into Excel using API

Method_Peter — Wed, 02 Aug 2017 22:09:49 GMT

Hi Peter,

Before we talk API I wanted to make sure you are aware of the export function in Method. In the GUI you can export the data you are looking for in csv format and easilly open it in Excel.

However, since you mentioned API you are probably looking for more of a real-time interface. The Method API is based on the SOAP protocol which primarily uses HTTP POST for interaction. If you pasted the URL you provided in a browser you will not get the reponse you are looking for (I think that is what you have indicated). A browser uses HTTP GET to return web pages. HTTP POSTs are typically generated when you submit a form on the Internet (or in programs specifically written to access APIs). You can do exactly that here: https://www.methodintegration.com/MethodAPI/Service.asmx?op=MethodAPITableListV2 . If you fill in the form you will get the XML reponse you are looking for (Table=Customer, Fields=RecordID,FullName).

The hard part is getting the data into Excel. You can save the reponse from the call above and with a little editing end up with an XML document that Excel will open without issue. However, that involves manual effort which I'm guessing you want to avoid. Microsoft previously published a Web Services Toolkit that was designed for this specific scenario, however, I believe it is no longer available. As I'm writing this I'm not aware of a replacement or another option. I'm going continue to look into this, but wanted to give you a response while I do.

Regards,

Peter

Importing data into Excel using API

thompps — Tue, 01 Aug 2017 23:19:41 GMT

Hi, I hope you can help as I've spent hoursa trying to work out how to get data into Excel. I was initally excited when I saw there was sample code, but it uses outdated references to XP Web Services add-ins which I could no longer obtain.

So using webservice experience from before I tried to in the most simplest way just post the URL and expected to get data back, and that doesn't work either. I used the below to try and do something really simple:

https://www.methodintegration.com/MethodAPI/service.asmx?op=MethodAPITableListV2&strCompanyAccount=XXXX&strLogin=XXXXXX&strPassword=XXXX&strSessionID=XXXXXXX&strTable=Customer

I replaced all the XXX's with the correct fields of course! :) However, all it does is return me to the POST screen where I can enter details in the boxes and invokke it rather than using the data to generate a response.

So I can't even get simple data out without using the tools on the API pages. Can you steer me in the right direction here please? It would be really helpful if you have up to date info or demo's I can use in Excel 16. I just need a worksheet with RecordID and Fullname fields from the Customer table.

thanks

Peter