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.

 

[mvmas]

Ok that didnt work out so well let me post the code without tags otherwise it will just display the "link"

Imgur: The most awesome images on the Internet

 

[mvmas]

Alright so I have reached far enough where I have married two subroutines together, one to visit the page and submit the form and the other to "pseudo download" the data. I am trying to figure out how to pass the userinput the user types into the input that Excel asks for into the url that the IEConnection string uses.

Thanks again!

[SIZE=5]second subroutine code *ConnectionString&Download[/SIZE]
Sub Scrape(myJob As String, myCity As String)Dim url As String 
Dim myJob As String 
Dim myCity As String


myJob = myJob.InputBox 
myCity= myCity.InputBox


'
' Macro1IndeedWebScrape Macro
'


Worksheets("DataDump").Select
Worksheets("DataDump").Activate


   
    With ActiveSheet.QueryTables.Add(Connection:= _
        [COLOR=#0000ff]"URL;http://www.indeed.com/jobs?q=[B]myJobValueShouldGoHere[/B]&l=[B]myCityValueShouldGoHere[/B]"[/COLOR], Destination:= _
        Range("$A$1"))
        'CommandType= 0 not needed since we are not making a OLEDB Query
        .Name = "jobs?q=myJobl=myCity"
        .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


End Sub

[SIZE=5]First Subroutine code  *userinput[/SIZE]
Sub IndeedDownloader()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




End With




Set ie = Nothing




End Sub

 

[mvmas]

Passing input from user into URL and submitting form

I am wondering if the URL could be split in two pieces. But I am wondering if that will just over complicate things....

 

[mvmas]

Re: Passing input from user into URL and submitting form

Sub Macro1IEConnectionString()'


[COLOR=#0000ff][B]strJobName = InputBox("Please enter the Job Name")
strJobLocation = InputBox("Please enter the Job Location ")[/B][/COLOR]
'
    ActiveWorkbook.Worksheets.Add
    With ActiveSheet.QueryTables.Add(Connection:= _
       [COLOR=#0000ff][B] "URL;http://www.indeed.com/jobs?q=&l=" & strJobName & strJobLocation, _
        Destination:=Range("$A$1"))[/B][/COLOR]
        '.CommandType = 0
        .Name = "jobs?q=data+analyst&l=Jacksonville%2C+FL"
        .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
End Sub

Ok so after much falling I have managed to split up the URL and add in parameters as input box's where it asks the user for the job name and job location. The end result is that this code "grabs data" but when it goes to throw it in the Excel sheet everything is empty. If anyone could guide me with this it will be greatly appreciated!