K
keri
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
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