使用VBA执行在文本框中编写的SQL [英] Execute SQL written in a textbox with VBA

问题描述

关于此问题:

---

在 VBA 中，我指的是 SqlString 中的文本框:

  Sub Get_Data_from_DWH()昏暗的conn作为新的ADODB.Connection昏暗作为新的ADODB.Recordset设置conn = New ADODB.Connectionconn.ConnectionString ="DRIVER = {MySQL ODBC 5.1驱动程序};SERVER = XX.XXX.XXX.XX;DATABASE = bi;UID = testuser;PWD =测试；OPTION = 3"；打开SqlString = ThisWorkbook.Sheet1.Shapes("SqlQuery1").OLEFormat.Object.Text设置rs =新的ADODB.Recordsetrs.Open strSQL，conn，adOpenStaticSheet1.Range("A1").CopyFromRecordset rsrs.Close关闭结束子 

---

但是，我在 SqlString 上收到了 runtime error 438 .
您知道要使其生效需要进行哪些更改吗?

解决方案

Thisworkbook.Sheet1 不是有效的对象路径，请尝试:

  SqlString = ThisWorkbook.Sheets("Sheet1").Shapes("SqlQuery1").OLEFormat.Object.Text 

或者只是

  SqlString = Sheet1.Shapes("SqlQuery1").OLEFormat.Object.Text 

并确保工作表明确命名为"Sheet1"

此外，您需要更改

  rs.Open strSQL，conn，adOpenStatic 

对此:

  rs.Open SqlString，conn，adOpenStatic 

您可能应该使用

  Dim SqlString作为字符串 

在例程开始时

With reference to this question: Avoid new line seperators in mySQL query within VBA code, I wanted to execude a SQL statement that is written in a textbox in the Excel-File.
Therefore, I created a textbox called SqlQuery1 looking like this:

---

In the VBA I refered to the textbox within the SqlString:

Sub Get_Data_from_DWH ()

    Dim conn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
        
    Set conn = New ADODB.Connection
    conn.ConnectionString = "DRIVER={MySQL ODBC 5.1 Driver}; SERVER=XX.XXX.XXX.XX; DATABASE=bi; UID=testuser; PWD=test; OPTION=3"
    conn.Open
    
    SqlString = ThisWorkbook.Sheet1.Shapes("SqlQuery1").OLEFormat.Object.Text
                            
    Set rs = New ADODB.Recordset
    rs.Open strSQL, conn, adOpenStatic

    Sheet1.Range("A1").CopyFromRecordset rs
    
    rs.Close
    conn.Close
    
End Sub

---

However, I get runtime error 438 on the SqlString.
Do you have any idea what I need to change to make it work?

解决方案

Thisworkbook.Sheet1 is not a valid object path, try instead:

SqlString = ThisWorkbook.Sheets("Sheet1").Shapes("SqlQuery1").OLEFormat.Object.Text

Or just

SqlString = Sheet1.Shapes("SqlQuery1").OLEFormat.Object.Text

And make sure the sheet is definitely named "Sheet1"

Also, you need to change

rs.Open strSQL, conn, adOpenStatic

to this:

rs.Open SqlString, conn, adOpenStatic

And you should probably use

Dim SqlString as String

at the start of the routine

这篇关于使用VBA执行在文本框中编写的SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！