Hi,

I am sure there is a way to do this but do not know where to start!
I want an excel sheet to update some cells from different values on
different websites every x amount of minutes.

If somebody could point me towards somewhere with some instructions
that would be great.

Thanks,

Keri,
DataImport External DataNew Web Query

NickHK

"keri" wrote in message
oups.com...
Hi,

I am sure there is a way to do this but do not know where to start!
I want an excel sheet to update some cells from different values on
different websites every x amount of minutes.

If somebody could point me towards somewhere with some instructions
that would be great.

Thanks,

Thanks. However I do not know if this is the solution to my problem so
I will explain further.

Currently I have VBA that opens a web page, and using sendkeys submits
data to the page to open another page. It is on this second page that
I automatically want the code to copy some information back into
excel, then close the web page. I can see that the web query works but
can I put this query into my code or is there another way of coding it?

Currently I am using this code;

Sub gettaf()
ActiveWorkbook.FollowHyperlink _
Address:="http://weather.noaa.gov/weather/shorttaf.shtml"
Application.Wait (Now + TimeValue("0:00:15"))

SendKeys "{TAB}{TAB}{TAB}{TAB}{TAB}{TAB}{TAB}{TAB}{TAB}{TAB }{TAB}{TAB}
{TAB}{tab}{TAB}{TAB}{TAB}{TAB}{TAB}{TAB}{TAB}{TAB} {TAB}", True
SendKeys "LEVC", True
SendKeys "~"
Application.Wait (Now + TimeValue("0:00:5"))

MsgBox "Done"

End Sub

Which opens the web page, uses send keys to enter the value required
and submits. I then need to get the data off the next page that is
opened after submitting.

All of the threads I have read use code like below;

Sub gettaf2()

' Prepare to open the web page
Set IE = CreateObject("InternetExplorer.Application")

With IE
.Visible = True
.Navigate "http://weather.noaa.gov/weather/shorttaf.shtml"

' Loop until the page is fully loaded
Do Until Not .Busy
DoEvents
Loop

' Make the desired selections on the web page and click the
submitButton
Set IPF = IE.Document.all.ITEM("CCCC")
IPF.Value = "LEVC"
Set IPF = IE.Document.all.ITEM("SUBMIT")
IPF.Value = "submit"
IPF.Click

' Loop until the page is fully loaded
Do Until Not .Busy
DoEvents
Loop

End With


' Select and copy all of the data from the web page
IE.ExecWB OLECMDID_SELECTALL, OLECMDEXECOPT_DONTPROMPTUSER
IE.ExecWB OLECMDID_COPY, OLECMDEXECOPT_DODEFAULT


' Close the internet explorer application
IE.Quit

End Sub

However this does not work for me because;
1. I am unsure if I am putting the correct names and values of the
items as the page does not enter the value and submit. Some of the
source code is below;

<br<br
<form name="textbox" method="post" action="/cgi-bin/
mgetstaf.pl"<INPUT TYPE="TEXT"
NAME="cccc" SIZE="20" ALIGN="MIDDLE" <BR<INPUT TYPE="SUBMIT"
NAME="Submit"
VALUE="SUBMIT" ALIGN="MIDDLE"
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
<INPUT TYPE="RESET" NAME="Clear" VALUE="CLEAR" ALIGN="MIDDLE"
</form

2. I am unsure what IPF is - and my computer seems to be unsure also -
it does not recognise it.

For info I have references set to Microsoft Internet Controls, HTML
Object Library and XL, v5.0

Thanks,

Sorry for posting lots of times - don't want anyone to be trying to
solve a problem for me that is no longer a problem!

I now have the code below working;

Sub THISONE()
Dim IE
Dim IPF

' Prepare to open the web page
Set IE = CreateObject("InternetExplorer.Application")


With IE
.Visible = True
.Navigate "http://weather.noaa.gov/weather/shorttaf.shtml"


' Loop until the page is fully loaded
Do Until Not .Busy
DoEvents
Loop


' Make the desired selections on the web page and click the
submitButton
Set IPF = IE.Document.all.ITEM("CCCC")
IPF.Value = "LEVC"
Set IPF = IE.Document.all.ITEM("SUBMIT")
IPF.Value = "submit"
IPF.Click


' Loop until the page is fully loaded
Do Until Not .Busy
DoEvents
Loop


End With


' Select and copy all of the data from the web page
IE.ExecWB OLECMDID_SELECTALL, OLECMDEXECOPT_DONTPROMPTUSER
IE.ExecWB OLECMDID_COPY, OLECMDEXECOPT_DODEFAULT


' Close the internet explorer application
IE.Quit
'Sheets("sheet2").Activate
Range("B25").Select
ActiveSheet.PasteSpecial Format:="Unicode Text", link:=False, _
DisplayAsIcon:=False

End Sub

However
1. The paste special errors Run Time error 1004, PasteSpecial Method
of Worksheet class failed. If I paste manually into a cell (using
paste special or just Ctrl & V it works fine)

2. I now need the code to run every 5 minutes from when it is started
until it is stopped.

3. I need to do some error handling incase there is no internet
connection. I am not sure how to word this is the code.