We have been running this Macro for years now but on Wednesday it stopped working with the following errors
I have tried to find what could have caused this but am at a dead end
please see the code below and the line where I am getting the error
can anybody point me in the right direction - much appreciated
==================
Sub MainSub()
' 1. Retrieve exchange rates from xe.com
' 2. Insert/Update the exchange rates in Sage ERP
Dim xmlObject As Object
Dim htmlDocumentObject As Object
Dim dataTable As Object
Set xmlObject = CreateObject("MSXML2.XMLHTTP")
With xmlObject
Call .Open("GET", "http://www.xe.com", False)
Call .send
If (.Status = 200) Then
' create HTML file and populate with response
Set htmlDocumentObject = CreateObject("HTMLFILE")
htmlDocumentObject.Open
htmlDocumentObject.write .responseText
htmlDocumentObject.Close
' set the table data
Set dataTable = htmlDocumentObject.getElementById("xRatesBxTable")
Dim r As Integer, i As Integer
' set up array to hold currency codes
Dim a As Variant
a = Array("USD", "EUR", "GBP", "INR", "AUD", "CAD", "ZAR", "NZD", "JPY")
' create dictionaries to hold rates
Dim USD, EUR, GBP
Set USD = CreateObject("Scripting.Dictionary")
Set EUR = CreateObject("Scripting.Dictionary")
Set GBP = CreateObject("Scripting.Dictionary")
' populate dictionaries
r = 1
For i = 2 To dataTable.Rows.Item(r).Cells.Length - 1
USD.Add a(i - 2), dataTable.Rows.Item(r).Cells.Item(i).innerText
Next
r = 3
For i = 2 To dataTable.Rows.Item(r).Cells.Length - 1
EUR.Add a(i - 2), dataTable.Rows.Item(r).Cells.Item(i).innerText
Next
r = 5
For i = 2 To dataTable.Rows.Item(r).Cells.Length - 1
GBP.Add a(i - 2), dataTable.Rows.Item(r).Cells.Item(i).innerText
Next