Prev: How do i extract worksheet cells with the same contents?
Next: Search and Replace, or Add New Row
From: Tom on 20 Apr 2010 20:55
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 21 Apr 2010 10:06
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" <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 22 Apr 2010 03:00
"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 22 Apr 2010 11:01
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" <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 22 Apr 2010 18:57
"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
>>>>
>>
>>


 |  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