Hi,

Apologies for the re-post but I was putting lots of problems on one
page.

My macro opens a web page, submits data and therefore opens a second
webpage. On the second web page there is some data I wish to retrieve
into my spreadsheet.

I tried copying the whole page and pasting into my sheet in VBA,
however this constantly produced an error when pasting. I then found
this code;

ActiveSheet.cells(1, "A").Value = IE.Document.body.innerText

However this pastes a huge amount of text into 1 cell and I do not
know how to select just the text I need to copy and paste elsewhere.

So;

As a solution I either need a way (that works!) of pasting the whole
sheet into my spreadsheet or a way to select some text (always starts
with the same 3 letters but can be variable length) from the contents
of one cell and copy them.

My code is below;

ub GETTAF()
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
Sheets("sheet2").Select
ActiveSheet.cells(1, "A").Value = IE.Document.body.innerText

' Close the internet explorer application
With IE
.Visible = True
End With
IE.Quit


Call PASTETAF

End Sub

keri,

This will find "TAF", and give you the next two lines that start with TAF:

Dim myStr As String
myStr = IE.Document.body.innerText
myStr = Mid(myStr, InStr(1, myStr, "TAF"), Len(myStr))
myStr = Left(myStr, InStr(InStr(1, myStr, Chr(13)) + 1, myStr, Chr(13)))
ActiveSheet.Cells(1, "A").Value = myStr


Replace your line

ActiveSheet.cells(1, "A").Value = IE.Document.body.innerText

with the above code.


HTH,
Bernie
MS Excel MVP


"keri" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Hi,
>
> Apologies for the re-post but I was putting lots of problems on one
> page.
>
> My macro opens a web page, submits data and therefore opens a second
> webpage. On the second web page there is some data I wish to retrieve
> into my spreadsheet.
>
> I tried copying the whole page and pasting into my sheet in VBA,
> however this constantly produced an error when pasting. I then found
> this code;
>
> ActiveSheet.cells(1, "A").Value = IE.Document.body.innerText
>
> However this pastes a huge amount of text into 1 cell and I do not
> know how to select just the text I need to copy and paste elsewhere.
>
> So;
>
> As a solution I either need a way (that works!) of pasting the whole
> sheet into my spreadsheet or a way to select some text (always starts
> with the same 3 letters but can be variable length) from the contents
> of one cell and copy them.
>
> My code is below;
>
> ub GETTAF()
> 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
> Sheets("sheet2").Select
> ActiveSheet.cells(1, "A").Value = IE.Document.body.innerText
>
> ' Close the internet explorer application
> With IE
> .Visible = True
> End With
> IE.Quit
>
>
> Call PASTETAF
>
> End Sub
>

Try this:

'************
Option Explicit


Sub GETTAF()
Dim IE


' Prepare to open the web page
Set IE = CreateObject("InternetExplorer.Application")
With IE
.Visible = True
.Navigate "http://weather.noaa.gov/weather/shorttaf.shtml"

Do Until Not .Busy
DoEvents
Loop

.document.all("cccc").Value = "LEVC"
.document.all("SUBMIT").Click

Do Until Not .Busy
DoEvents
Loop

'TAF value is in a <pre> tag - get content
Dim pre
Set pre = .document.getElementsByTagname("PRE")
If pre.Length <> 0 Then
MsgBox pre(0).innerText
Else
MsgBox "Not found"
End If

.Quit
End With

End Sub
'***************

Tim




"keri" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Hi,
>
> Apologies for the re-post but I was putting lots of problems on one
> page.
>
> My macro opens a web page, submits data and therefore opens a second
> webpage. On the second web page there is some data I wish to retrieve
> into my spreadsheet.
>
> I tried copying the whole page and pasting into my sheet in VBA,
> however this constantly produced an error when pasting. I then found
> this code;
>
> ActiveSheet.cells(1, "A").Value = IE.Document.body.innerText
>
> However this pastes a huge amount of text into 1 cell and I do not
> know how to select just the text I need to copy and paste elsewhere.
>
> So;
>
> As a solution I either need a way (that works!) of pasting the whole
> sheet into my spreadsheet or a way to select some text (always starts
> with the same 3 letters but can be variable length) from the contents
> of one cell and copy them.
>
> My code is below;
>
> ub GETTAF()
> 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
> Sheets("sheet2").Select
> ActiveSheet.cells(1, "A").Value = IE.Document.body.innerText
>
> ' Close the internet explorer application
> With IE
> .Visible = True
> End With
> IE.Quit
>
>
> Call PASTETAF
>
> End Sub
>