VBA update command text in OLEDB Query

  • 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.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!