I download approx. 40 pages of data from 2 websites everyday. Is there a way
to do this thru VBA.
--
rac

Workbooks.Open Filename:="http://www.mywebsite.com"

You will need to add the additional url info.
--
Pops Jackson


"rac" wrote:

I download approx. 40 pages of data from 2 websites everyday. Is there a way
to do this thru VBA.
--
rac

Again, it will depend on the specifics.

In most cases, a simple Web Query for each set of information might do
the trick. They can be automated with VBA. In some cases, VBA can
even be avoided by using IQY files.

However, I have a free open-source add-in that I wrote in order to
grab financial information from the web because of little problems I
had with web queries, but it does have several general purpose
functions that may help in your case -- depending on the data you are
going after. The add-in, documentation on its functions, and
templates that show example of the functions' use, can be found in the
files area of this Yahoo group:

http://finance.groups.yahoo.com/group/smf_addin/

In the "Documentation" folder, you may want to look at the
documentation for these three functions:

-- RCHGetHTMLTable()
-- RCHGetTableCell()
-- RCHGetWebData()

The first allows you to extract one or more HTML tables from most web
pages. The second allows you to grab individual table cells from most
web pages. The last allows you to get the HTML source code of a web
page so you can do custom extraction of the data.

On Jun 15, 11:33 am, rac wrote:
I download approx. 40 pages of data from 2 websites everyday. Is there a way
to do this thru VBA.

I have been using VBA for 10 years or so only self taught. Don't know what
IQY files mean. Thanks, Rich
--
rac


"Randy Harmelink" wrote:

Again, it will depend on the specifics.

In most cases, a simple Web Query for each set of information might do
the trick. They can be automated with VBA. In some cases, VBA can
even be avoided by using IQY files.

However, I have a free open-source add-in that I wrote in order to
grab financial information from the web because of little problems I
had with web queries, but it does have several general purpose
functions that may help in your case -- depending on the data you are
going after. The add-in, documentation on its functions, and
templates that show example of the functions' use, can be found in the
files area of this Yahoo group:

http://finance.groups.yahoo.com/group/smf_addin/

In the "Documentation" folder, you may want to look at the
documentation for these three functions:

-- RCHGetHTMLTable()
-- RCHGetTableCell()
-- RCHGetWebData()

The first allows you to extract one or more HTML tables from most web
pages. The second allows you to grab individual table cells from most
web pages. The last allows you to get the HTML source code of a web
page so you can do custom extraction of the data.

On Jun 15, 11:33 am, rac wrote:
I download approx. 40 pages of data from 2 websites everyday. Is there a way
to do this thru VBA.

Here are a couple of examples of EXCEL workbooks using IQY files:

http://ogres-crypt.com/iqy/MSN-10-Year-Summary.xls
http://ogres-crypt.com/iqy/MSN-Financial-Statements.xls
http://ogres-crypt.com/iqy/advfn-ann...cial-query.xls

IQY files can contain the definitions of an EXCEL Web Query *PLUS*
allow you to get the run parameters out of cells -- so if you change
the value of the cell, the Web Query will automatically update.

For example, try this (which is how the first workbook above was
created):

1. Create a new workbook.

2. Put a string of MMM in cell B2 (this is the ticker symbol we'll
use to retrieve data on)

3. Click on cell B4 and execute menu option Data Import External
Data Import Data. When the dialog for "Select Data Source" comes
up, enter the following as the file name and click on "Open":

http://ogres-crypt.com/iqy/MSN-10-Year-Summary.iqy

4. When the "Import Data" dialog come up, click on the "Parameter"
button.

5. Check the box in front of "Get the value from the following Cell,
then click on the text box below it, then click on cell B2, then click
the check box in front of "Refresh automatically when cell value
changes", and then click "OK".

6. Now you should be back on the "Import Data" dialog. Just click
"OK".

After a few seconds, you should see the 10-year summaries of financial
information from MSN, for ticker symbol MMM. If you now go to cell B2
and change it to be IBM, your data should automatically update to
contain the 10-year summaries of financial data for IBM.

Just put a "view-source:" prefix in front of the IQY file address
above in your browser and you should be able to view the contents of
the file. The first and second lines rarely change -- usually always
"WEB" and "1". The third line is a URL -- the "[...]" contents allow
you to specify parameters for the URL on the fly. The fifth line,
usually left off, specifies which tables to extract from the web
page. Basically, the IQY is just specifying the parameters that you
would normally give to the VBA command to add a query. This is what
the one we are working with looked like:

================================================== ================================
WEB
1
http://moneycentral.msn.com/investor...ummary&Symbol=["Symbol",
"Enter Symbol"]

Selection="INCS","BALS"
================================================== ================================

In my version of EXCEL, I can just click on the above IQY file and it
will automatically open it in EXCEL and prompt me for the ticker
parameter. You can try it and see if yours will do the same -- just
enter a ticker symbol when it asks, because it is creating a new
workbook and you won't have a worksheet cell to point it at.

Do a Google search on the following and you should find a lot more
information:

excel "web query" iqy

On Jun 15, 1:29 pm, rac wrote:
I have been using VBA for 10 years or so only self taught. Don't know what
IQY files mean. Thanks, Rich