VBA to Follow Hyperlink in Cells and Extract Data from Webpage

[ledl20483037]

Hello all! I want to say thanks in advance, I am a new user of VBA and have exhausted my searching to arrive to this forum board. I am downloading a CSV file with raw data that includes a column with a website in it. My goals are to have a VBA do the following:

Turn the text website address into a hyperlink (completed)

Create a new tab with headings I want to fill in (somewhat completed, it makes two sheets instead of the one I am wanting)

Go to each website in each row of the original raw data tab and pull a total of 16 items off that webpage per row and populate the new sheet that is organized with those 16 line items. I'm thinking I have to get the "inspect ID" for each individual item then create some type of loop to run it through every line of the raw data. (I've gotten to the point to where it will load the first row's hyperlink but I do not know how to pull the data off the web page). I've found several tutorials and websites on scraping but I am not starting from a base internet explorer, each row has a specific website to follow to get the information I am needing.

Any help would be greatly appreciated! Below is the following code I have written.



Sub InsertColumns()
'Insert Column to the left of Column R
Columns("R:R").Insert Shift:=x1ToRight, _
CopyOrigin:=x1FormatFromLeftOrAbove 'or x1FormatFromRightOrBelow
End Sub




Sub AddHeader()
'Insert Heading in New Column R
Worksheets(1).Range("R1").Formula = "Well Details Hyperlink"
End Sub




Sub AddFormula()
'Insert hyperlink formula to new Column R
Range("R2").Formula = "=HYPERLINK(S2)"
End Sub




Sub FillColumn()
'Fills column to last row of data from Cell R2
Dim LastRow As Long
LastRow = Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Range("R2:R" & LastRow).FillDown
End Sub




Sub InsertSheet()
Dim WS As Worksheet
Set WS = Sheets.Add(After:=Sheets(Worksheets.Count))
Sheets.Add.Name = "SWD"
End Sub




Sub AddHeader2()
Worksheets("SWD").Range("A1").Formula = "API Number"
Worksheets("SWD").Range("B1").Formula = "Current Operator"
Worksheets("SWD").Range("C1").Formula = "Well Name"
Worksheets("SWD").Range("D1").Formula = "Well Number"
Worksheets("SWD").Range("E1").Formula = "Well Type"
Worksheets("SWD").Range("F1").Formula = "Well Direction"
Worksheets("SWD").Range("G1").Formula = "Well Status"
Worksheets("SWD").Range("H1").Formula = "Section"
Worksheets("SWD").Range("I1").Formula = "Township"
Worksheets("SWD").Range("J1").Formula = "Range"
Worksheets("SWD").Range("K1").Formula = "OCD Unit Number"
Worksheets("SWD").Range("L1").Formula = "Surface Location"
Worksheets("SWD").Range("M1").Formula = "Bottomhole Location"
Worksheets("SWD").Range("N1").Formula = "Formation"
Worksheets("SWD").Range("O1").Formula = "MD"
Worksheets("SWD").Range("P1").Formula = "TVD"
End Sub


Sub FollowURL()


ActiveWorkbook.FollowHyperlink Address:=Range("R2").Value, NewWindow:=False, AddHistory:=True


End Sub








Sub DoEverything()
InsertColumns
AddHeader
AddFormula
FillColumn
InsertSheet
AddHeader2
Worksheets("SWD").Columns("A:P").AutoFit
FollowURL
End Sub

 

[ledl20483037]

Something I did fail to mention. Some of the information can/will come directly from the raw data sheet. Some of the info has to be pulled from the website because it is not contained in the raw data CSV file.