• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Integrating API code in excel

P

ppp1812

New Member
I would like to integrate API code into Excel worksheet.
I am totally new to all this and would like to get help on this topic.

Would like to know how to do the same

Thanks

Prakash
 
Hi Prakash ,

It is better to master VBA by itself , without complicating matters using Windows API ; using the API without being familiar with all of its nuances can crash not just Excel but even the OS.
​
To get you started , here are a few links :​
​
​
​
​
​
Narayan​
​
 
NARAYANK991 said:
Hi Prakash ,

It is better to master VBA by itself , without complicating matters using Windows API ; using the API without being familiar with all of its nuances can crash not just Excel but even the OS.
​
To get you started , here are a few links :​
​
​
​
​
​
Narayan​
​
Click to expand...
Hi Narayan

Thanks for your support.

However my issue is as follows:

I am into retail & marketing and we collect lot of customer data such as Name, address and mobile Nos.
Now what happens is that the mobile nos. have to be checked for their status regarding their DND.
If it is not registered for DND then we can send SMS's to that no. otherwise we cannot send.
To do this there is a website from where we can do the scrubbing for the same.
This website is giving the API code so that the same can be integrated into the excel where we take the details.
By doing this we can know the status in real time.

That is why I needed to do this. And me being a total new person to VBA. That is why I need this help from experts.
Pl. advice in this regards.

If you want I can send you the excel file in which we put our data and the website is
http://www.dncindia.com
from where we can get the status regarding the DND. Now this can be done for single nos. as well as a list of Nos. by way of uploading the csv file with the nos in it. But all this takes time and I would be grateful if the same process can be automated. The API code can be seen on this website.

Hope you will look into this.

Thanks and Regards,

Prakash
 
Hi Prakash,

The website holds the API functions written in Java, PHP and C#.Net. I doubt you can integrate any of these into VBA directly, unless you workaround these codes build an XLL and use it just like any Excel addin. This requires lot of expertise to make these work with Excel.

Meantime, if you could upload your Excel file and the description about how you are currently using the file with the website, a workaround can be tried if possible.
 
lohithsriram said:
Hi Prakash,

The website holds the API functions written in Java, PHP and C#.Net. I doubt you can integrate any of these into VBA directly, unless you workaround these codes build an XLL and use it just like any Excel addin. This requires lot of expertise to make these work with Excel.

Meantime, if you could upload your Excel file and the description about how you are currently using the file with the website, a workaround can be tried if possible.
Click to expand...

Hi Lohith,

I am not using the website at the moment as I don't know to do so.

Attached herewith is the Excel File.
Once you see it you will be able to note what we are looking for.

In Co. B we put the 10 digit mobile nos. ........ we need to check these nos. for the DND status and then we should be able to put the same in any corresponding cell i n the same row in order for us to filter the same. I hope I am able to explain this to you.

You may give me your cell no. so that I can call you.

See if something can be worked out.

Thanks and Regards,

Prakash
 

Attachments

  • SMS Sheet.xlsm
    44 KB · Views: 19
Hi Prakash,

Did you check out the API technical document from the website which states about How to use the API from there. You dont have to get any code downloaded from the website but can have a VBA function which prepares the API string and pass on to the website to fetch the results. YOU SHOULD have an account for this to be done.

Please read out the instructions and try doing it manually with an account opened. (It has a free subscription with limited facilites- you can check that first)

I cannot share my phone number here. Read out the New joiners instructions http://chandoo.org/forum/threads/new-users-please-read.294/

We have a conversation option here on the site and am happy to ping you over there..
 
lohithsriram said:
Hi Prakash,

Did you check out the API technical document from the website which states about How to use the API from there. You dont have to get any code downloaded from the website but can have a VBA function which prepares the API string and pass on to the website to fetch the results. YOU SHOULD have an account for this to be done.

Please read out the instructions and try doing it manually with an account opened. (It has a free subscription with limited facilites- you can check that first)

I cannot share my phone number here. Read out the New joiners instructions http://chandoo.org/forum/threads/new-users-please-read.294/

We have a conversation option here on the site and am happy to ping you over there..
Click to expand...
Hi Lohith

I am already a account on the site and have generated my API key which is as follows:

8gdeg4f451i5cls1

I think we would need the API key to use the facility from the site.
Also attached here with is the API documentation from the site. For me all this is very new.

Pl. try and integrate this into excel. And if you can create a add-in I am sure you will be able to get lots of people who would be even willing to pay for the same.

Thanks and Regards,

Prakash


I have also attached herewith the API documentation from the same site
 

Attachments

  • DNCIndia-API-Document.pdf
    356.2 KB · Views: 14
Hi Lohith ,

When I paste the above URL and go , the result is as follows :

{"status":"OK","number":"9822588851","call":"NOT_OK","BIFC":"NOT_OK","realEstate":"NOT_OK","education":"NOT_OK","health":"NOT_OK","CGandA":"NOT_OK","TandL":"NOT_OK","CBEI":"OK"}

Narayan
 
Thank you Narayan..

This means the URL works as expected. Let me work around and give Prakash a complete version for his need.
 
Hey Guys,

You all are awesome. You all really love EXCEL.
Would like to know from Narayan how did he check the link.......... where did he enter into excel inorder to get the result....
I wonder if I will ever be Half as knowledgeable as you all. Anyways it is persons like you who inspire others to excel in EXCEL.

Thanks a LOT Guys.... and keep on the GOOD work

Regards,

Prakash
 
Hi Narayan & Prakash,

Please help me with this. I cannot upload a file or an image with the current environment I work in . So, please put in my changes explained below and try.

With the file Prakash has uploaded copy Column A & B (Names and Phone Number being a header row ) to a new worksheet. Have cell C1 value as the API key (8gdeg4f451i5cls1).

Below code should go into the module. (Have created a user defined function =getsinglescrub(B2,$C$1))

Code: 
Option Explicit
Function GetFromWebpage(URL As String) As String
    On Error GoTo Err_GetFromWebpage
   
    Dim objWeb As Object
    Dim strXML As String
   
    ' Instantiate an instance of the web object
    Set objWeb = CreateObject("Microsoft.XMLHTTP")
   
    ' Pass the URL to the web object, and send the request
    objWeb.Open "GET", URL, False
    objWeb.send
   
    ' Look at the HTML string returned
    strXML = objWeb.responseText
   
    GetFromWebpage = strXML
   
   
End_GetFromWebpage:
    ' Clean up after ourselves!
    Set objWeb = Nothing
    Exit Function
   
Err_GetFromWebpage:
    ' Just in case there's an error!
    MsgBox Err.Description & " (" & Err.Number & ")"
    Resume End_GetFromWebpage
   
End Function
 
 
Public Function GetSingleScrub(PhoneNumber As String, ApiKey As String) As String
    Dim MyString As String
    Dim URLString As String
    Dim strPhoneNumber As String
    Dim strApi As String
    Dim ScrubResult As Variant
    Dim i As Integer
   
    URLString = "http://dncindia.com/dacx/jsonCommand?command=singleScrubApi&data={"
    URLString = URLString & Chr(34) & "number" & Chr(34) & ":" & Chr(34) & PhoneNumber & Chr(34) & " , "
    URLString = URLString & Chr(34) & "apikey" & Chr(34) & ":" & Chr(34) & ApiKey & Chr(34) & ","
    URLString = URLString & Chr(34) & "categories" & Chr(34) & ":" & "[]}"
   
    MyString = GetFromWebpage(URLString)
    GetSingleScrub = MyString
End Function

on Cell D2, use the UDF (=getsinglescrub(B2,$C$1)), This should fetch the result string from the website which will be similar to the result Narayan has got in the previous posts.

From Cell E1 until L1, have these names (call, BIFC, realEstate, education, health, CGandA, TandL, CBEI)
Have the below formula for all cells from E2 to L2. (This should fetch the actual result (OK, Not_OK) from the result string for the above parameters).

Code: 
=
MID(
    $D$2,
    FIND(
        E1,
        $D$2,
        1
    )+
    LEN(
        E1
    )+3,
    IFERROR(FIND(
        ",",
        $D$2,
        FIND(
            E1,
            $D$2,
            1
        )
    ),LEN($D$2)-2)-(
    FIND(
        E1,
        $D$2,
        1
    )+
    LEN(
        E1
    )+4)
)


Sorry for this lengthy post, but have no option to express my findings . :(

Give a try and let me know, how that goes. Thanks.
 
Hi Guys,

Big THANKS to Lohith and Narayan.
Sirrr........... you are telling me ..... a person of almost zero knowledge to try the above mention. I will have to be born again to be as knowledgeable in Excel to do that.
However I am sure by now Narayan must have tried this.
He will probably post it sooner or later.
Waiting for the same.

Thanks to Both of You for doing this for me.
God Bless

Prakash
 
Hi Prakash ,

Sorry , but I have seen Lohith's post just now ; I was away for some time.

Can you give me an hour or so to do what Lohith has posted ?

Narayan
 
Hi Narayan

I tested the file you had send and noted the folowing :

The result is perfect and very accurate.
The sheet you uploaded has records in arnd 409 rows.
Now what happens is that every time you press the Get DND button ..... if there is no "1" in col. K then we get the result.
Now if you put a 10 digit no. in the last cell in the Mobile Col. and then press the Get DND button nothing happens ie we do not get the DND status.

Now what i have done is I have deleted and reduced the rows with records for purpose of ease.

Now I have tried diff. scenarios for which we get diff. results. One thing is for sure that the data which we are getting from the site is accurate and there is absolutely no issue with that. But the process has to be streamlined.

Now do the following with the file I have uploaded and check the results which you are getting:

  1. If you enter a new record of Mobile No. in the last row B19 and press the Get DND button ..... nothing happens.
  2. In the sheet there are Empty rows 6 to 10. Now enter new records in row 6 and 7 and press the Button .........alongwith the data for this two rows the other rows also get populated with a error message. The data for the 6 and 7 record is correct as concerned to the DND status.
  3. Now delete all the records from row 6 to 18 and then enter a new mobile record in row 6 and now press the button .............. all the rows from 6 to 18 will get populated with data.
  4. Now if you delete the complete rows say 16, 17, and 18. and try the process no. 3 above mentioned then you will see that the data gets populated till row 15 only.
  5. Insert rows in between 2 to 18 and then enter a new mobile no. record ......will get you the result alongwith data being populated till the last row.
If there is the digit "1" in col. K in the corresponding row then it skips checking the DND status.
I hope I have been able to explain you all this properly. Try the above mentioned diff scenarios and you will be able to get to the issue.

The button for getting the status is very helpful and seems the right way.

Now what would be the correct process :

If a 10 digit mobile no. is entered in Col B then and then only the DND status should get loaded in the correponding cells otherwise no
And if we are deleting any record from col. B then the corresponding DND status should also get deleted. automatically otherwise it will create lots of confusions. This becomes necessary in case of any record is being updated in case of change in mobile No. of the customer. So once the previous No is deleted and new No. is entered then we should be able to get the status for the new no. for that particular record.

In case any other issue is there then I will post the same but right now I think this is it.

Sorry that I am giving you all lots of trouble.

Thanks & Regards,

Prakash
 

Attachments

  • SMS Sheet 3.xlsm
    69.7 KB · Views: 10
Hi Narayan

Attached here with pl. find the file.

Thanks & Regards,

Prakash
 

Attachments

  • SMS Sheet 4.xlsm
    73.4 KB · Views: 20
You must log in or register to reply here.
Back
Top