is it possible to use SQL in excel?

poiu

Active Member
Joined
Sep 13, 2011
Messages
384
Hello,

I'd like to query a worksheet (which will be a database) in an excel workbook using an SQL statement (within excel VBA) and then populate another worksheet in excel with the output of the query.

Please could you tell me if it's possible? :confused:

Many thanks,

Poiu
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
Look on the web for help with ADO. Example:
Code:
Public Sub QueryWks_ADO()
    Dim objRS As Object
    Dim strConnection As String
    Dim strSQL As String
    
    Const strDatabasePath As String = "C:\Database\DB.xls"
    Const strTable As String = "Sheet1"
    
    strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                    "Data Source=" & strDatabasePath & ";" & _
                    "Extended Properties=Excel 12.0;" 'change to Excel 8.0 for 97-2003
                    
    strSQL = "SELECT * FROM [" & strTable & "$]" 'The dollar suffix indicates that _
                                                  the table is a sheet
    
    Set objRS = CreateObject("ADODB.Recordset") 'Create the recordset object
    
    Call objRS.Open(strSQL, strConnection, 1, 3, 1) 'Load the recordset
    Call Sheets(1).Range("A1").CopyFromRecordset(objRS) 'Unload the recordset
    
    objRS.Close
    Set objRS = Nothing
End Sub
 
Upvote 0
Thanks very much, very interesting. Just a couple of questions please if I may:

1. From reading about MS query my understanding is that the whole process can't be contained with an excel workbook, so I couldn't send an excel file with macros to someone without MS query on their computer and get VBA to run the SQL queries on their computer. Please could you tell me if I'm correct?

2. If I run equivalent SQL queries on identical large datasets in Access (with an excel file output) and Excel (using ADO) how big would the difference be in speed?

Many thanks.

Poiu
 
Upvote 0
I've never seen a Windows machine that didn't have MS Query installed. I regularly move Excel files between work and home that query other Excel files. Sometimes the "tables" (if you will) are all in the same Excel file sometimes not. What matters is whether or not the "tables" are located in the same folder at each workstation if they are not all in the same file. VBA really has nothing to do with the query as far as retrieving data goes. I use VBA for post processing sometimes but if the query is well designed there usually isn't much for VBA to do other than add custom buttons for refresh or filtering and fluff like that.

I also use ADO, mostly when I need to write back to the database. My largest database is less than 100K records so I really don't notice much difference either way. I suppose it would depend on how efficient your ADO code is. Given a choice, I use MS Query because it can be done without any code that I will end up maintaining.

Gary
 
Upvote 0
Thanks Gary,

I'm thinking of using MS Query for 'in-department' work, and for anything going out of the department I'll use ADO within the VBA code (so that people don't panic at the sight of sql code or an unfamiliar piece of software).

Many thanks,

Poiu
 
Upvote 0
Thanks Jon,

I found the advise below from the Microsoft's webpage http://support.microsoft.com/kb/257819#Connect :

Connect to Excel with ADO

******** type=text/javascript> loadTOCNode(2, 'moreinformation'); *********>ADO can connect to an Excel data file with either one of two OLE DB Providers included in MDAC:
  • Microsoft Jet OLE DB Provider -or-
  • Microsoft OLE DB Provider for ODBC Drivers

Do you know if these two providers are consistently available on different versions of excel? - I'd be sending reports out to different subsidiaries of a company accross many countries so I'm wondering if there could be compatibility issues?

Thanks,

Poiu
 
Upvote 0
I've done all sorts of projects now with ADO and almost always use Jet for Excel / Access data sources. I've never had any problem... I am working on a project currently where I have had to programme it such that it uses ACE for office12+, and Jet for all the earlier office versions.

I've found this website very useful when needing to build up connection strings for other data sources: http://www.connectionstrings.com/

To see a list of available providers, and help building connection strings, follow the instructions here to set-up connect.udl: http://www.webconcerns.co.uk/asp/udl/udl.asp

The first tab in connect.udl lists the available providers.
 
Upvote 0
Thanks Jon,

I've added a couple of reference libraries ("Microsoft DAO 3.6 Object Library" and "Microsoft ActiveX Data Objects 2.8 Library") but am getting a "type mismatch" error on the bold line of code below. The value of variable objConn is:

Provider=Microsoft.Jet.OLEDB.4.0;Password="";User ID=Admin;Data Source=H:\Excel ADO test\ado_test2.xls;Mode=Share Deny None;Extended Properties="Excel 8.0;HDR=Yes";Jet OLEDB:System database="";Jet OLEDB:Registry Path="";Jet OLEDB:Database Password="";Jet OLEDB:Engine Type=35;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False

Public Sub create_ado_connection()
Path = "H:\Excel ADO test\ado_test2.xls"
GetExcelConnection (Path)
End Sub


Public Function GetExcelConnection(ByVal Path As String, _
Optional ByVal Headers As Boolean = True) As Connection
Dim strConn As String
Dim objConn As ADODB.Connection
Set objConn = New ADODB.Connection
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & Path & ";" & _
"Extended Properties=""Excel 8.0;HDR=" & _
IIf(Headers, "Yes", "No") & """"
objConn.Open strConn
Set GetExcelConnection = objConn


' copy data from a workbook
'To read a sheet:
strRequest = "SELECT * FROM Sheet1$"
'To refer to a range by its address:
strRequest = "SELECT * FROM Sheet1$A1:D10"
'To refer to a single-cell range, pretend it's a multi-cell range
'and specify both the top-left and bottom-right cells:
strRequest = "SELECT * FROM Sheet1$A1:A1"
'To read a named range:
strRequest = "SELECT * FROM MyDataRange"
'To read a worksheet-level named range
strRequest = "SELECT * FROM Sheet1$MyData"

'and then just open a recordset
objRS.Open strRequest, objConn

End Function

Many thanks,

Poiu
 
Upvote 0

Forum statistics

Threads
1,224,583
Messages
6,179,681
Members
452,937
Latest member
Bhg1984

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top