Hi Paul, I believe Excel can be a nightmare to work with because of bugs to do with datatypes, see http://sites.encompassnow.com/idude/articles/using_excel_file_datasources.as p
You use the dollar sign in your code, see from above url "What about Tables in Excel? Excel Spreadsheets do not provide table objects for you to access, you will have to use one of the following in place of a table name. There are a number of ways you can reference a table (or range) in an Excel workbook: Workbook Name Use the Workbook Name followed by a dollar sign (for example, [Sheet1$] or [My Workbook$]). A Workbook table that is referenced in this manner consists of the entire used range of the Workbook. "Select [column_names] from [Sheet1$]" Cell Range Use the Workbook Name followed by a dollar sign with cell range appended to it! Don't forget to use the colon : between the starting and ending cell positions! "Select [column_names] from [Sheet1$A1:B10]" Named Range This is a range of cells with a defined name "Select [column_names] from [Defined Name Range]" " Happy Christmas Colm Colm Brazel MA CB Publications www.cbweb.net <http://www.cbweb.net> Macromedia-Dublin UserGroup Manager http://www.mmug-dublin.com [EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> [EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> Web Site Design & Development, Cold Fusion, general publishing including E-Books. The information contained in this e-mail transmission is confidential and may be privileged. It is intended only for the addressee(s) stated above. If you are not an addressee, any use, dissemination, distribution, publication,or copying of the information contained in this e-mail is strictly prohibited. If you have received this e-mail in error, please immediately notify CB Publications by telephone at 353-1-4941625 or e-mail [EMAIL PROTECTED] and delete the e-mail from your system. -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: 18 December 2003 12:31 To: [EMAIL PROTECTED] Subject: [ cf-dev ] Excel as a datasource Hi all, I am trying to use an excel spreadsheet as a datasource. I can get all the ODBC thingy setup OK I have this simple query <CFQUERY NAME= "q_schools" DATASOURCE= "SchoolsBudget"> SELECT DFESNo,SchoolName from Schools </CFQUERY> The workbook contains three sheets called Schools, Primary Budget and Secondary Budget As I understand it in laymans terms the name of the sheet refers to the name of the table. So I have a sheet named Schools. This has DFESNo & SchoolName as the top line and just a few lines of data beneath. What am I doing wrong? Regards - Paul **************************************************************************** ********************* The information contained within this e-mail (and any attachment) sent by Birmingham City Council is confidential and may be legally privileged. It is intended only for the named recipient or entity to whom it is addressed. If you are not the intended recipient please accept our apologies and notify the sender immediately, or telephone +(44) 121 303 6666. Unauthorised access, use, disclosure, storage or copying is not permitted and may be unlawful. Any e-mail including its content may be monitored and used by Birmingham City Council for reasons of security and for monitoring internal compliance with the office policy on staff use. E-mail blocking software may also be used. Any views or opinions presented are solely those of the originator and do not necessarily represent those of Birmingham City Council. We cannot guarantee that this message or any attachment is virus free or has not been intercepted and amended. **************************************************************************** ********************* -- ** Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/ To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] For human help, e-mail: [EMAIL PROTECTED] --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.552 / Virus Database: 344 - Release Date: 15/12/2003 -- ** Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/ To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] For human help, e-mail: [EMAIL PROTECTED]
