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

x
?
Solved

Excel Macro to Pull Website Data

Posted on 2011-02-15
12
Medium Priority
?
629 Views
Last Modified: 2012-05-11
What can be the simplest and smallest Excel Macro code to find a website, and read some data from there? The website will be having fixed fields. (I understand the question is not so small though).
0
Comment
Question by:sanjay-gandhi
[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
  • 4
  • 4
  • 2
  • +1
12 Comments
 
LVL 45

Expert Comment

by:patrickab
ID: 34895651
sanjay-gandhi,

Please provide the URL for the wesite from which you want to obtain the data.

Patrick
0
 
LVL 20

Expert Comment

by:darbid73
ID: 34895897
I agree with patrick - what is the URL.

If the website is nice and you want for example a table excel might be nice to you.  But if you need specific information then you might have to identify, find and get each value individually from the html text that is the webpage.
0
 
LVL 26

Expert Comment

by:redmondb
ID: 34897262
sanjay-gandhi,

And which version of Excel?

Thanks,
Brian.
0
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 

Author Comment

by:sanjay-gandhi
ID: 34908995
Version of Excel is 2007.
For example, www.ssmsindia.com

-Sanjay
0
 
LVL 20

Expert Comment

by:darbid73
ID: 34909282
the words "for example" are a little scary.

That webpage is a pretty standard html website so it should not be too hard.  But and this is a big BUT, you need to know and need to program exactly what you want from the page.  2nd if they change the webpage your code most likely will need to be updated.

So what exactly do you want from this page.
0
 
LVL 45

Expert Comment

by:patrickab
ID: 34910534
sanjay-gandhi,

>For example, www.ssmsindia.com

That's about as vague as it gets. If you want us to help, you will need to specify the exact URL and the data that you are wanting to extract from that website.

Patrick
0
 
LVL 26

Accepted Solution

by:
redmondb earned 2000 total points
ID: 34912031
sanjay-gandhi,

As indicated by the previous responders, this really could be an impossibly open-ended question. However, to perhaps narrow things down, are your requirements any larger than the standard Excel Web functionality?

Have you used "Get External Data" ? Apologies if you're familiar with it, but if not it's really worth a look...
(1) On the menu bar click on "Data" then on "From Web".
(2) On the dialogue displayed, enter the required web address and click on "Go". Excel then displays the web page.
(3) By clicking on the arrow in the yellow box in the top left hand corner you can important the whole page...
(4) ... but probably even more usefully, various elements within the page have their own arrows and you can select the section(s) you want. For example, on http://www.ssmsindia.com/ by clicking on the arrow shown just above "Why Shri Sai?" you can insert that table on to the worksheet.
(5) The sheet remembers that connection and it can be easily manually (or automatically when the file is opened) updated. (Whether manually or by macro, updating is trivial.)

<Loud warning bells> You are very much dependent of the quality and design of the web page. And, as has been said, future changes to the design (content is ok) will likely cause problems (e.g. if a new table is inserted above the one you've been accessing).

Regards,
Brian.
0
 

Author Closing Comment

by:sanjay-gandhi
ID: 35034737
Fine, can you share a small snippet of macro code if I wanted to search for a question in Google. How could I write a small macro on the following?
Find 'Power Point 2007 tips' in Google.
0
 

Author Comment

by:sanjay-gandhi
ID: 35034746
In fact thanks really for holding for long. Incidentally I went busy night and day, so could not respond earlier, apologies for late response. Thanks again.
0
 
LVL 26

Expert Comment

by:redmondb
ID: 35038332
sanjay-gandhi,

Many thanks for the grade! (BTW, did that process pull the data sucessfully for you?)

I'm not sure what you mean by your "Google" post. Could you expand a bit, please?

Thanks,
Brian.

0
 
LVL 26

Expert Comment

by:redmondb
ID: 35039261
sanjay-gandhi,

The attached file has a Google query (""Power Point 2007 tips" | "PowerPoint 2007 tips"). Whenever the file is opened the query is refreshed (it could easily be changed to also refresh, say, every 5 minutes). The query can be manually refreshed by clicking on the "Refresh All" button on the data tab.

It's not needed, but I have also included the macro for creating the query.

OK, all good news so far, but now it's time for the bad. Looking at what google actually passes to Excel, I think it's unlikely that you will find the results useful. You could reformat the page to get rid of the unwanted information and make the rest more readable, but in a lot of cases the url is simply not provided and I'm assuming that would be a key requirement.

If Google is your main interest then it might be worth raising a new question about the Google API. I'm afraid that I can't help with that as I've no experience of using it.

Regards,
Brian.
Create-PowerPoint-Tips-Connectio.xlsm
0
 

Author Comment

by:sanjay-gandhi
ID: 35044279
Hi Brian,

Thanks for all this. I think this will give me a good direction to work on. Sincerely I just needed these sort of pointers to work on. Well, some more questions are on the way. Bye.

Sanjay.
0

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

661 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