Hallo zusammen,
ich befasse mich seid Kurzem mit dem Thema Web Scraping. Habe einige Videos und Bücher/ Beispiele im Internet angeschaut, leider schaffe ich es dennoch nicht mit Hilfe von VBA Excel die Daten einer Homepage automatisch zu ziehen. :/
Login über VBA Excel auf die Homepage funktioniert. Beim HTML Text auslesen habe ich jetzt Probleme...
(Entschuldigt mich, wenn Fehler drin sind, wie gesagt ich bin neu oder Anfänger)
'show text of HTML document returned
Set html = ie.document
Dim myfile As String
myfile = Application.DefaultFilePath & "\Ranking.txt"
Set rng = Selection
Open myfile For Output As #1
Write #1, HTML_string
Close #1
HTML_string = html.DocumentElement.innerHTML
Do While InStr(HTML_string, "products-ratings-vue-page")
HTML_string = ie.document.DocumentElement.innerHTML
Application.StatusBar = "Trying to open @ Ranking ..."
DoEvents
Loop
'reset status bar
Application.StatusBar = ""
ScrapeSitetoString = HTML_string
End Function
Function ParseTableEntry(HTML_string As String) As String
Dim score As Integer
Dim pos_score As Long
Dim Time As Integer
'Washing'
Length = Len(HTML_string)
pos_Washing = InStr(HTML_string, """attributeId"":915")
HTML_string = Right(HTML_string, Length - pos_Washing)
endpos_Washing = InStr(HTML_string, """")
washing = Left(HTML_string, endpos_Washing - 1)
Wie kann ich z.B. die fettdedruckten Kategorien und deren Werte (siehe unten den Ausschnitt aus einem HTML Text) in eine Exceltabelle einfügen bzw. wie müssten die Befehle aussehen?
Tabelle:
- in Spalte A (Marke) "brandName": "LG"
- in Spalte B (Washing) "attributeId":915 mit dem Wert "value: 4"
- in Spalte C (Drying)"attributeId":7447 mit dem Wert "value": 3"
Vllt kann mir hier jemand weiterhelfen. Bin auf jeden Fall für jeden Tipp dankbar!
Danke.
Hier der Ausschnitt aus dem HTML Text
{"_id":392902,"modelName":"LDP6797ST","slugName":"lg-ldp6797st-392902","slugModelName":"ldp6797st","defaultMpn":"LDP6797ST","defaultUpc":"048231022156","asin":"B077ZGFNW3","overallScoreDescription":"Based mainly on washing, it also factors in noise, energy and water use, loading, and more. The displayed score is out of a total of 100 points.","overallDisplayScore":66,"price":950,"priceDescription":"An approximate retail price.","brandId":158113,"brandName":"LG","brandSlugName":"lg","productGroupTypeId":6,"productGroupTypeName":"SUBCATEGORY","productGroupClassificationId":1,"productGroupClassificationName":"PRODUCTS","productGroupId":33347,"productGroupName":"24-Inch","testStateId":1,"testStateName":"Tested","modelAvailabilityId":1,"modelAvailabilityName":"Available","productGroupHierarchy":{"franchiseId":28967,"franchiseName":"Appliances","franchiseSlugName":"appliances","franchiseCuGroupId":2288,"subFranchiseId":28970,"subFranchiseName":"Kitchen appliances","subFranchiseSlugName":"kitchen-appliances","subFranchiseCuGroupId":2291,"superCategoryId":28979,"superCategoryName":"Dishwashers","superCategorySlugName":"dishwashers","superCategorySingularName":"Dishwasher","superCategoryCuGroupId":2477,"categoryId":28687,"categoryName":"Dishwashers","categorySlugName":"dishwasher","categoryCuGroupId":268,"subCategoryId":33347,"subCategoryName":"24-Inch","subCategorySlugName":"24-inch","subCategoryCuGroupId":3451},"expertReview":......
[{"attributeId":915,"attributeTypeId":1,"attributeTypeName":"TEST_RESULT","sortOrder":2,"name":"Washing","displayName":"Washing","description":"Assessed by running the sensor, normal or equivalent cycle to clean a full load of dishes with baked-on food.","attributeDataTypeId":3,"attributeDataTypeName":"numeric-rating-score","isProductGroupMainAttribute":true,"isAttributeArchived":false,"isFilterSuppressed":false,"value":4,"value10Pt":7,"ratingsBlobScale":"10pt"},{"attributeId":7447,"attributeTypeId":1,"attributeTypeName":"TEST_RESULT","sortOrder":3,"name":"Drying","displayName":"Drying","attributeDataTypeId":3,"attributeDataTypeName":"numeric-rating-score","isProductGroupMainAttribute":true,"isAttributeArchived":false,"isFilterSuppressed":false,"value":3,"ratingsBlobScale":"5pt"},{"attributeId":912,"attributeTypeId":1,"attributeTypeName":"TEST_RESULT","sortOrder":4,"name":"Energy use","displayName":"Energy use","attributeDataTypeId":3,"attributeDataTypeName":"numeric-rating-score","isProductGroupMainAttribute":true,"isAttributeArchived":false,"isFilterSuppressed":false,"value":5,"value10Pt":9,"ratingsBlobScale":"10pt"},......
|