I keep getting "Compile error: Can't find project or library"
Please help.
Please help.
Code:
Sub RunExtractor()
GetSector
End Sub
Public Function GetSector()
' Start here, get a list of sectors then drill down in the Industry page.
On Error GoTo ErrTrap
Dim nRowYahoo As Integer ' The Yahoo Pg
Dim nRowExcel As Integer
Dim sYahooPage As String
Dim sBaseURL As String
Debug.Print "Sector Started", Now()
sBaseURL = "http://biz.yahoo.com/p/"
sYahooPage = "s_qtou.html"
nRowYahoo = 0
nRowExcel = 2 ' Start at Row 2 instead of 1 so headers can be put in later to show A is the Sector and B is the URL
' Clear out current area. Leave the header row in place if someone wants it labeled
Workbooks("Yahoo-Extractor.xls").Worksheets("Sector").Range("A2:Z15000").Value = ""
' First open the Yahoo page in a new workbook, that has all the Sectors listed.
Workbooks.Open sBaseURL & sYahooPage
' Search the A columns for the text "Click on column heading to sort." to find the place to
' start the extraction at. Will have to cursor down to get the first line, currently 4 lines
With Worksheets(1).Range("A1:A200")
Set s = .Find("Click on column heading to sort.")
If Not s Is Nothing Then ' Found
nRowYahoo = s.Row + 4 ' Add 4 to get to the first item, usually "Basic Materials"
Do
' Get the Sector name and store to the workbook
Workbooks("Yahoo-Extractor.xls").Worksheets("Sector").Range("A" & nRowExcel) = Range("A" & nRowYahoo).Value
' Get the URL and store it to the workbook in the second column
Workbooks("Yahoo-Extractor.xls").Worksheets("Sector").Range("B" & nRowExcel) = Range("A" & nRowYahoo).Hyperlinks.Item(1).Address
' Increment the row counters
nRowYahoo = nRowYahoo + 1
nRowExcel = nRowExcel + 1
' Continue looping until the value in the Yahoo page column 1 is blank, then exit this routine.
Loop While Range("A" & nRowYahoo).Value <> ""
End If
End With
' Close Yahoo workbook
Workbooks(sYahooPage).Close
Exit Function
ErrTrap:
Debug.Print "GetSector", Err.Description, Err.Number
Resume Next
End Function