Solved

Macro to Pull Web data based on User input

Posted on 2013-01-18
15
408 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 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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

840 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