我需要一些幫助才能從網站中提取一些資料。不幸的是需要登錄，所以分享鏈接毫無意義。希望有 HTML 理解的人會直接幫助我。
我正在使用以下代碼來提取網頁的組態檔（見下圖）。
Sub testWebScap()
' Code uses the links from the webpage, create sheets with the customer name and paste all Profile details
Dim oHttp As Object, Html As HTMLDocument
Dim HTMLDoc As New MSHTML.HTMLDocument
Dim HTMLClCol As MSHTML.IHTMLElementCollection
Dim HTMLCl As MSHTML.IHTMLElement
Dim i As Integer, j As Integer
Dim contentPage As String
Set Html = New HTMLDocument
Set oHttp = CreateObject("MSXML2.XMLHTTP")
j = 2
' ClientList sheet contains the webpage link for each customer.
For i = 2 To ThisWorkbook.Sheets("ClientList").Range("A65000").End(xlUp).Row
' Loop through each customer profile webpage
Sheets.Add(After:=Sheets(Sheets.Count)).Name = ThisWorkbook.Sheets("ClientList").Range("A" & i)
contentPage = "https://my.powerdiary.com" & ThisWorkbook.Sheets("ClientList").Range("B" & i)
j = 2
With oHttp
.Open "GET", contentPage, False
.send
sResp = .responseText
End With
HTMLDoc.body.innerHTML = oHttp.responseText
Set HTMLClCol = HTMLDoc.getElementsByClassName("form-control-label col-lg-4 col-12")
' Loop through Label-elements From Salutation to Country but skipping the Middle Name and Telephone Home. Labels copied in Column A
For Each HTMLCl In HTMLClCol
ActiveSheet.Range("A" & j).Value = HTMLCl.innerHTML
j = j 1
Next HTMLCl
Set HTMLClCol = HTMLDoc.getElementsByClassName("form-control-label col-xl-4 col-12")
' Loop through Label-elements From Status to NDIS but skipping the Medicare Ref, Month and Year. Labels copied in Column A
For Each HTMLCl In HTMLClCol
ActiveSheet.Range("A" & j).Value = HTMLCl.innerHTML
j = j 1
Next HTMLCl
Set HTMLClCol = HTMLDoc.getElementsByClassName("form-control")
j = 2
' Loop through All values (including those correspond to the missing labels). Values copied in Column B
For Each HTMLCl In HTMLClCol
ActiveSheet.Range("B" & j).Value = HTMLCl.getAttribute("value")
j = j 1
Next HTMLCl
Next i
End Sub
該代碼部分作業為：
- 我缺少一些標簽元素（檢查資料表影像）。例如無法獲得中間名標簽（缺少的標簽在影像上以紅色字體顯示）。
- 值元素移動一行（我猜代碼在開始時選擇了一個空的但不知道為什么）
由于我無法與您分享網頁，因此我添加了與組態檔資料相對應的 HTML 部分。
<form action="/Profile/33" data-prompt-unsaved="" method="post"> <div class="panel-body p-4">
<div class="row mt10 mb20">
<div class="col-xl-6 col-12 pr-md-4">
<div class="form-group row mb20">
<label class="form-control-label col-lg-4 col-12" for="NameView_Salutation">Salutation</label>
<div class="col-sm-12 col-lg-8">
<select class="form-control" id="NameView_Salutation" name="NameView.Salutation"><option value="">No Salutation</option>
<option value="Mr">Mr</option>
<option value="Ms">Ms</option>
<option value="Mrs">Mrs</option>
<option value="Miss">Miss</option>
<option value="Mx">Mx</option>
<option selected="selected" value="Master">Master</option>
<option value="Dr">Dr</option>
<option value="Prof">Prof</option>
</select>
</div>
</div>
<div class="form-group row mb20">
<label class="form-control-label col-lg-4 col-12" for="NameView_ClientFirstName">Name</label>
<div class="col-lg-4 col-6">
<input class="form-control medicare-first-name" data-val="true" data-val-length="The field First Name must be a string with a maximum length of 100." data-val-length-max="100" data-val-required="The First Name field is required." id="NameView_ClientFirstName" name="NameView.ClientFirstName" placeholder="First Name" type="text" value="TestFName" />
<span class="field-validation-valid" data-valmsg-for="NameView.ClientFirstName" data-valmsg-replace="true"></span>
</div>
<label class="sr-only" for="NameView_ClientMiddleName">Middle Name</label>
<div class="col-lg-4 col-6">
<input class="form-control" data-val="true" data-val-length="The field ClientMiddleName must be a string with a maximum length of 80." data-val-length-max="80" id="NameView_ClientMiddleName" name="NameView.ClientMiddleName" placeholder="Middle Name" type="text" value="TestMName" />
</div>
</div>
<div class="form-group row mb20">
<label class="form-control-label col-lg-4 col-12" for="NameView_ClientLastName">Last Name</label>
<div class="col-sm-12 col-lg-8">
<input class="form-control medicare-last-name" data-val="true" data-val-length="The field ClientLastName must be a string with a maximum length of 80." data-val-length-max="80" id="NameView_ClientLastName" name="NameView.ClientLastName" placeholder="Last Name" type="text" value="TestLName" />
</div>
</div>
<div class="form-group row mb20">
<label class="form-control-label col-lg-4 col-12" for="NameView_PreferredClientName">Preferred Name</label>
<div class="col-sm-12 col-lg-8">
<input class="form-control" data-val="true" data-val-length="The field PreferredClientName must be a string with a maximum length of 200." data-val-length-max="200" id="NameView_PreferredClientName" name="NameView.PreferredClientName" type="text" value="TestPName" />
</div>
</div>
<div class="form-group row mb20" id="clientDateofBirthBlock">
<label class="form-control-label col-lg-4 col-12" for="BirthView_DateOfBirth">Date of Birth</label>
<div class="col-lg-8 col-sm-12 col-12">
<div class="input-group date bs-datepicker">
<input CurrentMenuItem="ClientDetails" autocomplete="off" class="form-control form-control medicare-date-of-birth" data-val="true" data-val-date="The field DateOfBirth must be a date." id="BirthView_DateOfBirth" name="BirthView.DateOfBirth" style="min-width: 93px" type="text" value="03/11/2021" />
<div class="input-group-append input-group-addon">
<span class="input-group-text input-group-addon-gray"><i class="far fa-calendar-alt"></i></span>
</div>
</div>
<span class="field-validation-valid" data-valmsg-for="BirthView.DateOfBirth" data-valmsg-replace="true"></span>
<span class="fsize11 d-inline-block" id="clientAge">
(Age: <span id="ageYears">0</span> yrs, <span id="ageMonths">0</span> mths)
</span>
</div>
</div>
<div class="form-group row mb20">
<label class="form-control-label col-lg-4 col-12" for="BirthView_Gender">Gender</label>
<div class="col-lg-8 col-12">
<select class="form-control" id="BirthView_Gender" name="BirthView.Gender"><option value="">Select...</option>
<option value="0">Female</option>
<option selected="selected" value="1">Male</option>
<option value="2">Other</option>
</select>
</div>
</div>
<script>
//Update client's age
var $clientDob = $('#BirthView_DateOfBirth');
$clientDob.change(function() {
var date = $(this).val();
if (!date) {
$('#clientAge').removeClass("d-inline-block").addClass("d-none");
$('#clientDateofBirthBlock').addClass("mb20").removeClass("mb10");
return;
}
$('#clientAge').removeClass("d-none").addClass("d-inline-block");
$('#clientDateofBirthBlock').removeClass("mb20").addClass("mb10");
ajaxHelper.post('/Client/GetAge', { dob: date }, $clientDob, {
onJsonSuccess: function(response) {
if (response) {
var age = response.data;
$('#ageYears').html(age.years);
$('#ageMonths').html(age.months);
$('#clientAge').show();
}
}
});
});
</script>
<div class="form-group row mb20 mobile-number-group">
<label class="form-control-label col-lg-4 col-12" for="PhoneView_ClientMobile">Mobile Number</label>
<div class="col-sm-12 col-lg-8">
<input class="form-control" data-val="true" data-val-length="The mobile number must be less than 25 characters" data-val-length-max="25" data-val-mobilenumber="" data-val-phonenumberbasic="" id="PhoneView_ClientMobile" name="PhoneView.ClientMobile" type="text" value="123456789" />
<span class="field-validation-valid" data-valmsg-for="PhoneView.ClientMobile" data-valmsg-replace="true"></span>
</div>
</div>
<div class="form-group row mb10">
<label class="form-control-label col-lg-4 col-12" for="PhoneView_WorkPhone">Telephone</label>
<div class="col-lg-4 col-6">
<input class="form-control" data-val="true" data-val-landlineormobilenumber="" data-val-length="The work phone number must be less than 25 characters" data-val-length-max="25" data-val-phonenumberbasic="" id="PhoneView_WorkPhone" name="PhoneView.WorkPhone" placeholder="Work Phone" type="text" value="987654321" />
<span class="fsize11">Work</span>
<span class="field-validation-valid" data-valmsg-for="PhoneView.WorkPhone" data-valmsg-replace="true"></span>
</div>
<div class="col-lg-4 col-6">
<label class="sr-only" for="PhoneView_HomePhone">Home Phone</label>
<input class="form-control" data-val="true" data-val-landlineormobilenumber="" data-val-length="The home phone number must be less than 25 characters" data-val-length-max="25" data-val-phonenumberbasic="" id="PhoneView_HomePhone" name="PhoneView.HomePhone" placeholder="Home Phone" type="text" value="0147258369" />
<span class="fsize11 ">Home</span>
<span class="field-validation-valid" data-valmsg-for="PhoneView.HomePhone" data-valmsg-replace="true"></span>
</div>
</div>
<div class="form-group row mb20">
<label class="form-control-label col-lg-4 col-12" for="PhoneView_ClientEmail">Email</label>
<div class="col-sm-12 col-lg-8">
<input class="form-control" id="PhoneView_ClientEmail" name="PhoneView.ClientEmail" title="Filter by any part of the client's email address." type="text" value="[email protected]" />
<span class="field-validation-valid" data-valmsg-for="PhoneView.ClientEmail" data-valmsg-replace="true"></span>
</div>
<script>
$(function (){
let emailControl = $('#PhoneView_ClientEmail');
emailControl.on('blur', function (){
let trimmed = emailControl.val().trim();
emailControl.val(trimmed);
});
});
</script>
</div>
<div class="form-group row mb20">
<label class="form-control-label col-lg-4 col-12" for="AddressView_Address">Address</label>
<div class="col-sm-12 col-lg-8">
<textarea class="form-control" data-val="true" data-val-maxlength="The field Address must be a string or array type with a maximum length of '255'." data-val-maxlength-max="255" id="AddressView_Address" name="AddressView.Address" rows="2">
1 Address Road</textarea>
</div>
</div>
<div class="form-group row mb20">
<label class="form-control-label col-lg-4 col-12" for="AddressView_Suburb">City</label>
<div class="col-sm-12 col-lg-8">
<input class="form-control" data-val="true" data-val-maxlength="The field Suburb must be a string or array type with a maximum length of '50'." data-val-maxlength-max="50" id="AddressView_Suburb" name="AddressView.Suburb" placeholder="Suburb/Town" type="text" value="TestSuburb" />
</div>
</div>
<div class="form-group row mb20">
<label class="form-control-label col-lg-4 col-12" for="AddressView_Region">State</label>
<div class="col-lg-4 col-6">
<input class="form-control" data-val="true" data-val-maxlength="The field Region must be a string or array type with a maximum length of '50'." data-val-maxlength-max="50" id="AddressView_Region" name="AddressView.Region" placeholder="State" type="text" value="VIC" />
</div>
<div class="col-lg-4 col-6">
<label class="sr-only" for="AddressView_Postcode">Postcode</label>
<input class="form-control" data-val="true" data-val-maxlength="The field Postcode must be a string or array type with a maximum length of '8'." data-val-maxlength-max="8" id="AddressView_Postcode" name="AddressView.Postcode" placeholder="Postcode" type="text" value="1234" />
</div>
</div>
<div class="form-group row mb20">
<label class="form-control-label col-lg-4 col-12">Country</label>
<div class="col-sm-12 col-lg-8">
<select class="form-control" data-val="true" data-val-maxlength="The field Country must be a string or array type with a maximum length of '100'." data-val-maxlength-max="100" id="AddressView_Country" name="AddressView.Country"><option value="">Select a Country...</option>
<option value="AF">Afghanistan</option>
<option selected="selected" value="AU">Australia</option>
<option value="ZW">Zimbabwe</option>
</select>
</div>
</div>
</div>
<div class="col-xl-6 col-12 mb20 pl-md-4">
<div class="form-group row mb20">
<label class="form-control-label col-lg-4 col-12" for="ClientStatusId">Status</label>
<div class="col-sm-12 col-lg-8">
<select class="form-control" data-val="true" data-val-number="The field ClientStatusId must be a number." data-val-required="The ClientStatusId field is required." id="ClientStatusId" name="ClientStatusId"><option value="0"></option>
<option selected="selected" value="1">Active</option>
<option value="4">Closed</option>
<option value="2">Follow Up Required</option>
<option value="3">On Hold</option>
</select>
</div>
</div>
<div class="form-group row mb20">
<label class="form-control-label col-lg-4 col-12" for="ClientTypeId">Client Type</label>
<div class="col-sm-12 col-lg-8">
<select class="form-control" data-val="true" data-val-number="The field ClientTypeId must be a number." id="ClientTypeId" name="ClientTypeId"><option value=""></option>
<option value="53372">Example Client Type 1</option>
<option value="53371">Example Client Type 2</option>
</select>
</div>
</div>
<div class="form-group row mb20">
<label class="form-control-label col-lg-4 col-12" for="FileUnder">File Under</label>
<div class="col-sm-12 col-lg-8">
<input class="form-control" data-val="true" data-val-maxlength="The field FileUnder must be a string or array type with a maximum length of '80'." data-val-maxlength-max="80" id="FileUnder" name="FileUnder" type="text" value="" />
</div>
</div>
<div class="form-group row mb20">
<label class="form-control-label col-lg-4 col-12" for="ClientCompanyNameView_CompanyName">Business Name</label>
<div class="col-sm-12 col-lg-8">
<input class="form-control" data-val="true" data-val-length="The field Business Name must be a string with a maximum length of 100." data-val-length-max="100" id="ClientCompanyNameView_CompanyName" name="ClientCompanyNameView.CompanyName" type="text" value="TestBName" />
<span class="field-validation-valid" data-valmsg-for="ClientCompanyNameView.CompanyName" data-valmsg-replace="true"></span>
</div>
</div>
<div class="form-group row mb20">
<label class="form-control-label col-lg-4 col-12" for="Occupation">Occupation</label>
<div class="col-sm-12 col-lg-8">
<input class="form-control" data-val="true" data-val-maxlength="Maximum 100 characters allowed." data-val-maxlength-max="100" id="Occupation" name="Occupation" type="text" value="TestOcc" />
</div>
</div>
<div class="form-group row mb20">
<label class="form-control-label col-lg-4 col-12" for="HowHeardId">How Client Heard About Us</label>
<div class="col-sm-12 col-lg-8">
<select class="form-control" data-val="true" data-val-number="The field HowHeardId must be a number." id="HowHeardId" name="HowHeardId"><option value=""></option>
<option selected="selected" value="1">Internet</option>
<option value="2">Referral</option>
</select>
</div>
</div>
<div class="form-group row mb25">
<label class="form-control-label col-lg-4 col-12" for="DateAdded">Date Added</label>
<div class="col-sm-12 col-lg-8">
6/11/2021
</div>
</div>
<input class="medicare-client-id" data-val="true" data-val-number="The field Id must be a number." data-val-required="The Id field is required." id="ClientBase_Id" name="ClientBase.Id" type="hidden" value="9083410" />
<div><input data-val="true" data-val-number="The field Id must be a number." data-val-required="The Id field is required." id="ClientInsurerCardViews_0__Id" name="ClientInsurerCardViews[0].Id" type="hidden" value="4836399" /></div>
<div class="form-group row mb5">
<label class="form-control-label col-xl-4 col-12" for="ClientInsurerCardViews_0__Number">Medicare</label>
<div class="col-xl-8 col-12">
<div class="row">
<div class="col-xl-7 col-sm-8 col-12">
<div class="row">
<div class="col-9">
<input class="form-control" id="ClientInsurerCardViews_0__Number" name="ClientInsurerCardViews[0].Number" placeholder="Number" style="min-width:107px;" type="text" value="0123456789" />
<span class="fsize11">Medicare Number </span>
</div>
<div class="col-3 pl0">
<label class="sr-only" for="ClientInsurerCardViews_0__Irn">Reference</label>
<input class="form-control small-number pr0" id="ClientInsurerCardViews_0__Irn" name="ClientInsurerCardViews[0].Irn" placeholder="IRN" style="margin-left: 10px;" type="text" value="1" />
<span class="fsize11" style="margin-left: 10px;">Ref</span>
</div>
</div>
</div>
<div class="col-xl-5 col-sm-4 col-12">
<div class="row">
<div class="col-6">
<label class="sr-only" for="ClientInsurerCardViews_0__Month">Month</label>
<input class="form-control small-number" data-val="true" data-val-number="The field Month must be a number." id="ClientInsurerCardViews_0__Month" name="ClientInsurerCardViews[0].Month" placeholder="MM" type="text" value="1" />
<span class="fsize11">Month</span>
</div>
<div class="col-6 pl0">
<label class="sr-only" for="ClientInsurerCardViews_0__Year">Year</label>
<input class="form-control small-number" data-val="true" data-val-number="The field Year must be a number." id="ClientInsurerCardViews_0__Year" name="ClientInsurerCardViews[0].Year" placeholder="YYYY" type="text" value="2025" />
<span class="fsize11">Year</span>
</div>
</div>
</div>
</div>
</div>
</div>
<div class="form-group row mb20">
<div class="col text-right">
<div data-toggle="tooltip" title="" data-original-title="">
<button class="btn btn-success btn-xs shadow validate-medicare-button" type="button" id="validate-medicare-button-id-0">
<i classname="fa fa-check fa-margin"></i> Validate Medicare Data
</button>
</div>
</div>
</div>
<script type="text/javascript">
$(function () {
$("#validate-medicare-button-id-0").click(function (e) {
e.preventDefault();
var $this = $(this);
$this.setDisabled(true);
// medicare-client-id is declared outside
var clientId = $(".medicare-client-id").val();
var number = $("#ClientInsurerCardViews_0__Number").val();
var irn = $("#ClientInsurerCardViews_0__Irn").val();
var dob = $(".medicare-date-of-birth").val();
var lastName = $(".medicare-last-name").val();
var firstName = $(".medicare-first-name").val();
PD.Client.Medicare.validateMedicareNumber(clientId,
number,
irn,
dob,
firstName,
lastName,
function() { $this.setDisabled(false); });
});
$("#ClientInsurerCardViews_0__Month").rules("add", { digits: true, range: [1, 12] });
var currentYear = (new Date()).getFullYear();
$("#ClientInsurerCardViews_0__Year").rules("add", { digits: true, range: [currentYear - 10, currentYear 20] });
$("#ClientInsurerCardViews_0__Irn").rules("add", { digits: true, range: [1, 99] });
$("#ClientInsurerCardViews_0__Number").rules("add", { digits: true, minlength: 10, maxlength: 10 });
});
</script>
<div><input data-val="true" data-val-number="The field Id must be a number." data-val-required="The Id field is required." id="ClientInsurerCardViews_1__Id" name="ClientInsurerCardViews[1].Id" type="hidden" value="4836404" /></div>
<div class="form-group row mb5">
<label class="form-control-label col-xl-4 col-12" for="ClientInsurerCardViews_1__Number">NDIS</label>
<div class="col-xl-8 col-12">
<div class="row">
<div class="col-xl-7 col-sm-8 col-12">
<div class="row">
<div class="col-9">
<input class="form-control" id="ClientInsurerCardViews_1__Number" name="ClientInsurerCardViews[1].Number" placeholder="Number" style="min-width:107px;" type="text" value="231353151351" />
<span class="fsize11">Number</span>
</div>
</div>
</div>
</div>
</div>
</div>
<input data-val="true" data-val-number="The field ClientNumber must be a number." data-val-required="The ClientNumber field is required." id="clientNumber" name="clientNumber" type="hidden" value="33" />
</div>
</div>
<div class="row">
<div class="col-12">
<div class="form-group row">
<label class="form-control-label col-12" for="ClientNotes">Additional Comments</label>
<div class="col-12">
<textarea class="form-control" data-val="true" data-val-maxlength="The field ClientNotes must be a string or array type with a maximum length of '60000'." data-val-maxlength-max="60000" id="ClientNotes" name="ClientNotes" rows="7">
</textarea>
</div>
</div>
</div>
</div>
<div class="row">
<div class="col-12 mt-4">
<button type="submit" id="btnSubmit" class="float-right btn btn-lg btn-green save validate-form action-form"><i class="fa fa-check fa-margin"></i>SAVE CHANGES</button>
</div>
</div>
</div>
</form></div>uj5u.com熱心網友回復：
嘗試
Option Explicit
Sub testWebScrape()
' read html from file
Dim fso As Object, ts As Object, s As String
Set fso = CreateObject("Scripting.Filesystemobject")
Set ts = fso.openTextFile("C:\temp\so\1.html")
s = ts.readall
ts.Close
Dim HTMLClCol As MSHTML.IHTMLElementCollection
Dim HTMLCl As MSHTML.IHTMLElement
Dim j As Integer, sLabel As String
Dim HTMLDoc As New MSHTML.HTMLDocument
Dim e1 As MSHTML.IHTMLElement, e2 As MSHTML.IHTMLElement, e3 As MSHTML.IHTMLElement
HTMLDoc.body.innerHTML = s
' results
j = 1
Sheet1.Cells.Clear
Sheet1.Columns("A:B").NumberFormat = "@" ' text
Set HTMLClCol = HTMLDoc.getElementsByTagName("div")
For Each HTMLCl In HTMLClCol
For Each e1 In HTMLCl.Children
If e1.className Like "form-group row mb[125]*" Then
For Each e2 In e1.all
If e2.tagName = "LABEL" Then
j = j 1
Sheet1.Cells(j, 1) = e2.innerText
sLabel = e2.innerText
ElseIf e2.tagName = "OPTION" And e2.getAttribute("Selected") = "selected" Then
Sheet1.Cells(j, 2) = e2.innerText
ElseIf e2.className Like "form-control*" Then
Sheet1.Cells(j, 2) = e2.getAttribute("value")
ElseIf e1.className = "form-group row mb25" Then
Sheet1.Cells(j, 2) = Format(CDate(e2.innerText), "dd mmm yyyy")
End If
Next
End If
Next
Next
MsgBox "Done"
End Sub
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/351351.html
上一篇：取消保護指定路徑中的Excel作業簿并重繪作業簿連接，然后保護作業簿
下一篇：根據數字為另一列著色行