我有一个工作簿连接到一个包含股票数据的数据库。我有大约 500 个股票代码数据,我通过在查询中输入股票代码(粉红色突出显示)来一一获取它们,如下所示。
我一直都必须打开连接属性然后定义来更改股票代码。这个过程非常耗时。
我想在我的功能区中插入一个文本框,在其中插入股票代码,它将更改查询。
这是我录制宏后的 VBA 代码。
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
丝带中的文本框真的很乱。一个更简单的方法是弹出输入框。将 .CommandText 更改为:
Array("Select QuoteDate ,StockSymbol, HighPrice, LowPrice, ClosePrice, Volume From StockQuotedaily Where StockSYmbol='" & InputBox("Stock Symbol"), "' Order by Quotedate")
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句