Copying text from website OR selecting and coping certain text within a cell
 

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

Copying text from website OR selecting and coping certain text within a cell
 

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" wrote in message
oups.com...
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

Copying text from website OR selecting and coping certain text within a cell
 

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" wrote in message
oups.com...
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