Solved

VBA script to copy a portion of a web page to Excel

Posted on 2015-01-26
16
832 Views
Last Modified: 2015-02-02
We are looking for a way that we can use VBA to copy a portion of a web page into our Excel.  We've had no success.  How can we go about this?
0
Comment
Question by:rayluvs
  • 8
  • 7
16 Comments
 
LVL 23

Assisted Solution

by:Michael74
Michael74 earned 375 total points
ID: 40571701
Here is a good article from Microsoft
https://support.office.com/en-gb/article/Get-external-data-from-a-Web-page-708f2249-9569-4ff9-a8a4-7ee5f1b1cfba

If you would like help with VBA code some details we would need are

What Web page
What are trying to get back
What have you tried and the errors you are encountering
0
 

Author Comment

by:rayluvs
ID: 40571726
We tried the "Get external data from a Web page" (that link specifically), before placing the question but was not successful.

Nevertheless, this is what we are trying to do:

- the page is http://nutritiondata.self.com/facts/vegetables-and-vegetable-products/2349/2
- the data we want to access or grab are the 2 boxes: "Vitamins" and "Mineral" around half way down the page.
  (see pic below, the red circle is the data we want to bring to excel)
values
Based on "Get external data from a Web page", how can we go about it?
0
 
LVL 23

Assisted Solution

by:Michael74
Michael74 earned 375 total points
ID: 40571769
You have picked a very difficult page as the data is not in a table Excel can read and looking at the page source I am not sure that it is possible to get this data out consistently with VBA.

Unfortunately your best bet maybe copy and paste.

Another alternative could be to find a site that uses a format that lends itself to this. While not ideal this site does use forms and Excel can import the data
http://www.healthaliciousness.com/nutritionfacts/nutrition-comparison.php?o=11081&t=11087&h=11081&s=85.0&e=72.0&r=
Import.xlsx
0
 
LVL 6

Assisted Solution

by:Let's Go
Let's Go earned 125 total points
ID: 40571999
I asked a similar question recently; you may be able to adapt the VBA code provided by the expert Rainer Jeschor at http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/Q_28594646.html#a40562046.
0
 

Author Comment

by:rayluvs
ID: 40572484
Thanx both!

You guys gave us an idea combining both recommendation:

1.  First, get a site with tables excel can read: http://ndb.nal.usda.gov/ndb/foods/show/2900
     (in this case for Beets, raw but it will be for more foods)

2. Use VBA similar to 'Let's Go' to extract only the second column.


Based on these:

1. Can we extract the second column using "Get external data from a Web page" ?

2. If not, then using 'Let's Go' VBA, can we extract the second column?
(we are analyzing the VBA and it seems to extract in variable 'strJsonResponse' a portion of the page, not the entire see pic below)
vbanuri
When viewing 'strJsonResponse' contents we saw the lower part of the page, the "Fatty acids" area.

please advice on which is the best to go.

Thanx in advance
0
 

Author Comment

by:rayluvs
ID: 40572612
There are a lot of tools out there, but we found one that seems to get close to what we want (http://www.wiseowl.co.uk/blog/s393/query-table.htm).  The code is:

Sub GetCourseList()
'add a table of courses into an Excel workbook
'the website containing the files listing courses
 Const prefix As String = "http://ndb.nal.usda.gov/ndb/foods/show/2900?fg=&man=&lfacet=&format=&count=&max=25&offset=&sort=&qlookup=beets"
 Const FileName As String = "microsoft-excel-advanced"
 Dim qt As QueryTable
 Dim ws As Worksheet
'using a worksheet variable means autocompletion works better
 Set ws = ActiveSheet

'set up a table import (the URL; tells Excel that this query comes from a website)
 Set qt = ws.QueryTables.Add( _
 Connection:="URL;" & prefix & FileName & ".htm", _
 Destination:=Range("A1"))

'tell Excel to refresh the query whenever you open the file
 qt.RefreshOnFileOpen = True
'giving the query a name can help you refer to it later
 qt.Name = "NutritionalData"
'you want to import column headers
 qt.FieldNames = True
'need to know name or number of table to bring in
'(we'll bring in the first table)
 qt.WebSelectionType = xlSpecifiedTables
 qt.WebTables = 1

'import the data
 qt.Refresh BackgroundQuery:=False
End Sub

Open in new window


It does get us the data, but we need to:

- Have the excel ask for the link
  (instead of placing it in the VBA every time, since there will be lots of food)

- Have it imported without formatting, no "Wrap Text", "Merg & Center", etc.
  (always have it as with the columns as the web page; we found that with certain food like beer [http://ndb.nal.usda.gov/ndb/foods/show/4155?fg=&man=&lfacet=&format=&count=&max=25&offset=&sort=&qlookup=beer] it will include a blank column)

Included is the excel we are working in (the sheet 'The way we want it via VBA' is how we want it with no format)
zGetData-WebPage.xlsm
0
 
LVL 23

Expert Comment

by:Michael74
ID: 40573987
To prompt the user to enter the link is very easy

Dim webSite As String
webSite = InputBox(Prompt:="Enter the Website to be used", Title:="ENTER WEBSITE")

Open in new window

0
 

Author Comment

by:rayluvs
ID: 40574081
I think Im doing something wrong; get an error when using it (see pic)

err
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 23

Expert Comment

by:Michael74
ID: 40574126
The problem here is that you are trying to assign a string variable to a constant value when it will only accept a string literal.

Change the webSite Variable to prefix ie
Dim prefix As String
prefix = InputBox(Prompt:="Enter the Website to be used", Title:="ENTER WEBSITE")

Open in new window

and then remove the line
Const prefix as String = webSite

Open in new window

0
 

Author Comment

by:rayluvs
ID: 40574135
same error...
err2
0
 
LVL 23

Expert Comment

by:Michael74
ID: 40574147
You need to remove the line
Const prefix as String = webSite

Open in new window


When declaring a constant string value in VBA it must be a string literal, that is, a string enclosed in double quotes.

What we are doing is replacing the constant value with a user input and so we are storing this in a standard string variable. So just completely delete this line from your code.
0
 

Author Comment

by:rayluvs
ID: 40574156
understood.
0
 
LVL 23

Expert Comment

by:Michael74
ID: 40574161
Note:

The keyword Dim tells VBA that you are declaring a variable of a specified type to store data or an object.

eg Dim prefix as String

says that a variable with the name prefix will hold string values

The keyword Const tells VBA that you are declaring a constant value with the specified type

eg Const prefix as String = "www.google.com"

Constants must be declared and given a value at the same time and cannot be changed after this.

Variables created with Dim cannot be given a value when declared and can be changed as often as needed.

For some more info have a look at http://www.excelfunctions.net/VBA-Variables-And-Constants.html
0
 

Author Comment

by:rayluvs
ID: 40574164
That's what we have been trying to do with no success; how would you do it?
0
 
LVL 23

Accepted Solution

by:
Michael74 earned 375 total points
ID: 40574169
Sub GetCourseList()
 Dim qt As QueryTable
 Dim ws As Worksheet
 Dim prefix As String

'add a table of courses into an Excel workbook
'the website containing the files listing courses
 prefix = InputBox(Prompt:="Enter the Website to be used", Title:="ENTER WEBSITE")
 Const FileName As String = "microsoft-excel-advanced"

'using a worksheet variable means autocompletion works better
 Set ws = ActiveSheet

'set up a table import (the URL; tells Excel that this query comes from a website)
 Set qt = ws.QueryTables.Add( _
 Connection:="URL;" & prefix & FileName & ".htm", _
 Destination:=Range("A1"))

'tell Excel to refresh the query whenever you open the file
 qt.RefreshOnFileOpen = True
'giving the query a name can help you refer to it later
 qt.Name = "NutritionalData"
'you want to import column headers
 qt.FieldNames = True
'need to know name or number of table to bring in
'(we'll bring in the first table)
 qt.WebSelectionType = xlSpecifiedTables
 qt.WebTables = 1

 'import the data
 qt.Refresh BackgroundQuery:=False
End Sub

Open in new window

0
 

Author Comment

by:rayluvs
ID: 40576407
Thanx worked!

Last question, how can we extract the specific data using Excel "Get external data from a Web page" ?
(since there it uses the web page as a query, maybe we can access specifics)
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
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.

747 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now