Posts by thomasyork

    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:


    [VBA]


    Dim strSQL as string


    strSQL = worksheets("SomeWorksheet").Range("B6")
    With ActiveWorkBook.Connections("Connection_Name").OLEDBConnection
    .CommandText = strSQL
    End With


    ActiveWorkBook.Connections("Connection_Name").Refresh


    [/VBA]



    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.