How can I run SQL statements on a named range within an excel sheet?
You can just use the name.
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
strFile = Workbooks(1).FullName
strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFile _
& ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"
Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
cn.Open strCon
''Pick one:
strSQL = "SELECT * FROM DataTable" ''Named range
strSQL = "SELECT * FROM [Sheet1$A1:E346]" ''Range
rs.Open strSQL, cn
Debug.Print rs.GetString
In response to question part 2
I notice that you only want today's records, so you should be able to modify the sql to:
strSQL = "SELECT DISTINCT(Expiration) FROM [PositionSummaryTable] " _
& "where [Instrument Type] = 'LSTOPT' AND [Expiration]=#" _
& Format(Date(),"yyyy/mm/dd") & "#"
You have not closed the connection:
cn.Close
And then
Set rs=Nothing
Set cn=Nothing
Using excels named range in a sql string in VBScript
Work can be unpredictable and sometimes we have to go back and modify, fix, reuse code from a while ago. It happened today. I have gone back and reproduced my code and got it working the first time. I must have been doing something wrong when I posted this question, looking at the wrong lines or procedures or something strange. The below code runs perfectly.
it:
- opens a workbook
- establishes a connection with the workbook to retrieve data in a recordset
- opens up a connection to a database and executes a sample insert statment
After running the code I have checked the Temporary databases, the values have been inserted, so I can confirm this is my working solution to the problem originally raised.
Option Explicit
Private Const adUseClient = 3
Dim xl, wb, ws, fPath, rng
fPath = "C:\Users\admin\Desktop\Book1.xlsm"
Call OpenFile()
Call InsertRecordset()
Call CloseFile()
Private Sub OpenFile()
Set xl = CreateObject("Excel.Application")
xl.Visible = False
Set wb = xl.Workbooks.Open(fPath)
Set ws = wb.Sheets(1)
End Sub
Private Sub CloseFile()
wb.Saved = True
wb.Close
xl.Quit
Set wb = Nothing
Set xl = Nothing
End Sub
Private Sub InsertRecordset()
strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & wb.fullname & ";Extended Properties=""Excel 8.0;HDR=NO;IMEX=1"";"
Dim cn, rs, strCon, strSql, cn2
ws.Range("A1:B2").Name = "DATA"
strSql = "SELECT * FROM DATA"
Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
cn.Open strCon
rs.Open strSql, cn
Set cn2 = CreateObject("ADODB.Connection")
With cn2
.CursorLocation = adUseClient
.Open "Driver={SQL Server};Server=HELIUM\PRI; Database=TEMPORARY; UID=admin; PWD=password"
.CommandTimeout = 0
rs.MoveFirst
Do While Not rs.EOF
.Execute "INSERT INTO TEMPORARY.dbo.TEMP_TABLE ( [TEMP_COLUMN] ) VALUES ('" & rs.Fields(1) & "')"
rs.MoveNext
Loop
End With
' Close connections
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
cn2.Close
Set cn2 = Nothing
End Sub
How to run a SQL query on an Excel table?
There are many fine ways to get this done, which others have already suggestioned. Following along the "get Excel data via SQL track", here are some pointers.
Excel has the "Data Connection Wizard" which allows you to import or link from another data source or even within the very same Excel file.
As part of Microsoft Office (and OS's) are two providers of interest: the old "Microsoft.Jet.OLEDB", and the latest "Microsoft.ACE.OLEDB". Look for them when setting up a connection (such as with the Data Connection Wizard).
Once connected to an Excel workbook, a worksheet or range is the equivalent of a table or view. The table name of a worksheet is the name of the worksheet with a dollar sign ("$") appended to it, and surrounded with square brackets ("[" and "]"); of a range, it is simply the name of the range. To specify an unnamed range of cells as your recordsource, append standard Excel row/column notation to the end of the sheet name in the square brackets.
The native SQL will (more or less be) the SQL of Microsoft Access. (In the past, it was called JET SQL; however Access SQL has evolved, and I believe JET is deprecated old tech.)
Example, reading a worksheet:
SELECT * FROM [Sheet1$]
Example, reading a range:
SELECT * FROM MyRange
Example, reading an unnamed range of cells:
SELECT * FROM [Sheet1$A1:B10]
There are many many many books and web sites available to help you work through the particulars.
Further notes
By default, it is assumed that the first row of your Excel data source contains column headings that can be used as field names. If this is not the case, you must turn this setting off, or your first row of data "disappears" to be used as field names. This is done by adding the optional HDR= setting
to the Extended Properties of the connection string. The default, which does not need to be specified, is HDR=Yes
. If you do not have column headings, you need to specify HDR=No
; the provider names your fields F1, F2, etc.
A caution about specifying worksheets: The provider assumes that your table of data begins with the upper-most, left-most, non-blank cell on the specified worksheet. In other words, your table of data can begin in Row 3, Column C without a problem. However, you cannot, for example, type a worksheet title above and to the left of the data in cell A1.
A caution about specifying ranges: When you specify a worksheet as your recordsource, the provider adds new records below existing records in the worksheet as space allows. When you specify a range (named or unnamed), Jet also adds new records below the existing records in the range as space allows. However, if you requery on the original range, the resulting recordset does not include the newly added records outside the range.
Data types (worth trying) for CREATE TABLE: Short, Long, Single, Double, Currency, DateTime, Bit, Byte, GUID, BigBinary, LongBinary, VarBinary, LongText, VarChar, Decimal
.
Connecting to "old tech" Excel (files with the xls extention): Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyFolder\MyWorkbook.xls;Extended Properties=Excel 8.0;
. Use the Excel 5.0 source database type for Microsoft Excel 5.0 and 7.0 (95) workbooks and use the Excel 8.0 source database type for Microsoft Excel 8.0 (97), 9.0 (2000) and 10.0 (2002) workbooks.
Connecting to "latest" Excel (files with the xlsx file extension): Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Excel2007file.xlsx;Extended Properties="Excel 12.0 Xml;HDR=YES;"
Treating data as text: IMEX setting treats all data as text. Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Excel2007file.xlsx;Extended Properties="Excel 12.0 Xml;HDR=YES;IMEX=1";
(More details at http://www.connectionstrings.com/excel)
More information at http://msdn.microsoft.com/en-US/library/ms141683(v=sql.90).aspx, and at http://support.microsoft.com/kb/316934
Connecting to Excel via ADODB via VBA detailed at http://support.microsoft.com/kb/257819
Microsoft JET 4 details at http://support.microsoft.com/kb/275561
Unable to query dynamically named range on Excel sheet using ADO
As stated in the comments, the code that ended up working for me (for those of you that may find you have the same issue) was something along the lines of:
Set ExcelRecSet = ExcelCon.Execute("SELECT * FROM [" & wksName & "$" & _
wks.Range("tblData").Address(0, 0) & "]")
This definitely works, but if there was a way to select a variable range without having to reference it as above, I'd love to see the way to do that.
Thanks!!
Unable to query named range on sheet with spaces in name in Excel
Would it be possible to use an excel range instead of named range? I got the following to work:
SELECT * FROM [Report 1$A4:P]
I'm getting the sheet name from the GetOleDbSchemaTable() method and removing the apostrophes. The sheetname with apostrophes does not work for me with a range.
if (tableName.Contains(' '))
tableName = Regex.Match(tableName, @"(?<=')(.*?)(?=\$')", RegexOptions.None).Value + "$";
Performing SQL queries on an Excel Table within a Workbook with VBA Macro
One thing you may be able to do is get the address of the dynamic named range, and use that as the input in your SQL string. Something like:
Sheets("shtName").range("namedRangeName").Address
Which will spit out an address string, something like $A$1:$A$8
Edit:
As I said in my comment below, you can dynamically get the full address (including sheet name) and either use it directly or parse the sheet name for later use:
ActiveWorkbook.Names.Item("namedRangeName").RefersToLocal
Which results in a string like =Sheet1!$C$1:$C$4
. So for your code example above, your SQL statement could be
strRangeAddress = Mid(ActiveWorkbook.Names.Item("namedRangeName").RefersToLocal,2)
strSQL = "SELECT * FROM [strRangeAddress]"
Related Topics
Truncate Timestamp to Arbitrary Intervals
Why Does the Following Join Increase the Query Time Significantly
Using Start Date and End Date in Access Query
Sample Query to Show Cardinality Estimation Error in Postgresql
Column Reference Is Ambiguous in Postgresql Function
How to Build a Summary by Joining to a Single Table with SQL Server
Query JSON Dictionary Data in SQL
What Did Mongodb Not Being Acid Compliant Before V4 Really Mean
How to Check for Is Not Null and Is Not Empty String in SQL Server
How to Drop a Foreign Key Constraint Only If It Exists in SQL Server
How to Identify All Stored Procedures Referring a Particular Table
With (Nolock) VS Set Transaction Isolation Level Read Uncommitted
Oracle Delete Query Taking Too Much Time
How to Do "Where Exists" in Arel
How to Compare Two Columns for Equality in SQL Server
How to Detect If a String Contains Special Characters
Executing SQL Server Agent Job from a Stored Procedure and Returning Job Result
Return All Possible Combinations of Values Within a Single Column in SQL