Hi Giles, I'm back with the OLE DB instructions.
Yes indeed, you can do if/then constructs. It should be more full-featured in general.
OLE DB uses an underlying "provider" (database engine) to connect to your data file (in this case an Excel file). The two underlying database engines from Microsoft that support Excel files are the Jet Database Engine (the database engine used by MS Access in Office 95 through Office 2003) and the Microsoft Access Database Engine (replaces Jet in Office 2007 and 2010).
I think you would only use the Jet provider for compatibility with older systems. For one thing, it hasn't been updated and is designed for Excel 2003 and older files.
Here's a quick walkthrough in something like shorthand (for me) for adding a QueryTable linked to an Excel sheet, using the Microsoft Access Database Engine Provider. Tested in Excel 2010.
[pre]
Code:
================================================================================
[Get External Data]
DATA CONNECTION WIZARD
Other/Advanced
[NEXT]
DATA LINK PROPERTIES
Provider tab
Microsoft Office 12.0 Access Database Engine OLE DB Provider
[NEXT]
Connection tab
Data Source
C:PathToFileFilename.xlsx
[Advanced tab]
Access Permissions
Read
Share Deny None
[All tab]
Extended Properties
Excel 12.0
[Connection tab]
[Test Connection]
if success [OK]
DATA CONNECTION WIZARD
[uncheck "Connect to a specific table"]
[Finish]
if prompted to replace data connection file [YES]
SELECT TABLE
irrelevant, hit [OK]
IMPORT DATA
[Properties]
CONNECTION PROPERTIES
Connection Name
MergeQuery_Or_Whatever_You_Want
(QueryTable will be named "Table_ConnectionName")
::::::Optional::::::::::::::::::::::::::::::::::::::::
::: can skip to [ok] and edit query after to avoid :::
::: abort if sql statement error :::
......................................................
[Definition tab]
Command Type
SQL
Command Text
SELECT * FROM [Sheet Name$]
::::::::::::::::::::::::::::::::::::::::::::::::::::::
[OK]
THE CONNECTION IN THIS WORKBOOK...PROCEED? warning
[YES]
IMPORT DATA
[OK]
::::::Optional::::::
Right-click table, TABLE>External Data Properties>select options
Right-click table, TABLE>Edit Query>modify your SQL statement
Notes:
The [url=http://http://www.microsoft.com/download/en/details.aspx?id=13255]Office Access Database Engine 2010[/url] (download) can be installed freely on older systems that don't have it.
DATA LINK PROPERTIES/Extended Properties specifies the version of the Excel file. Excel 8.0 is Excel 97-2003 format. Excel 12.0 is Excel 2007-2011.
At DATA LINK PROPERTIES/Provider you could select the Microsoft Jet 4.0 Provider on systems that don't have the Access Database Engine installed. Maximum version for the Extended Properties is Excel 8.0 (although it read an .xlsx file for me).
================================================================================
[/pre]
It's not any more involved than using ODBC outside of the fact that you can't use Microsoft Query to help you design your query.
I did find an Excel Add In for this task that I haven't tried yet, but looks interesting, called
SQL Drill. Free/donation-ware. While I'm distracted from the particulars of your database task, I'll also mention another Add-In, for Excel and for Word, that I discovered in my research --
DBAddin -- which is free/open-source and adds all kinds of database functionality.
Back to your project:
Using OLE DB to connect to your Excel sheets, your flavor of SQL has changed. It is now, I believe, essentially the same flavor used by Microsoft Access and Microsoft SQL Server (so any documentation you read about that syntax should be pretty relevant).
Syntax highlights:
1. No more backticks. (Optionally) Enclose "database object" names, such as your table/sheet name and field names in brackets instead, like so:
Code:
[table].[sheet$]
2. No special quoting for object names that have spaces.[code][Bank A$] will do fine. In ODBC it was [code]~'Bank A$'~ (backticks for tildes).
3. Alias names can go in brackets just like the original table/field names. Never put them in quotes (like we did with ODBC).
4. You can use either single or double quotes to delimit literal strings of text, but the single quote is ANSI standard SQL. If you need to have the delimiter character as part of your string, use two of them ('I''d love to.'
becomes the text "I'd love to.")
5. Specify date and time literals using # as a delimiter. Dates should always be in m/d/y format, and times h:mm:ss. These are all acceptable: #5/16/2008# #5/16/08# #9:06:00# #21:06:30# #5/16/2008 9:06:00#[/code]
6. Expanded SQL syntax and capabilities.
7. Supported functions include:
Many/most Visual Basic for Applications functions
[pre]Iif(boolean,value-if-true,value-if-false)
Switch(boolean,value-if-true[,boolean,value-if-true[,...]])
Choose(index,value if 1[,value if 2[,...]])
IsNull(value) returns boolean http://bytes.com/topic/access/insights/716197-what-null
AVG(number) returns aggregate of all/grouped records
COUNT(field or * for records/rows) returns aggregate number of non-nulls
FIRST(value) returns value for first row
LAST(value) returns value for last row
MAX(number) returns aggregate of all/grouped records
MIN(number) returns aggregate of all/grouped records
SUM(number) returns aggregate of all/grouped records
Year(date)
Month(date)
Day(date)
Hour(time/date-time)
Minute(time/date-time)
Second(time/date-time)
Date() returns today's date
Time() returns current time
Now() returns current date and time
Weekday(date)
Format(date-time,format)
DateAdd(interval,number,date)
DateDiff(interval,date1,date2)
DatePart(interval,date-time)
DateSerial(year,month,day)
UCase(text string)
LCase(text string)
Mid(text string,start position,length)
Left(text string,length)
Right(text string,length)
Round(number,number of decimal places)
InStr(text string to search,text string to find) other syntaxes available too
InstrRev(text string to search,text string to find)
Int(number)
Fix(number)
Sgn(number) returns 1=positive, 0=zero, -1=negative[/code][/pre]
8. Supported expression operators include:
expr IS [NOT] NULL in excel, null means an empty cell
expr AND expr both
expr OR expr either
NOT (expr) expr is false
expr XOR expr one and only one expr is true
expr EQV expr both exprs true or both false
expr IMP expr false if 1st expr true but not 2nd, else true
expr [NOT] IN (expr1[,expr2[,...]]) among expr1, expr2...
expression [NOT] IN (query) among query results
ANY SOME EXISTS these are three more subquery operators
expr [NOT] LIKE expr comparison - wildcards ok in 2nd expr
= <> < > >= <= comparison
/ * + - arithmetic
+ concatenate text strings
& convert to text (and null to ''
and concatenate
