Run SQL-statement in Excel Cell with VBA

Question

Excel-File:

   |        A       |       B         |            C                |        D        |     E
---|----------------|-----------------|-----------------------------|-----------------|------------
1  |   sales_date   |   2020-01-15    |     =CONCATENATE(C2,C3)     |                 |
2  |                |                 |      SQL-Query Part 1*      |                 |
3  |                |                 |      SQL-Query Part 2*      |                 |
4  |                |                 |                             |                 |

VBA

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
    
    strSQL = Sheet1.Range("C1")
                            
    Set rs = New ADODB.Recordset
    rs.Open strSQL, conn, adOpenStatic

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

SQL-Query:

SELECT
product, brand, sales_channel,
country, sales_manager, sales_date, return_date,
process_type, sale_quantity, return_quantity, sales_value, variable_costs, fixed_costs
FROM bi.sales
WHERE sales_date = '"&B1&" AND country IN ('DE', 'US', 'NL') 
ORDER BY FIELD (brand, 'brand_A', 'brand_B', 'brand_C');

I want to run the above SQL-Query with the above VBA.

The problem is that the query contains more than 255 characters so it does not fit in one Excel-Cell.
Therefore, I randomly split the query into two parts in Cell C2 and Cell C3 and used CONCATENATE(C2,C3) in Cell C1 to combine both parts of the query.

When I now run the VBA I get runtime error '-2147217887 (80040e21)'.

I guess that VBA cannot handle the CONCATENATE(C2,C3) in Cell C1.
Therefore, I am wondering if there is any other way to solve this issue?

NOTE:
I know I could put the SQL directly into the VBA code. However, my idea is to split the SLQ-string and the execution code since my origianl SQL has even more characters than the example in this quesion and I want to keep the VBA structured as described in this question.

**(a)** there is no 255 char limit for an Excel cell. **(b)** I strongly assume that your problem comes from an invalid SQL statement. **(c)** Your clause `"&L1&"` looks very suspicions. Is `L1` supposed to be a parameter? **(d)** I strongly advocate to use `ADODB-Parameter`, see for example https://stackoverflow.com/a/60640185/7599798 — FunThomas, Apr 23 '21 at 09:20
(a) There is a limitation: https://stackoverflow.com/questions/12386414/excel-use-formula-longer-that-255-characters (b) the SQL-statement runs if I use less than 255 characters, (c) The reference L1 is a typo in the question above. I fixed it to B1 because it should refer to this cell (d) I will check the ADODB-Parameter — Michi, Apr 23 '21 at 09:27
The linked question is about the length of the *formula*, not the length of a cell. There is for sure no 255 char limit for an SQL statement at all. Passing dates to a query is always a problem, and if you don't uses ADODB.Parameter, you need to format the SQL-Statement exactly as the database expects it - which is very different for different databases — FunThomas, Apr 23 '21 at 09:35

score 0 · Answer 1 · answered Apr 23 '21 at 09:00

0

From where do you get the notion that an excel cell is limited to 265 characters?

Excel 365: This is 313 characters in C1:

This is the whole SQL query in cell A1:

answered Apr 23 '21 at 09:00

StureS

227
2
10

Excel gives me this error but even with 313 characters the key problem that a query with more characters than the character limitation cannot be handle remains. – Michi Apr 23 '21 at 09:15
What's the character limitation then? Tell us! – StureS Apr 23 '21 at 09:36

Michi · Accepted Answer · 2021-04-29T07:26:39.360

Option 1:

Splitting the query into parts using CONCATENATE function can delete empty spaces which are needed to run the query correctly.
Therefore, depending on the query instead of =CONCATENATE(C2,C3) you need to use =CONCATENATE(C2," ",C3).

Option 2:

If the above solution does not work you can also solve the issue by doing a work-around and copy&paste the CONCATENATE function from Cell C1 into another cell as Values:

Sub Get_Data_from_DWH()

Sheet1.Range("C4").ClearContents
Sheet1.Range("C1").Copy
Sheet1.Range("C4").Cells.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

    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
    
    strSQL = Sheet1.Range("C4")
                            
    Set rs = New ADODB.Recordset
    rs.Open strSQL, conn, adOpenStatic

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

In the comments it was discussed that there is no limitation on an Excel cell.
For a simple text there is no limitation but the entry of a formula is limited.
In the specific case in the question a formula is necessary because the entry in Cell B1 should be a variable that can be changed by the user anytime.

Therefore, I think the only way to

a) seperate the SQL-statement from the executing VBA-Code
b) use a query with more than 255 characters
c) apply entries in an Excel cell as variables

is one of the above solutions.

Run SQL-statement in Excel Cell with VBA

2 Answers2