Macro to Pull Web data based on User input

Hello EE experts,
I need help automating data pull from website into excel or access.  The data span several pages (about 30 pages) based on dateFrom and DateTo and page number. Each page of the report has 500 records with the a header (501) records.  Here is an example of the URL masking the login id and password :
https://www.mopapp.com/api/sales/list.xml?account=xxxxx&key=2324klffg34b5b842449f43fcb7fbae33f5&version=1.0&dateFrom=2012-12-01&dateTo=2012-12-10&page=X

I am looking a solution that will allow me to enter the date range as well as page number to pull the data.

I can pull the data using Data -> From Others Sources --> From XML Data Import, but looking for a streamlined process that will allow some automation.


Thanks in advance for your assistance.
LVL 1
fb1990Asked:
Who is Participating?
 
als315Commented:
Yes, it is possible
DBmopapp.accdb
0
 
als315Commented:
Look at sample (correct account and key in functon getpage). XML class library was taken here:
http://www.jpsoftwaretech.com/vba/msxml-object-library-routines/
DBmopap.accdb
0
 
fb1990Author Commented:
Thank you so much for this solution.  When added the key and selected the dateFrom and DateTo and page=1.  I got error on the form.

Can you please help me look into this?
form-err.jpg
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
als315Commented:
Typo error. Change to isnull.
0
 
fb1990Author Commented:
Hello als315,

I saw that isNull was missing N on the form module.  So when i added the missing N to the code.  It ran.  It gave me the html version of the data.  I am looking for the data in the html code.  It looks like the one attached in excel here.

I need the data to be created as a table in access or excel spreadsheet.  As you can see from excel attachment,  there is a currentPage and totalPages fields.  In this example i have data from 1/1/2013 to 1/1/2013 and there are 4 pages.  The maximum number of record per page is 500.  In this example the codes needs to say from page 1 to page 4

Thanks in advance for all your help..
Sample-Data-Page1---2.xlsx
0
 
fb1990Author Commented:
Here is an example of the html code
<?xml version="1.0" encoding="UTF-8" ?> 
- <root>
- <result>
  <code>200</code> 
- <description>
- <![CDATA[ 
Ok
  ]]> 
  </description>
  </result>
- <data>
  <currentPage>1</currentPage> 
  <totalPages>4</totalPages> 
  <totalItems>1717</totalItems> 
- <items>
- <item>
  <id>154496516</id> 
  <application>58414</application> 
  <storeAccount>5404</storeAccount> 
  <store>4</store> 
  <platform>2</platform> 
  <country>gh</country> 
  <version>3.1.2.0</version> 
  <email /> 
  <device>iPhone/iPod/iPad</device> 
  <date>2013-1-1 00:00:00</date> 
  <type>0</type> 
  <inapp>0</inapp> 
  <numSales>1</numSales> 
  <numUpdates>0</numUpdates> 
  <numInstalls>0</numInstalls> 
  <currency>usd</currency> 
  <unitPrice>0</unitPrice> 
  <unitPriceUsd>0</unitPriceUsd> 
  <unitPriceEur>0</unitPriceEur> 
  <revenue>0</revenue> 
  <profit>0</profit> 
  <revenueUsd>0</revenueUsd> 
  <profitUsd>0</profitUsd> 
  <revenueEur>0</revenueEur> 
  <profitEur>0</profitEur> 
  </item>

Open in new window

0
 
als315Commented:
I can't test results, because I have no access to data in mopapp. Have you got proper pages number after first loop?
0
 
fb1990Author Commented:
i will send the full html code thru gmail.  Thanks for your assistance..
0
 
als315Commented:
Test this sample. I can't test it on real site, but I think it could work.
DBmopapp.accdb
0
 
fb1990Author Commented:
Thank you so much for your help!  It is almost there.

It worked and the data was pasted to the tblItems table.  When i changed the date to new date, it gave me the number of pages in the new dataset (marvelously done!).  The procedure ran correctly, but the data was not appended to the tblItems table.

Is there a way to clear the data from tblItems every time new dataset is ready to be appended?  This way I will have new data in the table each time.  I will like to copy or move the new data to a new table.

Thank you so much for this assistance does far!
0
 
fb1990Author Commented:
I tested it again and it worked as it should.  I merely needed to close the table and open it again.  Thank you, thank you.
0
 
fb1990Author Commented:
Thank you very much.  One last ask.  Excuse  my indulgence.  Is there a way to auto loop through the pages without having to click each time?  Otherwise, this solution is acceptable
0
 
fb1990Author Commented:
All I have to say is wow als315!  Thank you so much for this help!
0
 
als315Commented:
REmove these 2 strings from code (comment them) - it was only for testing:
        r = MsgBox("Ready to get page " & I, vbOKCancel)
        If r = vbCancel Then Exit For
0
 
fb1990Author Commented:
Worked like a charm....Thank you.  I will find a way to add progress bar some other time.  

Thank you so much.  You have saved me countless hours of trying to get data into excel and access using the Web API.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.