Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to use the "shell" command?
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to use the "shell" command?
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" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to use the "shell" command?
"Steve Yandl" wrote in message
... 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" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to use the "shell" command?
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-...ewlett-packard "Steve Yandl" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to use the "shell" command?
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. "Steve Yandl" wrote in message ... 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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to use the "shell" command?
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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to use the "shell" command?
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" wrote in message ... 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 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to use the "shell" command?
"Steve Yandl" wrote in message
... 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-...0-024-notebook do I have to replace "http" by the above full address? 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" wrote in message ... 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 |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to use the "shell" command?
Try the following. This will cycle through all the web pages like the
original but it will append the text data to the existing data rather than overwrite........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 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 |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to use the "shell" command?
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 |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to use the "shell" command?
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. "Steve Yandl" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
Need command line to change "#DIV/0!" into a "0" (zero) | Excel Discussion (Misc queries) | |||
change "true" and "false" to "availble" and "out of stock" | Excel Worksheet Functions | |||
Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next | New Users to Excel | |||
inserting a conditional "go to" command on a excel "if" function | Excel Worksheet Functions |