CODEWITHSUNDEEP
sqlexcelvba
Don Nalaka
Don Nalaka

Reputation: 139

How to use SQL query to manipulate Excel data?

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.

enter image description here

The end result would be something like below,

enter image description here

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

enter image description here

Upvotes: 0

Views: 764

Answers (1)

Storax
Storax

Reputation: 12167

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

enter image description here

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

Upvotes: 1

Related Questions