How to use the "shell" command?

[Tom]

Hi,

How can I use "shell" to move from Excel to the current (default) web page
to copy some comma delimited text data, then open Notepad and save it there?
I then can open the saved text in Excel with the captured text displayed as
column data and rearrange it. Thanks for any help.

TIA
Tom

 

[Steve Yandl]

Tom,

You're adding steps you don't need. For example, you don't need the
intermediate step of opening Notepad to retrieve delimited text from a web
page to a worksheet.

When you refer to "current (default) web page", do you mean the Internet
Explorer home page or some page already in an open IE window or something
else?


Steve Yandl

 

[Tom]

Tom,

You're adding steps you don't need. For example, you don't need the
intermediate step of opening Notepad to retrieve delimited text from a web
page to a worksheet.

When you refer to "current (default) web page", do you mean the Internet
Explorer home page or some page already in an open IE window or something
else?

Whatever web page that is currently open. Or to use your description, "some
page already in an open IE window".
Here is an example of the data I last captured. It relates to the
specifications of one of the family of computers:
Sony VAIO FW BluRay Notebook VGNFW56GFB, 16.4-inch, 16:9 Real Wide LCD,
Intel Core 2 Duo Processor T9600 (2.8 GHz), 16.4` wide (WXGA++: 1600 x 900)
TFT colour display, 500GB HDD (Serial ATA 5400rpm), 4GB DDR2 SDRAM, Bluray
Disk Drive, Intel High Definition Audio compatible, 3D audio (Direct Sound
3D support), Built-in stereo speakers, Built-in monaural microphone, Video =
512MB ATI Mobility Radeon HD 4650 Graphics, Network = 10Base-T/100Base-TX /
1000Base-T, Bluetooth standard version 2.1+EDR, Wireless LAN: IEEE
802.11a/b/g/Draft n, Built-In Camera: MOTION EYE Camera: Effective Pixels:
1280x1024, Interfaces: USB 2.0 x3, 4-pin i.Link (IEEE 1394) S400 x1, HDMI
x1, ExpressCard /34 x1, RJ45 Ethernet x1, Headphone Jack x1, Microphone Jack
x1, Monitor connector (VGA, D-SUB 15 pin) x1, Memory Stick Standard/Duo Slot
x1, SD/MMC Card Slot x1, Windows 7 Home Premium 64-bit.
After I have opened the text file in Excel, they become column data. My
macro then rearranges them along a single column as follows:
Sony VAIO FW BluRay Notebook VGNFW56GFB
16.4-inch
16:9 Real Wide LCD
Intel Core 2 Duo Processor T9600 (2.8 GHz)
16.4` wide (WXGA++: 1600 x 900) TFT colour display
500GB HDD (Serial ATA 5400rpm)
4GB DDR2 SDRAM
Blueray Disk Drive
Intel High Definition Audio compatible
3D audio (Direct Sound 3D support)
Built-in stereo speakers
Built-in monaural microphone
Video = 512MB ATI Mobility Radeon HD 4650 Graphics
Network = 10Base-T/100Base-TX / 1000Base-T
Bluetooth standard version 2.1+EDR
Wireless LAN: IEEE 802.11a/b/g/Draft n
Built-In Camera: MOTION EYE Camera: Effective Pixels: 1280x1024
Interfaces: USB 2.0 x3
4-pin i.Link (IEEE 1394) S400 x1
HDMI x1,
ExpressCard /34 x1,
RJ45 Ethernet x1,
Headphone Jack x1,
Microphone Jack x1,
Monitor connector (VGA D-SUB 15 pin) x1,
Memory Stick Standard/Duo Slot x1,
SD/MMC Card Slot x1,
Windows 7 Home Premium 64-bit

Following on say every 4 columns away on the right of the spreadsheet would
be the specifications of another 2 or 3 more computers.
When the informations are displayed side by side, it is easier to compare
their properties. What I hope to avoid is having to go outside of Excel to
grab the data. I somehow think that the command "shell" can simplify the
task.

 

[Steve Yandl]

Tom,

You will need to alter this a bit but it should give you a start. For
testing, I assumed the text being grabbed was the inner text of the document
body for the open web page. If you're copying the text from some frame or
text box you will need to alter this.

In parsing the returned text string, I split the line up considering vbCrLf
as the delimiter; you will want to change that to comma. I also had the
result deposited in column A beginning at A1 and I suspect you'll want to
change that for your situation.

'---------------------------------
Sub ParseOpenWebPage()

Dim strDoc As String

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
strDoc = objIE.Document.Body.InnerText
End If
Next i

If Len(strDoc) > 0 Then
arrText = Split(strDoc, vbCrLf)
For r = 1 To UBound(arrText)
Cells(r, 1).Value = arrText(r)
Next r
End If

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


'---------------------------------

Steve Yandl

 

[Tom]

Tom,

You will need to alter this a bit but it should give you a start. For
testing, I assumed the text being grabbed was the inner text of the
document body for the open web page.

That's right. If it's a text box what has to be replaced?

If you're copying the text from some frame or text box you will need to
alter this.
In parsing the returned text string, I split the line up considering
vbCrLf as the delimiter; you will want to change that to comma.

I assume it also works for Tab, Semicolon and Space?
What other delimiters can also be used?

I also had the result deposited in column A beginning at A1

Can you prompt the user first to choose a location before dumping the data?
Lastly, at which point do I start to run this macro?
Much obliged for your help.

Tom

 

[Steve Yandl]

Tom,

Without having the web page, it's tough to give you the specific way to
retrieve the text. It really depends on how many areas the web designer
built into the page where there might be text. One option is to have your
user select the text to be worked on, then activate the Excel workbook and
run the macro. In that case, changing the line:
strDoc = objIE.Document.Body.InnerText
to the two lines
Set objSelection = objIE.Document.Selection.CreateRange()
strDoc = objSelection.Text
results in the routine retrieving and working with the selected text on the
web page.

As far as delimiters go, you can use anything you want with "Split". It
creates a 0 based array of strings. If you want something other than the
carriage return line feed, you change
arrText = Split(strDoc, vbCrLf)
to
arrText = Split(strDoc, ",")
for a comma delimiter, or
arrText = Split(strDoc, " ")
for a space delimiter, etc.

You can certainly ask your user for a start point to plant the retrieved
data. I generally have the routine locate the last cell containing data in
either a column or row and have my routines start placing results at the
next available cell which goes a long way toward keeping your user from
messing up previously retrieved data. It really depends on what your
ultimate goal is with this project.


Steve Yandl

 

[Tom]

You have cleared up all my doubts. Your codes are doing a good job.
Thanks Steve.

Tom

 

[Tom]

Oops! Meant to say. Your codes look like they're going to do a good job.
However, upon running them after selecting the text,

"Hewlett-Packard, HP Compaq 6710b (GL061PA#ABG), 15`` Laptop, , 1 X Intel(R)
Core(TM)2 Duo CPU T7100 @ 1.80GHz, 2048MB RAM, 80GB SATA HDD, 0 KB Mobile
Integrated Graphics Controller, 82801H (ICH8 Family) HD Audio Controller, 24
Speed MATSHITADVD-RAM UJ-861H, NetLink BCM5787M Gigabit Ethernet PCI
Express, PRO/Wireless 3945ABG Network Connection, No Operating System, (B),
5 USB Ports, 1 Firewire ports, AC Adapter (Serial No: CNU7450N49)
(44981-6)PROMO063"

from the web page,
http://www.graysonline.com/lot/0008...ompaq-6710b-gl061pa-abg-15-laptop-1-x-intel-r

I was surprised to find that nothing was happening. Can you figure out why,
Steve?

 

[Steve Yandl]

Tom,

These web page operations often require a bit of detective work to discover
how to extract what you want (and only what you want). I'll experiment a
bit later today.

Will you be doing all extractions from this particular web site, only
targeting different PC configurations?

Ultimately, how do you want the information presented on the worksheet?
Specifically, do you want the stats in separate rows with each unit in a
different column, or perhaps listed in a single column, one after the other
or something else?

One option that is fairly certain to work would be adding the extra step of
copying the selected text to the clipboard and then the macro could simply
grab the text from clipboard, clean it up and arrange it.

Steve

 

[Steve Yandl]

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

 

[Tom]

Tom,

These web page operations often require a bit of detective work to
discover how to extract what you want (and only what you want). I'll
experiment a bit later today.

Will you be doing all extractions from this particular web site, only
targeting different PC configurations?

This particular web site will expire in two days time. Immediately after
grabbing
the data from one web site, I then move on to the next similar web site but
the data
I will grab are the same, all comma limited.

Ultimately, how do you want the information presented on the worksheet?
Specifically, do you want the stats in separate rows with each unit in a
different column, or perhaps listed in a single column, one after the
other or something else?

From the first web site, the stats are displayed columnwise. On each time I
can choose which column to dump the data. So, each set of stats is displayed
column by column and looking horizontally across I can compare their similar
properties

One option that is fairly certain to work would be adding the extra step
of copying the selected text to the clipboard and then the macro could
simply grab the text from clipboard, clean it up and arrange it.

That sounds like what I'm currently doing except that I have to use Windows
function to save the data in a text file, then click a button in Excel
whereupon it asks me where I want to dump it. I select a spot and click the
paste symbol and it dumps the data there columnwise.

What I'm doing is to compare different similar products that are being
offered at this auction site http://www.graysonline.com . It can be about
computers or other products. The specifications for different products may
not always be displayed as comma delimited entities. Some of them have a
space followed by a line feed or something else. So, I hope to develop
different procedures attached to different buttons for them.

 

[Tom]

Thanks Steve. That's perfect. What needs to be modified if the data
displayed after the heading "Description" has a carriage return like in this
page?
http://www.graysonline.com/lot/0002-180115/hewlett-packard

 

[Tom]

I meant to add that the way I handle data that has a carriage return is to
save it as a text file, then open it as a "fixed-with" in Excel. The macro
is much shorter as the data is already arranged columnwise.

 

[Tom]

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

 

[Steve Yandl]

Tom,

That's why I asked you what you meant by "default" web page in your original
question. 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). 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. 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]

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?

 

[Steve Yandl]

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

 

[Tom]

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.