Improve company productivity with a Business Account.Sign Up

x
?
Solved

Macro to Pull Web data based on User input

Posted on 2013-01-18
15
Medium Priority
?
421 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
  • 9
  • 6
15 Comments
 
LVL 41

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 41

Expert Comment

by:als315
ID: 38798597
Typo error. Change to isnull.
0
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
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 41

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 41

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 41

Accepted Solution

by:
als315 earned 2000 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 41

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

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

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.

Join & Write a Comment

A quick solution showing how to control and open a POS Cash Register Drawer using VBA with MS Access.
I tried to use the SharePoint app to Import a Spreadsheet and import an Excel sheet into a Team site made in SharePoint 2016. But that just resulted in getting an error message 'Unknown Error'...
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Hi, this video explains a free download that you can incorporate into your Access databases, or use stand-alone for contact management. Contacts -- Names, Addresses, Phone Numbers, eMail Addresses, Websites, Lists, Projects, Notes, Attachments…

595 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