mysql - 从 UserDSN 或 ODBC 文件创建 "Workbook.Connections("MyDBx").ODBCConnection"

Question

Windows 10 Pro 计算机上的 Excel 2016:我可以打开工作簿并从

DATA / From Other Sources / Data Connection Wizard / odbc DSN

我在其中选择我的用户 DSN 并提供其他信息。

然后它创建 VBA 代码以在我的计算机和外部 SQL 数据库之间建立连接。我之前已经为此连接定义了一个 UserDSN。

如果我记录这个过程，我会得到这样的结果:

With ActiveWorkbook.Connections("MyDBx").ODBCConnection
  .BackgroundQuery = True
  .CommandType = xlCmdSql
  .Connection = "ODBC;DSN=SQL2;"
  .RefreshOnFileOpen = False
  .SavePassword = False
  .SourceConnectionFile = "D:\MyDocs\My Data Sources\Mydbx.odc"
  .SourceDataFile = ""
  .ServerCredentialsMethod = xlCredentialsMethodIntegrated
  .AlwaysUseConnectionFile = False
End With
ActiveWorkbook.Worksheets.Add
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:="ODBC;DSN=SQL2;" _
, Destination:=Range("$A$1")).QueryTable
  .CommandText = Array("SELECT * FROM `MyDBx`.`tablea`")
  .RowNumbers = False
  .FillAdjacentFormulas = False
  .PreserveFormatting = True
  .RefreshOnFileOpen = False
  .BackgroundQuery = True
  .RefreshStyle = xlInsertDeleteCells
  .SavePassword = False
  .SaveData = True
  .AdjustColumnWidth = True
  .RefreshPeriod = 0
  .PreserveColumnInfo = True
  .SourceConnectionFile = "D:\MyDocs\My Data Sources\MyDBx.odc"
  .Refresh BackgroundQuery:=False
End With

这行得通，我可以使用 VBA 代码来分析和绘制数据。但是，如果我删除上面创建的工作表然后尝试重新运行宏，它将在语句上失败

With ActiveWorkbook.Connections("MyDBx").ODBCConnection

当我删除上面的工作表时还有一个警告，要删除的工作表包含一个查询。

如何在宏中捕获/创建代码以允许我的宏使用上述数据连接在新/空白工作簿中运行。

显然，我可以保留工作表并始终在该工作簿中运行宏，但不能在另一个工作簿中运行。

Answer 1

请尝试像这样一个对象一个对象地构建它:

Private Sub NewWorkbookWithODBCConnection()
    Dim myWorkBook As Workbook
    Dim myWorkbookConnection As WorkbookConnection
    Dim myWorksheet As Worksheet
    Dim myQuerytable As QueryTable

    Set myWorkBook = Workbooks.Add

    Set myWorkbookConnection = myWorkBook.Connections.Add2( _
        Name:="MyDBx", _
        Description:="Whatever", _
        ConnectionString:="ODBC;DSN=SQL2;", _
        CommandText:="")

    With myWorkbookConnection.ODBCConnection
      .BackgroundQuery = True
      .CommandType = xlCmdSql
      .Connection = "ODBC;DSN=SQL2;"
      .RefreshOnFileOpen = False
      .SavePassword = False
      .SourceConnectionFile = "D:\MyDocs\My Data Sources\Mydbx.odc"
      .SourceDataFile = ""
      .ServerCredentialsMethod = xlCredentialsMethodIntegrated
      .AlwaysUseConnectionFile = False
    End With

    Set myWorksheet = myWorkBook.Worksheets.Add

    Set myQuerytable = myWorksheet.ListObjects.Add( _
        SourceType:=0, _
        Source:="ODBC;DSN=SQL2;", _
        Destination:=Range("$A$1")).QueryTable

    With myQuerytable
      .CommandText = Array("SELECT * FROM `MyDBx`.`tablea`")
      .RowNumbers = False
      .FillAdjacentFormulas = False
      .PreserveFormatting = True
      .RefreshOnFileOpen = False
      .BackgroundQuery = True
      .RefreshStyle = xlInsertDeleteCells
      .SavePassword = False
      .SaveData = True
      .AdjustColumnWidth = True
      .RefreshPeriod = 0
      .PreserveColumnInfo = True
      .SourceConnectionFile = "D:\MyDocs\My Data Sources\MyDBx.odc"
      .Refresh BackgroundQuery:=False
    End With
End Sub