Sticking point Day 2 of VBA, Scraping with "Object" Errors thrown

[mvmas]

So this is my second day of VBA, I normally try not to post unless I am seriously in a hole and can't figure out black from white.

Here is my code:

Sub clickFormButton()
Dim ie As Object
Dim ieform As Variant, button As Object
Dim ieObj As Object 'Appeasing Excel with object variable
Dim ieDoc As Object
Dim ieApp As Object






Set ie = CreateObject("InternetExplorer.Application")


'q=job
'l = location
'td id "resultsCol" is where all listings are stored on every page


'text input search job
myjob = InputBox("Enter type of job, ex. Sales,Manager,etc")
myCity = InputBox("Enter your city you wish to work in, ex. Los Angeles, San Francisco, New York")


With ie


.Visible = True
.navigate ("http://www.indeed.com")


'Wait for entire page to load
While ie.ReadyState <> 4
DoEvents
Wend


'send input values to url form
'Sending job type input to web form
ie.Document.getElementsByName("q").Item.innertext = myjob
'sending input location to web form
ie.Document.getElementsByName("l").Item.innertext = myCity


'Acess the website form
'Set form = ie.Document.getElementsByName("fj").Click


Set button = ie.Document.getElementsByName("fj").Click
'form("fj").submit


'Set ieDoc = ieApp.document
'Set ieform = ieDoc.forms(1)
'For Each ieObj In ieform.Elements
'If ieObj.ClassName = "input_submit" Then
'ieObj.Click
'End If
'Next ieObj


End With


Set ie = Nothing


End Sub

"fj" is the id of the "submit" button on the indeed.com website.

Images of Errors and Picture of Code Debugging with error spot:

Imgur: The most awesome images on the Internet

What am I trying to accomplish?

When the user submits his/her input the code "clicks" the submit or "find jobs" button and displays the relavent job listings.

Then once page has loaded have VBA scrape only the listings in the table id: "resultsCol"
and grab the next 40 pages and then pour into new excel sheets within the same workbook.

For that so far I have created a IE connection string by recording a macro and using Excel's own data grabber tool:

Sub Macro1IndeedImportData()'
' Macro1IndeedImportData Macro
'


    startrow = 1
    For i = 1 To 40
    Sheets.Add after:=Sheets(Sheets.Count)
    If i = 1 Then
    curl = "URL;http://www.indeed.com/jobs?q=data+jobs&l=jacksonville"
    
    End If
    
    Range("G7").Select
    With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;http://www.indeed.com/jobs?q=data+jobs&l=jacksonville", Destination:= _
        Range("$G$7"))
        .CommandType = 0
        .Name = "jobs?q=data+jobs&l=jacksonville"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlSpecifiedTables
        .WebFormatting = xlWebFormattingNone
        .WebTables = """pageContent"""
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With

I am trying to figure out how can I use this existing code paired with the table id of the page, to tell VBA to scrape the listings and then iterate over the next 40 pages and pour the data into columns in new worksheets.

Many thanks to all I know it takes effort to respond and I wholeheartedly appreciate everyone who is present and takes the time to post and respond. Your efforts are really impacting a lot of people.

 

[Norie]

GetElementsByName returns a collection of elements, not a single element.

 

[mvmas]

@Norie Hi so what should be to grab a specific tag from a webpage?

 

[Norie]

You could try getElementByID or, if there is only one element with the name 'fj', you could try something like this.

Set button = ie.Document.getElementsByName("fj")(0)

button.Click

 

[mvmas]

You could try getElementByID or, if there is only one element with the name 'fj', you could try something like this.

Set button = ie.Document.getElementsByName("fj")(0)

button.Click

@Norie Thanks I will give that a try and let you know!

 

[mvmas]

Another roadblock...Im lost...

Imgur: The most awesome images on the Internet

I first tried "fj" (I think that maybe right), but then changed to fj.input_submit which I probably dont need to...because I got an error, so fj is potentially correct.

I already this declared up at the top same as the code in the first post:

Dim button As Object

still dont understand why the code is throwing that error. Thank you again Norie for all your help.

 

[Norie]

I think what I would do would be to look at submitting the form rather clicking the submit/search button on the form.

That's usually straightforward as normally there is only one form on a page and it can easily be accessed using the forms collection.

So give this a try.

ie.Document.forms(0).submit

PS Even if there's more than one form this approach should be pretty easy.

 

[mvmas]

I think what I would do would be to look at submitting the form rather clicking the submit/search button on the form.

That's usually straightforward as normally there is only one form on a page and it can easily be accessed using the forms collection.

So give this a try.

ie.Document.forms(0).submit

PS Even if there's more than one form this approach should be pretty easy.

Wow you are the best it worked like a charm...now its time to trial and error with scraping all the pages.

 

[Norie]

No problems, if you find any suitable jobs for me on there give us a shout.

 

[mvmas]

Hey Norie definitly!

So here is where I am at with the code:

Sub clickFormButton1()Dim ie As Object
Dim ieform As Variant, button As Object
Dim ieObj As Object 'Appeasing Excel with object variable
Dim ieDoc As Object
Dim ieApp As Object












Set ie = CreateObject("InternetExplorer.Application")




'q=job
'l = location
'td id "resultsCol" is where all listings are stored on every page




'text input search job
myjob = InputBox("Enter type of job, ex. Sales,Manager,etc")
myCity = InputBox("Enter your city you wish to work in, ex. Los Angeles, San Francisco, New York")




With ie




.Visible = True
.navigate ("http://www.indeed.com")




'Wait for entire page to load
While ie.ReadyState <> 4
DoEvents
Wend




'send input values to url form
'Sending job type input to web form
ie.document.getElementsByName("q").Item.innertext = myjob
'sending input location to web form
ie.document.getElementsByName("l").Item.innertext = myCity


ie.document.forms(0).submit




'Acess the website form
'Set form = ie.Document.getElementsByName("fj").Click


'Set button = ie.Document.getElementsByName("fj.input_submit")(0)


'button.Click


'Set button = ie.Document.getElementsByName("fj").Click
'form("fj").submit




'Set ieDoc = ieApp.document
'Set ieform = ieDoc.forms(1)
'For Each ieObj In ieform.Elements
'If ieObj.ClassName = "input_submit" Then
'ieObj.Click
'End If
'Next ieObj


[COLOR=#0000cd][B]'Code to read column data of indeed website, scrape it and pour it into Excel[/B][/COLOR]
[COLOR=#0000cd][B]Set TDelements = .document.getElementsbytagname("  row  result")[/B][/COLOR]
[COLOR=#0000cd][B]r = 0[/B][/COLOR]


[COLOR=#0000cd][B]For Each TDelement In TDelements[/B][/COLOR]
[COLOR=#0000cd][B]Data.Range("A1").Offset(r, 0).Value = TDelement.innertext[/B][/COLOR]
[COLOR=#0000cd][B]r = r + 1[/B][/COLOR]
[COLOR=#0000cd][B]Next[/B][/COLOR]

[COLOR=#0000cd][B]End With[/B][/COLOR]
[COLOR=#0000cd][B]Set ie = Nothing[/B][/COLOR]


End Sub

I am trying to figure which "element" to pick and I am pretty sure I use "getElementsByTagName"

Health System Specialist

after the bolded portion is where all the content of the job posting is contained. I tried "OrganicJob, "row result", and even "jobtitle" to try and grab those rows which contained job listings data but I am not seeing any data being downloaded.

I have named the sheet which the data should be coming into as "Data".

Thank you again for all your help.</div>