retrieve data from an .aspx web page

[dan dungan]

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

 

[Joel]

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]

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

 

[Joel]

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.

 

[dan dungan]

Thanks for your comments Joel. I'll study this and see
if I can express some effective questions.

 

[dan dungan]

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