Manipulating data WITHIN excel using SQL

Question

I have an excel report I am working on which uses VBA to manipulate data.

Although there isn't a reason I can't use VBA for my purposes, I would rather do it in SQL as I am much more proficient in that language. In order to do so, I am facing a couple of challenges and have simplified my problem to one easier to solve.

Say I have a Excel spreadsheet with the number 1 in cell A1, 2 in cell A2, and 3 in cell A3. In cell A4 I am trying to sum these, resulting in the number 6.

The SUM() function would get me the answer I would like, but not the way I want to go about it.

Instead here is what I am trying to do...

1) read cells A1->A3 into a table object (call this x) using VBA
2) declare a string variable and set it to 'SELECT SUM(column1) FROM x'
3) execute that sql string
4) store the results in cell A4

Is this a feasible way of going about things? If so, could an example be posted?

Whilst it is feasible it is not sensible. Also, SO isn't a code writing service. Do some research if you want help — Tom, Aug 16 '17 at 17:19
Why isnt it sensible, also this isnt the code I need to write. I'm trying to make the link from excel to SQL. — Joel Sinofsky, Aug 16 '17 at 17:22
why not add, and save in a cell with VBA than quary the result? — BlooB, Aug 16 '17 at 17:37
@dirty_feri this is an example of something I wish to do, if I add it in SQL I can generalize the concept — Joel Sinofsky, Aug 16 '17 at 17:40
The correct tool for what you are looking to accomplish is VBA. If you are not very proficient at it, improve. — Dan Bracuk, Aug 16 '17 at 17:40
look here http://tomaslind.net/2013/12/26/export-data-excel-to-sql-server/ — BlooB, Aug 16 '17 at 17:51
Excel isn't complicated. Learn to use it. Whoever maintainer inherits whatever code you've built to solve this problem will curse you. I would. What you need is `=SUM($A$1:$A$3)` in cell `A4`, and you're done. You *are* taking something incredibly simple, and turning it into something incredibly complicated, for no reason whatsoever. — Mathieu Guindon, Aug 16 '17 at 17:58
Its not a school assignment...geez so much hate. Short answer - im a former DBA who just became a quant and these guys still have legacy VBA systems. They dont like them but arent taking the time to rewrite everything. — Joel Sinofsky, Aug 16 '17 at 19:11
There's no hate here, just people trying to guide you away from creating an overly complex monstrosity and toward using the tools that are built into the software you're using. As a former DBA, figuring out a few Excel formulas and a smidgen of VBA should be pretty easy for you to handle. — FreeMan, Aug 16 '17 at 19:15
https://technet.microsoft.com/en-us/library/ee692882.aspx - what @FreeMan said. Use the right tools for the right job. SQL is a formidable hammer, but not everything is a nail, *especially* when you're sitting in front of a powerful tool such as Excel. I've un-deleted my answer if you need it, hopefully for something MUCH more complicated than computing the sum of 3 cells, to justify the added complexity and inherent inefficiency of the solution. — Mathieu Guindon, Aug 16 '17 at 19:18
"In order to do so, I am facing a couple of challenges and have simplified my problem to one easier to solve" Did nobody read my original statement? I'm doing risk decomposition, but want to do it in sql. Obviously I am going to use it for more than summing three cells... — Joel Sinofsky, Aug 16 '17 at 19:33
You can get better charts outside of Excel. Python has better math libraries. SQL is better for big data and sets. C offers better performance. Latex can format text with more precision. None of them are built-in to Excel. Sometimes the best tool is the *available* tool. For better or worse, the world (particularly fin services/quant) runs on Excel, CSV files and VBA. It's not clear what algorithm you want to apply to your inputs, but unless the SQL is offloading computation to a server, SQL is unlikely the best approach to do it. — ThunderFrame, Aug 16 '17 at 23:30

Mathieu Guindon · Accepted Answer · 2017-08-16T19:24:02.770

Yes, it can be done. No, it shouldn't be done. If you need the sum of 3 cells, compute the sum of 3 cells - Excel has built-in functions specifically made for this.

=SUM(A1:A3)

Type that in [A4] and you'll get your sum without writing any code, in the most efficient way possible, and without surprising the Hell out of anyone looking at what you've done.

Still not convinced? Okay. Have a seat, grab some pop-corn, and enjoy the ride.

read cells A1->A3 into a table object (call this x) using VBA

Excel isn't a database, it doesn't have tables - not in the way you mean the word "table". But that's no showstopper.

Say your workbook has 3 sheets, code-named Sheet1, Sheet2 and Sheet3 (that's the default anyway). So you have Sheet1!A1:A3 populated with some numbers that you want to SUM up using sql, because... doesn't matter why, just because.

Since we want the sum to be written into Sheet1!A4, we won't be using Sheet1 as our "table" - rather we'll treat it as our output.

So we'll copy Sheet1!A1:A3 to Sheet2:

Sheet2.Range("A1").Value = "Values" 'our column header
Sheet1.Range("A1:A3").Copy Sheet2.Range("A2") 'our values

Next, we need something that can treat Sheet2 as a "table", and execute SQL queries against it. So we'll set up an ADODB/OLEDB connection to Sheet2, execute the SQL query, get a Recordset object with the results, and then dump the value into Sheet1!A4.

Sloppy late-bound code doing this would look like this:

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

Note that the connection string requires ThisWorkbook.FullName, so that won't work in a throw-away workbook that you haven't saved yet.

Between what's above, and =SUM(A1:A3) in cell A4, the design decision should be a no-brainer.

Late-bound calls get resolved at run-time. This is overhead that can be avoided by referencing the ADODB type library and using ADODB.Connection and ADODB.Recordset types instead of working with Object interfaces.
Connection to the workbook is also extraneous run-time overhead.
Querying the worksheet through OLEDB is absolutely not justified for computing the sum of 3 values.
You need to remember to clean up your connections and recordsets!
Don't do that.
Just don't.
Sheet1.Range("A4").Value = Application.WorksheetFunction.Sum(Sheet1.Range("A1:A3")) is the one-liner equivalent of the native Excel worksheet function solution - that's still overkill, but at least it remains in the realm of Excel and doesn't involve flying to the Moon and back.
Did I say don't do that?

This type of solution is useful for other purposes, e.g. when you have a workbook laid out as a table, that contains information that should live in a database but somehow lives in an Excel worksheet, with so much data that opening it through Workbooks.Open and computing a complex aggregate (perhaps involving WHERE and GROUP BY clauses) would be inefficient with SUMIFS or other non-SQL means.

Thank you for such a detailed answer. To be clear, like i stated in my original question "I am facing a couple of challenges and have simplified my problem to one easier to solve" so I was looking for a trivial computation to solve in this post. — Joel Sinofsky, Aug 16 '17 at 19:37
You've also just given your database a really easy point of sql injection — Tom, Aug 17 '17 at 09:55

Manipulating data WITHIN excel using SQL

1 Answers1

Linked