Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
retrieve data from an .aspx web page
Hi,
I'm using Excel 2000 on Windows XP. I'm attempting to retrieve data from http://www.backshellworld.com/crossreference.aspx. I copied the code below, and I'm trying to modify it to select the dropdown lists, but I'm missing something. Are there any suggestions? Thanks, Dan Case "ElectroAdapter" If itm.tagname = "Script" Then State = "GetData" End If Case "GetData" If itm.tagname = "H2" Then 'get next section of data State = "FindB2" End If Select Case itm.tagname Case "DIV" colCount = 4 'start each row in column D RowCount = RowCount + 1 Case "SPAN" Cells(RowCount, colCount) = itm.innertext colCount = colCount + 1 End Select End Select Next itm IE.Quit End Sub Sub Dump(document) With Sheets("dump") .Cells.ClearContents RowCount = 1 For Each itm In document.all .Range("A" & RowCount) = itm.tagname .Range("B" & RowCount) = itm.className .Range("C" & RowCount) = itm.ID .Range("D" & RowCount) = Left(itm.innertext, 1024) RowCount = RowCount + 1 Next itm End With End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
retrieve data from an .aspx web page
You are modifying my code!!! I recognize my style. Use the ID=dd2 to get
the object Sub BackShellWorld() URL = "http://www.backshellworld.com/crossreference.aspx" Set IE = CreateObject("InternetExplorer.Application") IE.Visible = True Set IE2 = CreateObject("InternetExplorer.Application") IE2.Visible = True 'get web page IE.Navigate2 URL Do While IE.readystate < 4 Or _ IE.busy = True DoEvents Loop Call Dump(IE) Set SelectComp = IE.document.getelementbyid("dd2") For Each Comp In SelectComp MsgBox (Comp.innertext) Next Comp End Sub Sub Dump(IE) 'test code With Sheets("Dump") .Cells.ClearContents RowCount = 1 For Each itm In IE.document.all .Range("A" & RowCount) = itm.tagname .Range("B" & RowCount) = itm.ID .Range("C" & RowCount) = itm.classname .Range("D" & RowCount) = Left(itm.innertext, 1024) RowCount = RowCount + 1 Next itm End With End Sub "dan dungan" wrote: Hi, I'm using Excel 2000 on Windows XP. I'm attempting to retrieve data from http://www.backshellworld.com/crossreference.aspx. I copied the code below, and I'm trying to modify it to select the dropdown lists, but I'm missing something. Are there any suggestions? Thanks, Dan Case "ElectroAdapter" If itm.tagname = "Script" Then State = "GetData" End If Case "GetData" If itm.tagname = "H2" Then 'get next section of data State = "FindB2" End If Select Case itm.tagname Case "DIV" colCount = 4 'start each row in column D RowCount = RowCount + 1 Case "SPAN" Cells(RowCount, colCount) = itm.innertext colCount = colCount + 1 End Select End Select Next itm IE.Quit End Sub Sub Dump(document) With Sheets("dump") .Cells.ClearContents RowCount = 1 For Each itm In document.all .Range("A" & RowCount) = itm.tagname .Range("B" & RowCount) = itm.className .Range("C" & RowCount) = itm.ID .Range("D" & RowCount) = Left(itm.innertext, 1024) RowCount = RowCount + 1 Next itm End With End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
retrieve data from an .aspx web page
Hi Joel,
Please pardon me for not acknowledging your authorship in my request. Here's my experience with your code and some questions: 1. I notice two instances of IE. I'm wondering the purpose of opening two instances. Set IE = CreateObject("InternetExplorer.Application") IE.Visible = True Set IE2 = CreateObject("InternetExplorer.Application") IE2.Visible = True 2. I'm not clear what to do with The Call Dump(IE) section. If I click "Ok" on the message boxes, the "Select Company" drop down is not selected. Then 4 more message boxes are activated--Glenair, ElectroAdapter, etc. I click the OK button each time, but no data is written to the "Dump" sheet. If I select the company name as the Message box directs, the "Call Dump" code returns an error Run-time error '70': Permission Denied. No line of code is highlighted that I can tell. 3. I'm not seeing the results I expected. I am hoping to see a Glenair Series and it's corresponding Amphenol Series. There is another level deeper where the site generates an entire Amphenol part number. I don't know if this level is accessible. 4. The code does return data, but I don't understand the data. For example: the following data is written to cell D2: Thanks for your time and expertise, Dan __________________________________________________ _ Cross Reference Non- Environmental Backshell Environmental Backshell Non Environmental EMI/RFI Backshell Environmental EMI/RFI Backshell Shrink Boot Adapter Shrink Boot Nut Band Lock Adaptor Crimp Adaptor/Ring Backshell Quick Clamp Strain Relief Back NutAngle Profile Coupling Styles Cross Reference: Select Company : Select Company Glenair SunBank ElectroAdapter Military Select Series : Home | Company | Products | Backshell Designer | Custom Design | Cross Reference | Qualifications | Market Segments | Contact Info | Feedback Copyright © 2007 BackShellWorld.com, All Rights Reserved. Read our Privacy Policy, Site Developed by Harisoft, Interactive Integrators. 3. While I'm stepping The code |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
retrieve data from an .aspx web page
Answer: The 2nd instance was from another macro that I forgot to delete 1. I notice two instances of IE. I'm wondering the purpose of opening two instances. Set IE = CreateObject("InternetExplorer.Application") IE.Visible = True Set IE2 = CreateObject("InternetExplorer.Application") IE2.Visible = True Answer: The dump is something I use for debugging. It is not necessary. the dump requires a worksheet called "Dump" 2. I'm not clear what to do with The Call Dump(IE) section. The code doesn't select the object, jst displays the contents of the object. didn't know what you need the object for. If I click "Ok" on the message boxes, the "Select Company" drop down is not selected. Then 4 more message boxes are activated--Glenair, ElectroAdapter, etc. I click the OK button each time, but no data is written to the "Dump" sheet. Answer : The macro isn't interactive with the webpage. If I select the company name as the Message box directs, the "Call Dump" code returns an error Run-time error '70': Permission Denied. No line of code is highlighted that I can tell. Answer : Once you naviagate to another webpage the VBA code doesn't recognize the new object. the VBA code would have to perform the action. 3. I'm not seeing the results I expected. I am hoping to see a Glenair Series and it's corresponding Amphenol Series. There is another level deeper where the site generates an entire Amphenol part number. I don't know if this level is accessible. Anwer : The webpage object has a property "Document" and the another property below the document called all. The dump code is putting 4 of the all poperties on the worksheet Column A : The Tagname property Column B : The ID property Column C : The classname property Column D : the 1st 1024 characters of the innertext property. Excel creates an error when the string size gets too long There are more properties if you add itm to the watch window you will see additional items. 4. The code does return data, but I don't understand the data. For example: the following data is written to cell D2: You sould look at the source code of the webpage by going to the IE menu View - Source or Tools - Developer tools F12 I don't know what you wanted to do and just got you started. I assumed if you need more details you would ask. It is hard sometimes from one question to know a persons skill level. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
retrieve data from an .aspx web page
Thanks for your comments Joel. I'll study this and see
if I can express some effective questions. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
retrieve data from an .aspx web page
Hi Joel:
Answer : Once you naviagate to another webpage the VBA code doesn't recognize the new object. *the VBA code would have to perform the action. Question 1: How would I select the dropdown box and choose one of the items on the list? Anwer : The webpage object has a property "Document" and the another property below the document called all. *The dump code is putting 4 of the all poperties on the worksheet There are more properties if you add itm to the watch window you will see additional items. Question 2: I've never used the "Watch window". So, I added itm to the watch window which shows: expression Value itm <expression not defined in context Type Context Empty Sheet1.BackshellWorld And I received a message: Object doesn't support this action. I don't see what line of code generated the error. Thanks for any insight. Dan |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I retrieve a page number in Excel? | Excel Worksheet Functions | |||
Retrieve data from company intranet page | Excel Programming | |||
I want to present and excel document on a .aspx page | Excel Programming | |||
Passing ClientCertificate data from excel to asp/aspx | Excel Programming | |||
ASPX can't change PageSetup property of Chart | Excel Programming |