How to change SQL queries in Excel using VBA?

Question

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

Michael · Accepted Answer · 2020-12-17 06:41:20Z

1

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

answered Dec 17, 2020 at 6:41

Michael

4,9032 gold badges13 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

DSR Over a year ago

That was perfect but one more thing I have to tap the refresh to see changes. Is there any way I enter a stock symbol in the input box it reflects changes instantly.

DSR Over a year ago

Thanks I have figured it out I have just entered this in the end of script. Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False

DSR Over a year ago

May I know what is the use of & symbol in & InputBox()?

Tim Williams Over a year ago

& is for string concatenation

Collectives™ on Stack Overflow

How to change SQL queries in Excel using VBA?

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related