如何在记录的Excel-VBA代码中插入变量数据库名称?

Question

下面的代码来自 Excel 2016 中从 SQL Server Express 数据库导入数据期间记录的宏。

Sub Macro2()
ActiveWorkbook.Queries.Add Name:="Query1", Formula:= _
"let" & Chr(13) & "" & Chr(10) & "    Source = Sql.Database(""DESKTOPSQLEXPRESS"", ""2014-All-1"", [Query=""SELECT *#(lf)  FROM [2014-All-1].[dbo].[OtherData]#(lf)#(lf)  WHERE volume > 1""])" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    Source"
Sheets.Add After:=ActiveSheet
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=Query1" _
, Destination:=Range("$A$1")).QueryTable
.CommandType = xlCmdSql
.CommandText = Array("SELECT * FROM [Query1]")
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.ListObject.DisplayName = "Query1"
.Refresh BackgroundQuery:=False
End With
Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
End Sub

我试图通过添加变量MyDB来使数据库名称成为变量，如您所见，它在长代码行中使用了两次。原来这是数据库的名称：2014-All-1

不幸的是，这种方法不起作用。有人知道如何用变量名替换数据库吗？

Sub Macro2()
MyDB = "2014-All-1"
ActiveWorkbook.Queries.Add Name:="Query1", Formula:= _
"let" & Chr(13) & "" & Chr(10) & "    Source = Sql.Database(""DESKTOPSQLEXPRESS"", ""MyDB"", [Query=""SELECT *#(lf)  FROM [MyDB].[dbo].[OtherData]#(lf)#(lf) WHERE volume > 1 ""])" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    Source"
Sheets.Add After:=ActiveSheet
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=Query1" _
, Destination:=Range("$A$1")).QueryTable
.CommandType = xlCmdSql
.CommandText = Array("SELECT * FROM [Query1]")
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.ListObject.DisplayName = "Query1"
.Refresh BackgroundQuery:=False
End With
Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
End Sub

Answer 1

以下是我的做法：

Sub MyMacro()
Dim strMyDB as String
MyDB = "2014-All-1"
Dim strFormulaTemplate as String
strFormulaTemplate = "let" & Chr(13) & "" & Chr(10) & "    Source = Sql.Database(""DESKTOPSQLEXPRESS"", ""@@DBName@@"", [Query=""SELECT *#(lf)  FROM [@@DBName@@].[dbo].[OtherData]#(lf)#(lf)  WHERE volume > 1""])" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    Source"
Dim strFormula as String
strFormula = replace(strFormulaTemplate,"@@DBName@@",strMyDB)
ActiveWorkbook.Queries.Add Name:="Query1", Formula:= strFormula
...
End Sub

如果需要，可以将数据库名称作为参数传递，如下所示：

Sub MyMacro(strMyDB as String)
...

然后使用MyMacro("MyDB")调用宏

Answer 2

尝试修改 "MyDB"： Chr(34) & MyDB & Chr(34)，例如：

"let" & Chr(13) & "" & Chr(10) & "    Source = Sql.Database(""DESKTOPSQLEXPRESS"", " & Chr(34) & MyDB & Chr(34) & ", [Query=""SELECT *#(lf)  FROM [" & Chr(34) & MyDB & Chr(34) & "].[dbo].[OtherData]#(lf)#(lf) WHERE volume > 1 ""])" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    Source"