如何将长而复杂的SQL查询嵌入到Excel VBA代码中? [英] How to embed long and complex sql queries into Excel VBA code?

问题描述

我有下面的VBA代码，当我必须嵌入一个简单查询(例如Select * from table1)时，可以成功地将数据从SQL数据库获取到Excel工作表中，但是该代码无法正常工作并显示

I have the below VBA code that works successfully in getting data from SQL database to an Excel worksheet when I have to embed a simple query (e.g. Select * from table1), but this code isn't working and showing a

编译错误:未定义用户定义的类型.

Compile error: user-defined type not defined.

当我遇到复杂而漫长的查询时:

when I have complex and long queries:

 Sub ConnectSqlServer()

        Dim conn As ADODB.Connection
        Dim rs As ADODB.Recordset
        Dim sConnString As String

        ' Create the connection string.
        sConnString = "Provider=SQLOLEDB;Data Source=vrsqladhoc;" & _
                      "Initial Catalog=TACT_REV;" & _
                      "Integrated Security=SSPI;"


        ' Create the Connection and Recordset objects.
        Set conn = New ADODB.Connection
        Set rs = New ADODB.Recordset

        ' Open the connection and execute.
        conn.Open sConnString
        Set rs = conn.Execute("select distinct column1 from table1;")

        ' Check we have data.
        If Not rs.EOF Then
            ' Transfer result.
            Sheets(2).Range("A2").CopyFromRecordset rs
        ' Close the recordset
            rs.Close
        Else
            MsgBox "Error: No records returned.", vbCritical
        End If

        End Sub

我打算在VBA代码中嵌入的长查询看起来像这样:

The long query I am intending to embed in the VBA code looks something like this:

    SELECT Count(column1) as status_count, case when 
    missing_flag = 1 then ' Missing' else ' Available' end as status,'Name' as Document_Type

    FROM table1 cm
    inner join table2 md on md.column1 = cm.column2
    WHERE cm.column3 = 'R'
    and cm.column4 = 3
    group by case when 
    missing_flag1 = 1 then 'Missing' else 'Available' end

    Union All

      SELECT Count(column1) as status_count, case when 
    missing_flag = 1 then ' Missing' else ' Available' end as status,'Name' as Document_Type

    FROM table2 cm
    inner join table2 md on md.column1 = cm.column2
    WHERE cm.column3 = 'R'
    and cm.column4 = 3
    group by case when 
    missing_flag1 = 1 then 'Missing' else 'Available' end

Union All

      SELECT Count(column1) as status_count, case when 
    missing_flag = 1 then ' Missing' else ' Available' end as status,'Name' as Document_Type

    FROM table3 cm
    inner join table2 md on md.column1 = cm.column2
    WHERE cm.column3 = 'R'
    and cm.column4 = 3
    group by case when 
    missing_flag1 = 1 then 'Missing' else 'Available' end

Union All

      SELECT Count(column1) as status_count, case when 
    missing_flag = 1 then ' Missing' else ' Available' end as status,'Name' as Document_Type

    FROM table4 cm
    inner join table2 md on md.column1 = cm.column2
    WHERE cm.column3 = 'R'
    and cm.column4 = 3
    group by case when 
    missing_flag1 = 1 then 'Missing' else 'Available' end

Union All

      SELECT Count(column1) as status_count, case when 
    missing_flag = 1 then ' Missing' else ' Available' end as status,'Name' as Document_Type

    FROM table5 cm
    inner join table2 md on md.column1 = cm.column2
    WHERE cm.column3 = 'R'
    and cm.column4 = 3
    group by case when 
    missing_flag1 = 1 then 'Missing' else 'Available' end

Union All

      SELECT Count(column1) as status_count, case when 
    missing_flag = 1 then ' Missing' else ' Available' end as status,'Name' as Document_Type

    FROM table6 cm
    inner join table2 md on md.column1 = cm.column2
    WHERE cm.column3 = 'R'
    and cm.column4 = 3
    group by case when 
    missing_flag1 = 1 then 'Missing' else 'Available' end

Union All

      SELECT Count(column1) as status_count, case when 
    missing_flag = 1 then ' Missing' else ' Available' end as status,'Name' as Document_Type

    FROM table7 cm
    inner join table2 md on md.column1 = cm.column2
    WHERE cm.column3 = 'R'
    and cm.column4 = 3
    group by case when 
    missing_flag1 = 1 then 'Missing' else 'Available' end

Union All

      SELECT Count(column1) as status_count, case when 
    missing_flag = 1 then ' Missing' else ' Available' end as status,'Name' as Document_Type

    FROM table8 cm
    inner join table2 md on md.column1 = cm.column2
    WHERE cm.column3 = 'R'
    and cm.column4 = 3
    group by case when 
    missing_flag1 = 1 then 'Missing' else 'Available' end

请让我知道如何将长的SQL查询嵌入到VBA代码中而不会出现任何错误.

Please let me know how the long SQL queries can be embedded into the VBA code without any errors.

推荐答案

要将SQL字符串化为VBA，请执行以下操作:

To stringify SQL into VBA do something like the below:

Dim sql As String

sql = "SELECT Count(column1) as status_count, case when " & _
        "missing_flag = 1 then ' Missing' else ' Available' end as status,'Name' as Document_Type" & _
        "FROM table1 cm " & _
        "inner join table2 md on md.column1 = cm.column2 " & _
        "WHERE cm.column3 = 'R' " & _
        "and cm.column4 = 3 " & _
        "group by case when " & _
        "missing_flag1 = 1 then 'Missing' else 'Available' end "

sql = sql & " UNION ALL "

sql = sql & " keep going like this ..."

如果它的SQL简短且易于阅读，则可以在一条语句中执行此操作，但是如果使用长语句，则会在VBA中遇到连续行(_)问题.这就是为什么我将它分成几个sql设置的原因.

You can do it in one statement if its short SQL and easy-to-read, but with long statements, you will run into continuation line (_) issues in VBA. That is why I broke it into several settings of sql.

这是蛮力的，不是理想的解决方案，请考虑调试和维护的噩梦！

This is brute force and not ideal solution, think nightmare to debug and maintain!

最好是将它作为存储过程直接存储在db中，并让excel vba调用它.

Best is to store in db directly as stored procedure and have excel vba call that.

这篇关于如何将长而复杂的SQL查询嵌入到Excel VBA代码中?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！