How to use the "shell" command? [General]

Prev: How do i extract worksheet cells with the same contents?
Next: Search and Replace, or Add New Row

From: Steve Yandl on 23 Apr 2010 10:54

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" <tclimb(a)hotmail.com> wrote in message
news:Iv4An.20819$pv.355(a)news-server.bigpond.net.au...
> "Steve Yandl" <syandl(a)comcast.net> wrote in message
> news:P4SdnZvsuuJN-U3WnZ2dnUVZ_oydnZ2d(a)giganews.com...
>> 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
>
>> 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" <tclimb(a)hotmail.com> wrote in message
>> news:0uSzn.20678$pv.6430(a)news-server.bigpond.net.au...
>>> "Steve Yandl" <syandl(a)comcast.net> wrote in message
>>> news:edmdnfAgnNrTm1LWnZ2dnUVZ_oydnZ2d(a)giganews.com...
>>>> 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" <tclimb(a)hotmail.com> wrote in message
>>>> news:p2szn.20447$pv.19387(a)news-server.bigpond.net.au...
>>>>> 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
>>>>>
>>>
>>>
>
>

From: Tom on 23 Apr 2010 23:31

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

Tom

"Steve Yandl" <syandl(a)comcast.net> wrote in message
news:B_ednUyaQccKKUzWnZ2dnUVZ_h-dnZ2d(a)giganews.com...
> 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" <tclimb(a)hotmail.com> wrote in message
> news:Iv4An.20819$pv.355(a)news-server.bigpond.net.au...
>> "Steve Yandl" <syandl(a)comcast.net> wrote in message
>> news:P4SdnZvsuuJN-U3WnZ2dnUVZ_oydnZ2d(a)giganews.com...
>>> 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
>>
>>> 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" <tclimb(a)hotmail.com> wrote in message
>>> news:0uSzn.20678$pv.6430(a)news-server.bigpond.net.au...
>>>> "Steve Yandl" <syandl(a)comcast.net> wrote in message
>>>> news:edmdnfAgnNrTm1LWnZ2dnUVZ_oydnZ2d(a)giganews.com...
>>>>> 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" <tclimb(a)hotmail.com> wrote in message
>>>>> news:p2szn.20447$pv.19387(a)news-server.bigpond.net.au...
>>>>>> 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
>>>>>>
>>>>
>>>>
>>
>>

From: Tom on 24 Apr 2010 05:31

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-72781/hewlett-packard-hp-compaq-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" <syandl(a)comcast.net> wrote in message
news:B_ednUyaQccKKUzWnZ2dnUVZ_h-dnZ2d(a)giganews.com...
> 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" <tclimb(a)hotmail.com> wrote in message
> news:Iv4An.20819$pv.355(a)news-server.bigpond.net.au...
>> "Steve Yandl" <syandl(a)comcast.net> wrote in message
>> news:P4SdnZvsuuJN-U3WnZ2dnUVZ_oydnZ2d(a)giganews.com...
>>> 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
>>
>>> 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" <tclimb(a)hotmail.com> wrote in message
>>> news:0uSzn.20678$pv.6430(a)news-server.bigpond.net.au...
>>>> "Steve Yandl" <syandl(a)comcast.net> wrote in message
>>>> news:edmdnfAgnNrTm1LWnZ2dnUVZ_oydnZ2d(a)giganews.com...
>>>>> 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" <tclimb(a)hotmail.com> wrote in message
>>>>> news:p2szn.20447$pv.19387(a)news-server.bigpond.net.au...
>>>>>> 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
>>>>>>
>>>>
>>>>
>>
>>

From: Steve Yandl on 24 Apr 2010 16:31

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

"Tom" <tclimb(a)hotmail.com> wrote in message
news:qTyAn.21119$pv.8291(a)news-server.bigpond.net.au...
> 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-72781/hewlett-packard-hp-compaq-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" <syandl(a)comcast.net> wrote in message
> news:B_ednUyaQccKKUzWnZ2dnUVZ_h-dnZ2d(a)giganews.com...
>> 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" <tclimb(a)hotmail.com> wrote in message
>> news:Iv4An.20819$pv.355(a)news-server.bigpond.net.au...
>>> "Steve Yandl" <syandl(a)comcast.net> wrote in message
>>> news:P4SdnZvsuuJN-U3WnZ2dnUVZ_oydnZ2d(a)giganews.com...
>>>> 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
>>>
>>>> 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" <tclimb(a)hotmail.com> wrote in message
>>>> news:0uSzn.20678$pv.6430(a)news-server.bigpond.net.au...
>>>>> "Steve Yandl" <syandl(a)comcast.net> wrote in message
>>>>> news:edmdnfAgnNrTm1LWnZ2dnUVZ_oydnZ2d(a)giganews.com...
>>>>>> 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" <tclimb(a)hotmail.com> wrote in message
>>>>>> news:p2szn.20447$pv.19387(a)news-server.bigpond.net.au...
>>>>>>> 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
>>>>>>>
>>>>>
>>>>>
>>>
>>>
>
>

From: Steve Yandl on 24 Apr 2010 18:34

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

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