Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Coping text using Formulas - | Excel Discussion (Misc queries) | |||
Questions on copying from one sheet to the other and coping every other cell. | Excel Discussion (Misc queries) | |||
I need shortcut in Excel for coping text only and not the entire c | Excel Discussion (Misc queries) | |||
Formatting text in a a cell with selecting it. | Excel Programming | |||
Copying text from a text box into a cell on another sheet | Excel Programming |