Home VBA web scrape REFERENCE
Reply: 1

VBA web scrape REFERENCE

josephkane
1#
josephkane Published in 2018-01-12 21:11:38Z

After navigating to the website, I have a list of names in a spreadsheet I need to populate into the search field, how do I reference the spreadsheet in the macro?

from the Navigate command, I have the Url, it asks for first name, last name which i have populated in the spreadsheet.

How do I reference the cells from the spread sheet?:

first_name= **A1** &last_name= **B1** &state=TX&country_code=US

something similar to this, with a loop statement? A1 B1 as example

K.Davis
2#
K.Davis Reply to 2018-01-12 21:47:56Z

This simple instruction should do what you need.

Code:

Sub GrabName()

    Dim ws As Worksheet, cel As Range, retVal As String, tRows As Long
    Set ws = ThisWorkbook.Worksheets(1)
    tRows = ws.Cells(ws.Rows.Count, 1).End(xlUp).row

    Dim r As Long
    For r = 1 To tRows

        retVal = "first_name=" & ws.Cells(r, 1)
        retVal = retVal & "$last_name=" & ws.Cells(r, 2)
        retVal = retVal & "&state=TX&country_code=US"
        retVal = Replace(retVal, " ", "%20")

        MsgBox retVal

    Next r

End Sub

Notice that I included a replacement for any spaces " " to change to %20 as URLs typically do not like spaces in them. You can remove if you don't need it.

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.317462 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO