tgibbs
01-02-2014, 03:40 PM
Microsoft Excel 2010, Internet Explorer 9, on a Windows 7 computer:
I am trying to pull the hyperlink address and jump text from a very simple website that consists of an ordered list <ol> contaning company name(this is a hyperlink that links to their website) and if they are hiring(hyperlink to companies website). Then it would output the links into an empty worksheet.
Here is the web page and the source code:
NOTE: number 4 on the list, 20x200 has 2 links in the source code "20x200" is a hyperlink and "(hiring)" is also a hyperlink
11030
I'm creating a macro that will:
1. Access the webpage from excel (Solved)
2. Import the ordered list into an array (Biggest Problem)
3. Parse the href element and it's target info(1 href if they aren't hiring, 2 if they are hiring)
4. output the links into a generic sheet,
A. Column A would have all the companies names irrespective if they are hiring or not.
B. Column B would have only the companies that are hiring.
Here is the Macro so far:
Sub findCompanyLinks()
Dim ie As Object
Set ie = CreateObject("InternetExplorer.Application")
With ie
ie.Visible = False
ie.Navigate URL
' Don't show window
ie.Visible = False
'Wait until IE is done loading page
Do While ie.Busy
Application.StatusBar = "Downloading information, lease wait..."
DoEvents
Loop
' Make a string from IE content
Set mdoc = ie.Document
hrefdata = mdoc.getElementsByTagName("li")
'hrefData = mdoc.links()
End With
End Sub
Thanks in advance for your help. Really the main question I have is how to access the href and target info for each list item and pull it into an array of some sort. Then I am confident I can export the array into excel.
I am trying to pull the hyperlink address and jump text from a very simple website that consists of an ordered list <ol> contaning company name(this is a hyperlink that links to their website) and if they are hiring(hyperlink to companies website). Then it would output the links into an empty worksheet.
Here is the web page and the source code:
NOTE: number 4 on the list, 20x200 has 2 links in the source code "20x200" is a hyperlink and "(hiring)" is also a hyperlink
11030
I'm creating a macro that will:
1. Access the webpage from excel (Solved)
2. Import the ordered list into an array (Biggest Problem)
3. Parse the href element and it's target info(1 href if they aren't hiring, 2 if they are hiring)
4. output the links into a generic sheet,
A. Column A would have all the companies names irrespective if they are hiring or not.
B. Column B would have only the companies that are hiring.
Here is the Macro so far:
Sub findCompanyLinks()
Dim ie As Object
Set ie = CreateObject("InternetExplorer.Application")
With ie
ie.Visible = False
ie.Navigate URL
' Don't show window
ie.Visible = False
'Wait until IE is done loading page
Do While ie.Busy
Application.StatusBar = "Downloading information, lease wait..."
DoEvents
Loop
' Make a string from IE content
Set mdoc = ie.Document
hrefdata = mdoc.getElementsByTagName("li")
'hrefData = mdoc.links()
End With
End Sub
Thanks in advance for your help. Really the main question I have is how to access the href and target info for each list item and pull it into an array of some sort. Then I am confident I can export the array into excel.