Solved

Macro to Pull Web data based on User input

Posted on 2013-01-18
15
410 Views
Last Modified: 2013-01-21
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.
0
Comment
Question by:fb1990
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 6
15 Comments
 
LVL 40

Expert Comment

by:als315
ID: 38797882
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
 
LVL 1

Author Comment

by:fb1990
ID: 38798504
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
 
LVL 40

Expert Comment

by:als315
ID: 38798597
Typo error. Change to isnull.
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 1

Author Comment

by:fb1990
ID: 38798604
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
 
LVL 1

Author Comment

by:fb1990
ID: 38798630
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
 
LVL 40

Expert Comment

by:als315
ID: 38798943
I can't test results, because I have no access to data in mopapp. Have you got proper pages number after first loop?
0
 
LVL 1

Author Comment

by:fb1990
ID: 38799146
i will send the full html code thru gmail.  Thanks for your assistance..
0
 
LVL 40

Expert Comment

by:als315
ID: 38801261
Test this sample. I can't test it on real site, but I think it could work.
DBmopapp.accdb
0
 
LVL 1

Author Comment

by:fb1990
ID: 38801937
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
 
LVL 40

Accepted Solution

by:
als315 earned 500 total points
ID: 38802034
Yes, it is possible
DBmopapp.accdb
0
 
LVL 1

Author Comment

by:fb1990
ID: 38802067
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
 
LVL 1

Author Comment

by:fb1990
ID: 38802195
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
 
LVL 1

Author Closing Comment

by:fb1990
ID: 38802235
All I have to say is wow als315!  Thank you so much for this help!
0
 
LVL 40

Expert Comment

by:als315
ID: 38802293
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
 
LVL 1

Author Comment

by:fb1990
ID: 38802609
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question