[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2618
  • Last Modified:

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

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
rayluvs
Asked:
rayluvs
  • 8
  • 7
4 Solutions
 
Michael FowlerSolutions ConsultantCommented:
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
 
rayluvsAuthor Commented:
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
 
Michael FowlerSolutions ConsultantCommented:
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Let's GoCommented:
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
 
rayluvsAuthor Commented:
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
 
rayluvsAuthor Commented:
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
 
Michael FowlerSolutions ConsultantCommented:
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
 
rayluvsAuthor Commented:
I think Im doing something wrong; get an error when using it (see pic)

err
0
 
Michael FowlerSolutions ConsultantCommented:
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
 
rayluvsAuthor Commented:
same error...
err2
0
 
Michael FowlerSolutions ConsultantCommented:
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
 
rayluvsAuthor Commented:
understood.
0
 
Michael FowlerSolutions ConsultantCommented:
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
 
rayluvsAuthor Commented:
That's what we have been trying to do with no success; how would you do it?
0
 
Michael FowlerSolutions ConsultantCommented:
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
 
rayluvsAuthor Commented:
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 8
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now