I'm using the following macro to scrape a DropBox web page for a string and compare it to a stored value within a worksheet to determine if an update for the workbook is available. This all works on Windows but I get an error when I try and dim a HtmlDocument object or use createobject("MSXML2.XMLHTTP") or (not shown)createobject("htmlfile"). What other options do I have if these don't work?
Code:
Private Function Update_Date() As Boolean
Dim Path As String, Update As Double, DD As Byte, WinHttpReq As Object, FileN As String, Update_Range As Range, _
Partial_P As String, [COLOR=#b22222]html As New HTMLDocument,[/COLOR] STR_AR() As String, X As Byte, File_Type As String
Set Update_Range = Variable_Sheet.ListObjects("Saved_Variables"). _
DataBodyRange.Find("Update", LookIn:=xlValues, lookat:=xlWhole).Offset(0, 1)
File_Type = Update_Range.Offset(1, 0).Value2
FileN = "Date_Check.txt"
X = Application.Match(File_Type, Array("L", "D", "T"), 0) - 1
Partial_P = Replace("https://www.dropbox.com/s/7lwhmhul1pqxbf9/Date_Check.txt?dl=0", _
"www.dropbox.com", "dl.dropboxusercontent.com")
[COLOR=#a52a2a]Set WinHttpReq = CreateObject("MSXML2.XMLHTTP")[/COLOR]
With WinHttpReq
.Open "GET", Partial_P, False 'File is a URL/web page: False means that it has to make the connection before moving on
.send
html.Body.innerHTML = .responseText
End With
If Round(Update_Range.Value2, 10) < Round(Split(html.Body.FirstChild.Data, ",")(X), 10) Then Update_Date = True
end function
Last edited:
