Solved

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

Posted on 2015-01-26
16
1,486 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
[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
  • 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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
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…

739 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