VBA Update Command Text in OLEDB Query

[tyork]

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")

With ActiveWorkBook.Connections("Connection_Name").OLEDBConnection
        .CommandText = strSQL
End With

ActiveWorkBook.Connections("Connection_Name").Refresh

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.