How to change SQL queries in Excel using VBA?

I have a workbook connected to a database that has stock data. I have around 500 stock symbol data and I fetch them one by one by entering the stock symbol (pink highlighted) in the query as shown below.

All the time I have to open Connection Properties then Definition to change the stock symbol. This process is time-consuming.

I want a textbox in my Ribbon where I insert stock symbol it will change the query.

Here’s my VBA code after I recorded a macro.

Sub Macro2()
'
' Macro2 Macro
'

'

    Range("B6963").Select
    With ActiveWorkbook.Connections("ABC"). _
        OLEDBConnection
        .BackgroundQuery = True
        .CommandText = Array( _
        "Select QuoteDate ,StockSymbol, HighPrice, LowPrice, ClosePrice, Volume  From StockQuotedaily Where StockSYmbol='BRC" _
        , "' Order by Quotedate")
        .CommandType = xlCmdSql
        .Connection = Array( _
        "OLEDB;Provider=SQLOLEDB.1;Password=ABC;Persist Security Info=True;Extended Properties=""DRIVER=SQL Server;SERVER=ABC" _
        , _
        ";UID=sa;APP=Microsoft Office 2013;WSID=ABC;DATABASE=ABC"";Use Procedure for Prepare=1;Auto Translat" _
        , _
        "e=True;Packet Size=4096;Workstation ID=ABC;Use Encryption for Data=False;Tag with column collation when possible=Fal" _
        , "se")
        .RefreshOnFileOpen = False
        .SavePassword = True
        .SourceConnectionFile = ""
        .SourceDataFile = ""
        .ServerCredentialsMethod = xlCredentialsMethodIntegrated
        .AlwaysUseConnectionFile = False
    End With
    With ActiveWorkbook.Connections("ABC")
        .Name = "ABC"
        .Description = ""
    End With
End Sub

Answer

Text boxes in ribbons are really messy. A much easier approach is a popup inputbox. Change the .CommandText to:

Array("Select QuoteDate ,StockSymbol, HighPrice, LowPrice, ClosePrice, Volume From StockQuotedaily Where StockSYmbol='" & InputBox("Stock Symbol"), "' Order by Quotedate")

Advertisement

Answer