避免在 VBA 代码中的 mySQL 查询中使用换行符 [英] Avoid new line seperators in mySQL query within VBA code

问题描述

我有以下 VBA 来从数据库中提取数据:

Sub Get_Data_from_DWH ()Dim conn As New ADODB.ConnectionDim rs As New ADODB.RecordsetDim dateVar 作为日期设置 conn = 新建 ADODB.Connectionconn.ConnectionString = "DRIVER={MySQL ODBC 5.1 驱动程序};服务器=XX.XXX.XXX.XX;数据库=双；UID=测试用户；PWD=测试；选项=3"连接打开strSQL = "选择"&_"产品、品牌、销售渠道"&_"国家，销售经理，销售日期，退货日期，"&_"process_type, sale_quantity, return_quantity "&_"FROM bi.sales"&_"WHERE sales_date BETWEEN '2020-01-01' AND '2020-06-30' "&_"AND country IN ('DE', 'US', 'NL') "&_"ORDER BY FIELD (brand, 'brand_A', 'brand_B', 'brand_C');"设置 rs = 新的 ADODB.Recordsetrs.Open strSQL、conn、adOpenStaticSheet1.Range(A1").CopyFromRecordset rsrs.关闭连接关闭结束子

这个VBA基于SQL提取数据没有任何问题.

---

但是，在我的原始文件中，VBA 要大得多，因此我必须使用很多换行符 " 和<代码>"&_ .
这使得在 VBA 中处理 SQL 非常困难，因为结构相当混乱.

因此，我想知道是否有替代方法可以让您在没有换行符的情况下输入 SQL.
像这样:

strSQL = "选择产品、品牌、销售渠道、国家、销售经理、销售日期、退货日期、流程类型、销售数量、退货数量FROM bi.salesWHERE sales_date BETWEEN '2020-01-01' AND '2020-06-30'AND country IN ('DE', 'US', 'NL')按字段排序(品牌，'brand_A'，'brand_B'，'brand_C')；"

你知道这是否可行吗?

解决方案

明确答案 - 在 VBA 中这是不可能的，IDE 无法允许在字符串中换行.

但这里有一些建议:

如果您无法在数据库上创建视图并仅使用这些视图，也许您可​​以直接链接到数据源并以这种方式进行查询?因此，您不必使用 VBA 来编写查询 - Excel 具有强大的内置连接性，并且您可以获得一个 MySQL 库，只需谷歌在 excel 中连接到 mysql 数据库"

如果您真的希望将查询定义为其他地方的文本，那么可能有助于整理代码的一件事是从另一个位置加载字符串，您可以在其中以您喜欢的格式保存 sql 查询.

您可以从 VBA 模块加载它们，该模块仍然具有拆分字符串，但您可以像 StringSQL = Module1.GetQueryOne 一样调用它们，它有助于将执行代码和 SQL 字符串分开>

您可以创建一种从工作表单元格中提取查询的方法，或者将它们保存在工作表的文本框中，然后您可以使用

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

或者甚至将它们保存在 Excel 工作簿随附的文本文件中，然后从那里加载它们.

I have the below VBA to extract data from the database:

Sub Get_Data_from_DWH ()

Dim conn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim dateVar As Date
      
    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
        
                strSQL = " SELECT" & _
                            " product, brand, sales_channel," & _
                            " country, sales_manager, sales_date, return_date, " & _
                            " process_type, sale_quantity, return_quantity " & _
                            " FROM bi.sales" & _
                            " WHERE sales_date BETWEEN '2020-01-01' AND '2020-06-30' " & _
                            " AND country IN ('DE', 'US', 'NL') " & _
                            " ORDER BY FIELD (brand, 'brand_A', 'brand_B', 'brand_C');"

    Set rs = New ADODB.Recordset
    rs.Open strSQL, conn, adOpenStatic
    
    Sheet1.Range("A1").CopyFromRecordset rs

    rs.Close
    conn.Close
    
End Sub

This VBA extracts the data based on the SQL without any problem.

---

However, in my original file the VBA is much bigger and therefore I have to use a lot of new line separators " and " & _ .
This makes the handling of the SQL within the VBA very difficult since the structure is quite confusing.

Therefore I am wondering if there is an alternative that allows you to enter the SQL without the new line separators.
Something like this:

strSQL = " SELECT
           product, brand, sales_channel,
           country, sales_manager, sales_date, return_date,
           process_type, sale_quantity, return_quantity
           FROM bi.sales
           WHERE sales_date BETWEEN '2020-01-01' AND '2020-06-30'
           AND country IN ('DE', 'US', 'NL') 
           ORDER BY FIELD (brand, 'brand_A', 'brand_B', 'brand_C'); "

Do you have any idea if this is possible?

解决方案

Definitive answer - It's not possible in VBA, there's no way for the IDE to allow line breaks in a string.

But here's some suggestions:

If you can't create views on the database and just use those, maybe you could directly link to the data source and do queries that way? So you don't have to use VBA to write queries - Excel has great in-built connectivity and you get a MySQL library, just google "connect to mysql database in excel"

If you really want your queries defined as text somewhere else, one thing that might help to neaten your code is to load your strings from another place where you can keep your sql queries in a format you prefer.

You could load them from a VBA module, which would still have split strings but you can call them like StringSQL = Module1.GetQueryOne and it helps keep the execution code and the SQL strings separate

You could create a method to pull queries from worksheet cells, or maybe keep them in textboxes on a worksheet, then you can use

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

or even keep them in a text file that accompanies your Excel workbook and load them from there.

这篇关于避免在 VBA 代码中的 mySQL 查询中使用换行符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！