Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Macro to Pull Web data based on User input

Posted on 2013-01-18
15
Medium Priority
?
414 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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
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 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 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

What Is Blockchain Technology?

Blockchain is a technology that underpins the success of Bitcoin and other digital currencies, but it has uses far beyond finance. Learn how blockchain works and why it is proving disruptive to other areas of IT.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

715 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