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
|