Prev: How do i extract worksheet cells with the same contents?
Next: Search and Replace, or Add New Row
From: Tom on 25 Apr 2010 22:54
"Steve Yandl" <syandl(a)comcast.net> wrote in message
news:CfOdnbzgULjazUnWnZ2dnUVZ_uydnZ2d(a)giganews.com...
> Tom,
>
> That's why I asked you what you meant by "default" web page in your
> original question.
I meant the URL or the active web page whose data I had selected for
copying.
There could be other web pages open but not active and whose URL addresses
all began with http://www.graysonline


> There can be multiple web pages open at any time and the subroutine look
> at all of them. The results produced are from the last page the sub
> looked at (results overwrite previous results, even if the new result is a
> blank).
Would refreshing the wanted page makes it the last page for the sub to look
at?
If not closing it down and reopening it again would that make it the last
page?
Any other suggestions?


Not only that, the shell windows collection includes
> Windows Explorer windows as well as Internet Explorer windows.
>
> The line
> If InStr(objIE.LocationURL, "http") Then
> checks to make sure the url of the window contains the letters "http" and
> ignores windows that don't include that combination.
If there are other http://www.graysonline pages that are open, for your sub
to target
a specific URL like say,
http://www.graysonline.com/lot/0001-180621/toshiba-satellite-p500-024-notebook
do I have to replace "http" by the above full address?

> That's how I avoided having the routine fail by trying to parse any open
> Explorer windows (like desktop and start button which are pretty much
> always open). You can expand what is contained inside the double quotes
> in that line of code so that the sub will ignore IE windows not open to
> the graysonline website.
>
>
> Steve
>
>
>
> "Tom" <tclimb(a)hotmail.com> wrote in message
> news:3XOAn.21267$pv.8961(a)news-server.bigpond.net.au...
>> One last question. Often I have other web pages open. I found that your
>> macro did not know the data of the web page I had selected when I ran it.
>> But if I closed all the others it had no problem. If several web pages
>> are open is there a way (e.g. include the URL in your codes) for it to
>> associate with the one whose data has been selected?
>>
>> Tom
>>


From: Steve Yandl on 26 Apr 2010 16:18
Try the following. This will cycle through all the web pages like the
original but it will append the text data to the existing data rather than
overwrite........Steve

Sub ParseOpenWebPage()

Dim strDoc As String
Dim a As Integer
Dim b As Integer

a = Selection.Row
b = Selection.Column

Set objShell = CreateObject("Shell.Application")
Set objShellWindows = objShell.Windows

If objShellWindows.Count = 0 Then
Set objShellWindows = Nothing
Set objShell = Nothing
Exit Sub
End If

strDoc = ""

For i = 0 To objShellWindows.Count - 1
Set objIE = objShellWindows.Item(i)
If InStr(objIE.LocationURL, "http") Then
Set objSelection = objIE.Document.Selection.CreateRange()
strDoc = strDoc & objSelection.Text & ","
End If
Next i

If Len(strDoc) > 0 Then
arrText = Split(strDoc, ",")
For r = 0 To UBound(arrText)
Cells(a + r, b).Value = arrText(r)
Next r
End If

Set objIE = Nothing
Set objShellWindows = Nothing
Set objShell = Nothing
End Sub




From: Steve Yandl on 26 Apr 2010 16:43
Tom,

Give the approach below a try. It looks at all the open pages but appends
each selection to the previous collected text rather than overwrite.

Sub ParseOpenWebPage()

Dim strDoc As String
Dim a As Integer
Dim b As Integer

a = Selection.Row
b = Selection.Column

Set objShell = CreateObject("Shell.Application")
Set objShellWindows = objShell.Windows

If objShellWindows.Count = 0 Then
Set objShellWindows = Nothing
Set objShell = Nothing
Exit Sub
End If

strDoc = ""

For i = 0 To objShellWindows.Count - 1
Set objIE = objShellWindows.Item(i)
If InStr(objIE.LocationURL, "http") Then
Set objSelection = objIE.Document.Selection.CreateRange()
strDoc = strDoc & objSelection.Text & ","
End If
Next i

If Len(strDoc) > 0 Then
arrText = Split(strDoc, ",")
For r = 0 To UBound(arrText)
Cells(a + r, b).Value = arrText(r)
Next r
End If

Set objIE = Nothing
Set objShellWindows = Nothing
Set objShell = Nothing
End Sub



From: Tom on 26 Apr 2010 19:52
Steve your codes are cycling well through all the open pages and pick only
the page with the selected text to capture and display its contents down
column 1. I must congratulate and thank you for your efforts to have done it
in just a few steps. Is VB your background rather than VBA?

I still use some programs written with Excel4 macros and like to replace
them with the current Excel 11(?) macros but have not been able to get a
solution from anyone. I wonder if you can help. The procedure is to read a
list of names starting with A1 column1 of Document1 then goes and finds the
exact name in Document2 which is a large database of person information. It
then pauses to allow the user to check, edit or extract any information
he/she likes before continuing when a pause button is clicked. It goes back
to Document1 and reads the next name down the list. This is repeated until
the whole list is read. Any help is much appreciated.

"Steve Yandl" <syandl(a)comcast.net> wrote in message
news:4qadnRHqlLZ8Z0jWnZ2dnUVZ_gednZ2d(a)giganews.com...
> Tom,
>
> Give the approach below a try. It looks at all the open pages but appends
> each selection to the previous collected text rather than overwrite.
>
> Sub ParseOpenWebPage()
>
> Dim strDoc As String
> Dim a As Integer
> Dim b As Integer
>
> a = Selection.Row
> b = Selection.Column
>
> Set objShell = CreateObject("Shell.Application")
> Set objShellWindows = objShell.Windows
>
> If objShellWindows.Count = 0 Then
> Set objShellWindows = Nothing
> Set objShell = Nothing
> Exit Sub
> End If
>
> strDoc = ""
>
> For i = 0 To objShellWindows.Count - 1
> Set objIE = objShellWindows.Item(i)
> If InStr(objIE.LocationURL, "http") Then
> Set objSelection = objIE.Document.Selection.CreateRange()
> strDoc = strDoc & objSelection.Text & ","
> End If
> Next i
>
> If Len(strDoc) > 0 Then
> arrText = Split(strDoc, ",")
> For r = 0 To UBound(arrText)
> Cells(a + r, b).Value = arrText(r)
> Next r
> End If
>
> Set objIE = Nothing
> Set objShellWindows = Nothing
> Set objShell = Nothing
> End Sub
>
>
>


First  |  Prev  | 
Pages: 1 2 3 4
Prev: How do i extract worksheet cells with the same contents?
Next: Search and Replace, or Add New Row