Excel 2016, I have an OLEDB Query linked to SQL Server to return a table. In prior versions I was able to update command text and refresh the table with the following:
Dim strSQL as string
strSQL = worksheets("SomeWorksheet").Range("B6")
.CommandText = strSQL
This is returning subscript out of range and I don't understand why. Connection type in properties is 'OLE DB Query'. I've double checked that the connection name matches my code and have subsequently tried it with other connections to no avail. Any help would be appreciated.