时间戳

首页 > 解决方案 > 使用 VBA 更新和现有 OLEDB 连接

sql - 使用 VBA 更新和现有 OLEDB 连接

问题描述

我想用可修改的脚本更新电子表格中的现有连接。

基本上，我希望用户能够放入账户引用列表，按下按钮，Excel 会输出 SQL 输出，其中这些账户引用与我们拥有的系统相匹配。

我已经设法将所有脚本整理到一个单元格中（我的“工作”选项卡上的 F1），所以一切都很好，我已经添加了初步连接（并将其称为“CustomerContactDetails”）。

我也启用了 Microsoft ActiveX Data Objects 6.1 Library。

我的 VBA 脚本是：

Sub UpdateScript()

Dim Script As String

SQLScript = Worksheets("Workings").Range("F1").Value

ActiveWorkbook.Connections("CustomerContactDetails").OLEDBConnection.ConnectionString = SQLScript
ActiveWorkbook.Connections("CustomerContactDetails").Refresh

End Sub

我在第四行得到错误（ActiveWorkbook.Connections("CustomerContactDetails").OLEDBConnection.ConnectionString = SQLScript）：

运行时错误'9'；下标超出范围

有谁知道如何帮助解决这个问题？感觉好像我并没有那么远，我也不想做任何太复杂的事情。我似乎无法让它工作！

标签： sqlexcelvba

解决方案

尝试这样的事情，看看你得到什么输出

Sub UpdateScript()

    Dim Script As String
    Dim conn as WorkbookConnection

    For Each conn in ActiveWorkbook.Connections
        Debug.Print conn.Name
        If conn.Name = "CustomerContactDetails" Then
            SQLScript = Worksheets("Workings").Range("F1").Value
            conn.OLEDBConnection.ConnectionString = SQLScript
            conn.OLEDBConnection.Refresh
        End If 
    Next conn
End Sub

编辑：看起来你拥有的是WorkbookQuery 我创建的（到 Oracle DB），你可以访问和修改它，如下所示：

Sub Tester()
    
    Dim q As WorkbookQuery, lo As ListObject
    
    Set lo = ActiveSheet.ListObjects("Query1")
    
    Debug.Print ActiveWorkbook.Queries.Count
    Set q = ActiveWorkbook.Queries("Query1")
    
    Debug.Print q.Formula
        
    'revise query
    q.Formula = "let" & vbCrLf & _
       " Source = Oracle.Database(""BLAH"", [HierarchicalNavigation=true," & _
       "Query=""select * from table1""])" & _
       vbCrLf & "in" & vbCrLf & "Source"
    
    lo.Refresh 'I got a popup "do you want to run this?" here...
    
End Sub

请注意，在创建您无法修改的对象类型时尝试录制宏几乎总是有用的——这将指向您需要的语法。

推荐阅读