Import Live Web Forms in to
a Spreadsheet
1
Create a new blank workbook.
a.
In Excell 2007/2010, click the Data tab on the
ribbon bar then click From Web in the Get External Date section.
b.
In older versions of Excel, click Data, the
Import External Data, and then New Web Query.
 |
| 01. Type the address of website, which you want import |
2
In all cases, a miniature web browser will open
(resize this if necessary by clicking and dragging any corner).
3
Type the web address into the box labelled
Address then click the Go button.
 |
| 02. The website has been downloaded |
4
Now click the Option button at the top right of
the browser, select 'Full HTML formatting' and click OK.
5
In the mini browser window you will see small
yellow and black arrow icons down the left side of the web page. These identify
tables that can be imported.
 |
| 03. Select the area, which you want copy to the Excel |
6
Hover the mouse pointer over one of these arrow,
and a blue frame will appear round the relevant table.
7
Scroll down the web page until you see the arrow
just to the left of the data column-click it.
8
The arrow will turn into a green tick mark,
indicating the table has been selected.
9
You can select multiple tables on a page, but in
this case we only need one.
10
Next click the Import button.
11
A Dialogue Box appears asking where to put the
data. Click the Properties button and tick the box labelled 'Refresh data when
opening the file' in the refresh Control section; this will update the table
every time you open the spreadsheet.
 |
| 04. Getting Data |
12
You can also make the data update periodically
while the file is open by ticking the 'Refresh every' box and setting the
minutes in the box next to it.
13
When you have finished setting the options,
click OK to close the dialogue box and then OK again to import the table.
14
The data table will appear in the spreadsheet
(in Excel 2002/3, a small External Data toolbar also appears). To hide unwanted
rows and columns, right click on a row or coloumn hold the Control (Ctrl) key
while selecting, then right-click one of the selected row numbers or column
letters.
Note that, deleting a cell, row or
column won't work, as each time the table is updated the deleted cells will be
reinstated.
15
You can use the imported values in formulae or
functions just like any other Excel data.
c.
However, don't put formulae into cells in the
table, as there will be overwritten when it is updated.
d.
To update the table manually, click the Refresh
All button on the Data tab (in Excel 2002/2003, click Data, then Refresh data).
 |
| 05. Data downloaded |
16
If you adjust any column width, they will revert
back to their original width when refreshed unless you right-click anywhere in
the table, choose data Range properties and remove the tick from the box
labelled 'Adjust column width'.
CA. SHIVA SHANKARA R. SHETTY
CHARTERED ACCOUNTANT
Mobile: 91 9035846043
E-Mail: ca.srshetty@icai.org
www.casrshetty228359.in
No comments:
Post a Comment