[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

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

Posted on 2015-01-26
16
Medium Priority
?
2,287 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:Michael Fowler
Michael Fowler earned 1500 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:Michael Fowler
Michael Fowler earned 1500 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 6

Assisted Solution

by:Let's Go
Let's Go earned 500 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:Michael Fowler
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:Michael Fowler
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:Michael Fowler
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:Michael Fowler
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:
Michael Fowler earned 1500 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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

649 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