EXCEL 自定义函数之公历转农历，VBA通过爬虫实现

        逛excelhome论坛时，有网友问到如何将公历自动转为农历。趁傍晚有点空闲时间，练一练手，特此记录，以便后期使用。

效果：

代码：

Function nongli(gongli_date)
    '函数说明：通过爬虫，得到农历信息，未使用正则。
    Dim HttpReq As Object
    Dim datas, url, gongli_nian, gongli_yue, gongli_ri, flag1, flag2, tmp As String
    Dim pos1, pos2 As Integer '用于记录位置信息
    '1.获取年月日
    gongli_nian = Year(gongli_date)
    gongli_yue = Month(gongli_date)
    gongli_ri = Day(gongli_date)
    '2.设置爬虫
    Set HttpReq = CreateObject("MSXML2.XMLHTTP.6.0")
    url = "https://gonglinongli.bmcx.com/"  '网址
    datas = "gongli_nian=" & gongli_nian & "&gongli_yue=" & Right("0" & gongli_yue, 2) & "&gongli_ri=" & Right("0" & gongli_ri, 2) '参数
    HttpReq.Open "Post", url, False
    HttpReq.setRequestHeader "Content-Length", Len(datas)
    HttpReq.setRequestHeader "CONTENT-TYPE", "application/x-www-form-urlencoded; charset=utf-8"
    HttpReq.send datas
    If HttpReq.Status = 200 Then
        tmp = HttpReq.responseText
    End If
    '3.清理数据，拿到农历信息。注：因为正则需要添加库，有的同事电脑上不能直接用。因此，这里未使用正则，看起来一点都不高大上。
    flag1 = "<td bgcolor=" & Chr(34) & "#F5F5F5" & Chr(34) & " align=" & Chr(34) & "center" & Chr(34) & ">农历</td>"
    flag2 = "<td bgcolor=" & Chr(34) & "#F5F5F5" & Chr(34) & " align=" & Chr(34) & "center" & Chr(34) & ">生肖</td>"
    pos1 = InStr(tmp, flag1)
    pos2 = InStr(tmp, flag2)
    tmp = Mid(tmp, pos1 + Len(flag1) + 62, pos2 - pos1 - Len(flag2) - 81)
    pos1 = InStr(tmp, "<div")
    tmp = Mid(tmp, 1, pos1 - 1)
    tmp = Replace(tmp, " ", "")
    nongli = tmp
End Function

感谢这位宋哥大侠的文章。

【VBA研究】用XMLHTTP的Post功能抓取数据_vba 网抓post无法返回想要的数据_宋哥的博客-CSDN博客https://blog.csdn.net/iamlaosong/article/details/102914771写在最后：或许是我没操作对。CSDN好像挺看不起VBA的，代码块里面跟B有并的，就只有VB.net；在文章标签里面，也找到VBA。