I'm trying to create a macro to fetch some content from a webpage and write the same in an excel file in a customized manner. I've used two identical links from the same website. Here is one of them. I'm interested in three fields Name,Recipe and Ingredients.
The script that I've created can parse the data accordingly. However, I wanna arrange them in an excel file like this.
I've written so far (working flawlessly):
Sub GetAndArrangeData()
Dim HTML As New HTMLDocument, oPost As Object
Dim HTMLDoc As New HTMLDocument, ws As Worksheet
Dim oTitle As Object, oPosts As Object
Dim linklist As Variant, url As Variant
linklist = Array( _
"https://www.chelseasmessyapron.com/avocado-chicken-salad-2/", _
"https://www.chelseasmessyapron.com/caprese-quinoa-salad/" _
)
Set ws = ThisWorkbook.Worksheets("Sheet1")
For Each url In linklist
With CreateObject("MSXML2.XMLHTTP")
.Open "GET", url, False
.setRequestHeader "User-Agent", "Mozilla/5.0 (Windows NT 6.1) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/88.0.4324.104 Safari/537.36"
.send
HTML.body.innerHTML = .responseText
End With
Set oTitle = HTML.querySelector("h1.entry-title")
Debug.Print oTitle.innerText
Set oPost = HTML.querySelectorAll(".cma-recipe-nutrition > .wprm-nutrition-label-container > span[class*='nutrition-container']")
For I = 0 To oPost.Length - 1
HTMLDoc.body.innerHTML = oPost(I).outerHTML
Debug.Print HTMLDoc.querySelector("span.wprm-nutrition-label-text-nutrition-label").innerText
Debug.Print HTMLDoc.querySelector("span[class*='nutrition-value']").innerText
Next I
Set oPosts = HTML.querySelectorAll(".wprm-recipe-block-container")
For I = 0 To oPosts.Length - 1
HTMLDoc.body.innerHTML = oPosts(I).outerHTML
On Error Resume Next
Debug.Print HTMLDoc.querySelector("span.wprm-recipe-details-label").innerText
Debug.Print HTMLDoc.querySelector("span.wprm-recipe-details").innerText
On Error GoTo 0
Next I
Next url
End Sub
How can I write the data in an excel file the way I've shown in the image above?
Btw, this is the result I got in the immediate window:
Avocado Chicken Salad
Calories:
542
Carbohydrates:
30
Protein:
11
Fat:
45
Saturated Fat:
7
Cholesterol:
16
Sodium:
285
Potassium:
687
Fiber:
8
Sugar:
9
Vitamin A:
945
Vitamin C:
19
Calcium:
36
Iron:
1
Course
Cuisine
Keyword
Prep Time
20
Cook Time
15
Total Time
35
Servings
2
Calories
542
Cost
$6.82
Caprese Quinoa Salad
Calories:
375
Carbohydrates:
30
Protein:
11
Fat:
26
Saturated Fat:
4
Cholesterol:
7
Sodium:
73
Potassium:
996
Fiber:
9
Sugar:
7
Vitamin A:
17616
Vitamin C:
32
Calcium:
168
Iron:
4
Course
Cuisine
Keyword
Prep Time
35
Cook Time
25
Chilling Time (Quinoa)
1
Total Time
2
Servings
6
Calories
375
Cost
$6.84