View Single Post
  #4   Report Post  
Posted to microsoft.public.excel.misc
Steve Yandl[_3_] Steve Yandl[_3_] is offline
external usenet poster
 
Posts: 117
Default How to use the "shell" command?

Tom,

I think this will do the trick for you. Select the text on your web page.
Activate the workbook containing the code. On the worksheet, select the
cell where you want the first line of the data to be placed and then run the
macro. The data selected on the web page will be split at the commas and
the elements of the array will be entered in the same column as your
selected cell.

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

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 = 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