Write SQL query on excel tables

Question

I was recently given a coding assignment which involves me writing a SQL query for a database presented in the .xlsx form (excel spreadsheet). The assignment basically involves me selecting certain values from the first three tables, doing some calculations on those values and then printing the answer on the fourth table. All four tables have been created, and the first three tables have input values for me to choose from, while the last table has headings below which I should put my answers in.

May I know how I can do that? (essentially, where can I write SQL commands like SELECT * FROM Table 1 WHERE Number = 1 when the target is an excel table)

I'm guessing you're not supposed to actually run queries on Excel... but rather the excel speadsheet is just a clear way of demonstrating the database schema. You could recreate the tables on any database and then run queries against them... — nhgrif
– nhgrif, Commented Apr 8, 2015 at 21:19
thanks, that may be the case (I've never done SQL before). So I should copy the tables into another file, or should I open the excel file using a different program? — mystery578
– mystery578, Commented Apr 9, 2015 at 7:52
Sounds like you might want to find out exactly what your assignment is asking... If it does need to be done in a database, you can install MySQL for free or you can probably get Microsoft SQL Server for free as well. If you have Access installed, that might be the easiest just to accomplish the task. — BAReese
– BAReese, Commented Apr 9, 2015 at 20:15
it just says: please write out your SQL query to display the following items. Can I use Access then or do I need to do it in MySQL (which I do have installed)/Microsoft SQL? — mystery578
– mystery578, Commented Apr 9, 2015 at 23:02

BAReese · Accepted Answer · 2015-04-10 13:57:15Z

1

I run queries against Excel files all the time - usually xls, but the code is basically the same for xlsx:

Sub ImportThisFile(FilePath As String, SourceSheet As String, Destination As Range)
    Set Conn = New ADODB.Connection
    Conn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _
        FilePath & ";Extended Properties=Excel 8.0;"

    Sql = "SELECT * FROM [" & SourceSheet & "$]"

    Set RcdSet = New ADODB.Recordset
    RcdSet.Open Sql, Conn, adOpenForwardOnly

    Destination.CopyFromRecordset RcdSet

    RcdSet.Close
    Set RcdSet = Nothing

    Conn.Close
    Set Conn = Nothing
End Sub

This uses early binding for the ADODB objects so "Microsoft ActiveX Data Objects 2.x Library" will need to be added to your references (Tools->References...)

According to connectionstrings.com, for XLSX the Provider should be Microsoft.Jet.OLEDB.12.0 and the Extended Properties should be Excel 12.0 Xml

https://www.connectionstrings.com/excel/

Example of how this would be called from another subroutine/function:

Sub StartDoingStuff()
    ImportThisFile "c:\Path\To\File.xls", "Sheet1", range("Sheet2!A2")
End Sub

edited Apr 10, 2015 at 13:57

answered Apr 8, 2015 at 21:25

BAReese

5012 silver badges5 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

mystery578 Over a year ago

ok, and where can I insert this program? (is it a program different from excel or can I do it somehow within excel...)

BAReese Over a year ago

This is added as a macro. Alt+F11 opens the Visual Basic code editor in Excel. Each sheet has it's own section for code as does the workbook, or you can add a module to the workbook just for code.

mystery578 Over a year ago

ok, and which should I do--the code for the respective table or add a module for the workbook? (I'm inclined to the latter) Also, how do I run the code after it's done?

BAReese Over a year ago

I prefer separate modules, but it's up to you. I stripped this out of a bigger module I had so it will need some modification - obviously. You could remove the parameter references or just modify them to fit your needs. This module was from a sheet that I use to combine every xls file in folder into one sheet. You could just create another sub that calls this one where: FilePath = Full path to the Excel workbook SourceSheet = Name of the sheet to import from that workbook Destination = Excel range where the query results would start.

Collectives™ on Stack Overflow

Write SQL query on excel tables

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related