How to use SQL query to manipulate Excel data?

Question

I'm trying to perform a group by SQL statement within an excel file. Below is the excel table. How can I execute a simple group by SQL statement via VBA to get the results.

The end result would be something like below,

PS - I do not want to use methods like Pivot, Power query etc. I need to do it using VBA and SQL within the excel file.

Example 02 -

I found below simple example, https://stackoverflow.com/a/45720915/5002322

I tried to replicate this so I can use the same method for my issue and get the feel of it.

Using example 02

Here I created a new excel file named "Book11.xlsx" and saved it.

I added values into "Sheet2" as A1 = 1, A2 = 2 and A3 = 3.

Created a excel Macro and used below code.

Public Sub OverkillSum()

    Dim connection As Object
    Set connection = CreateObject("ADODB.Connection")
    connection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                    "Data Source=" & ThisWorkbook.FullName & ";" & _
                    "Extended Properties=""Excel 8.0;HDR=Yes;"";"

    Dim recordset As Object
    Set recordset = connection.Execute("SELECT SUM(Values) As Total FROM [Sheet2$]")

    Sheet1.Range("A4").Value = recordset.Fields("Total").Value
    recordset.Close
    connection.Close

End Sub

When I ran the macro I get an error saying "No value given for one or more required parameters"

My excel file sheets

You can't use SQL to query Excel spreadsheets from within Excel. Use PowerQuery instead. Arguably, PowerQuery is better than SQL, it just takes some getting-used-to. It's also the first GUI editor for building queries that actually _isn't terrible_ (anyone else remember `MSQUERY`?) — Dai, Aug 08 '21 at 08:17
Thanks. I was reading that we may be able to do it using ADODB.Connection but I'm not understanding it. I want to automate the group by function. I was trying to record the macro to use power query but seems it is not integrated in that way. Can we automate it using Power Query ? — Don Nalaka, Aug 08 '21 at 08:25
Yes, it is possibe via [ADODB](https://excelmacromastery.com/excel-vba-copy/#Using_ADO_and_SQL). What did you try and where did you get stuck? — Storax, Aug 08 '21 at 08:32
@Storax I tried to go through this simple example to see if it works https://stackoverflow.com/a/45720915/5002322 Here I created a new excel file named "Book11.xlsx" and saved it. I added values into "Sheet2" as A1 = 1, A2 = 2 and A3 = 3. Created a excel Macro and copy past the code in there. When I ran the macro I get an error saying "No value given for one or more required parameters" — Don Nalaka, Aug 08 '21 at 08:41
@Storax could you check please. I have added it to my main question — Don Nalaka, Aug 08 '21 at 09:05
You have to add a header to your excel file and the header name has to be `Value` otherwise `SELECT SUM(Values) As Total FROM [Sheet2$]` does not know what column to add. — Storax, Aug 08 '21 at 09:32

Storax · Accepted Answer · 2021-08-08T10:16:52.670

1

In order for the code to run you have to add a header to the column A, i.e your sheet should look like that

This is stated in the connection information with HDR=Yes and the name Values of the column is used in the SQL staement SELECT SUM(Values) As Total FROM [Sheet2$]

Updated code for using the sheetname instead of the codename when writing the result

Public Sub OverkillSum()

    Dim connection As Object
    Set connection = CreateObject("ADODB.Connection")
    connection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                    "Data Source=" & ThisWorkbook.FullName & ";" & _
                    "Extended Properties=""Excel 8.0;HDR=Yes;"";"

    Dim recordset As Object
    Set recordset = connection.Execute("SELECT SUM(Values) As Total FROM [Sheet2$]")

    Worksheets("Sheet1").Range("A4").Value = recordset.Fields("Total").Value
    recordset.Close
    connection.Close

End Sub

edited Aug 08 '21 at 10:16

answered Aug 08 '21 at 09:42

Storax

11,158
3
16
33

Thanks. I have done the suggested change. I can run it now without any errors. However, I don't see the out put in cell Sheet1!A4. Would you be able to help me here as well please. – Don Nalaka Aug 08 '21 at 09:46
Hmm, for me the code is working as expected. Output is on **Sheet1** in cell A4, it is not on **Sheet2** ! – Storax Aug 08 '21 at 09:49
I closed down all the opened sheets and tried again. I get an error at Sheet1.Range("A4").Value = recordset.Fields("Total").Value Error says - Run-time error '424' Object Required – Don Nalaka Aug 08 '21 at 10:00
Yes, you have to have sheet with the codename Sheet1. – Storax Aug 08 '21 at 10:03
I do have it as sheet1. I just attached a screenshot to the question. If you could check please. – Don Nalaka Aug 08 '21 at 10:15
Perfect thanks. It works now. I will try to build it for my initial issue. Hoping to get some help if I run into further issues. – Don Nalaka Aug 08 '21 at 10:21
1

The SQL Statement for your first example is `"SELECT Col1,Col2,Col3,Col4, Sum(Qty) as SumQty FROM [Sheet3$] GROUP BY Col1, Col2, Col3, Col4"` PS I was to lazy to write the column names completely ;-) – Storax Aug 08 '21 at 10:22

How to use SQL query to manipulate Excel data?

1 Answers1