1) Here's the CFML ... it's just what is in the .cfm page that runs the query and calls the tag.

<cfquery name="getdata" datasource="datasource_name" dbtype="ODBC"> select * from USERS </cfquery>

<cf_query2excel query="getdata">

Only it displays this in an excel page.

2) Thanks, I'll look for the browser thread....

Erika

"Money is not required to buy one necessity of the soul."-Henry David Thoreau

-------------------------------- AIM: WebErika5 Yahoo: WebErika MSN: WebErika AskMe.com Expert: WebErika -------------------------------- Erika L. Walker Vice President RUWebby, LLC 973-626-2412 (c) 973-244-9120 (o) 153 Rutgers Lane Parsippany, NJ 07054 -------------------------------- Website Design/Programming Database Integration Allaire Partner - ColdFusion --------------------------------

-----Original Message----- From: Thomas Chiverton [mailto:thomas.chiverton@exodus.net] Sent: Thursday, April 05, 2001 9:19 AM To: CF-Talk Subject: RE: cf_Query2Excell

If your getting CFML in the excel spreedsheet, what CFML are you getting ? As to forcing a browser to download content, see a previous thread on here :-)

-----Original Message----- From: Erika L Walker [mailto:elwalker@ruwebby.com] Sent: Thursday, April 05, 2001 1:54 PM To: CF-Talk Subject: RE: cf_Query2Excell

Some questions regarding this tag:

1) I did like you asked in the comments of the tag.... I simply created a page which had a link to a .cfm page that just contains my query and the call to the tag below it. When I click on the link though, all I get is my actual .cfm code (query and tag call) in an excel sheet, not the results of the query.

2) I'm running Win2K, Office 2k and IE 5.5 .. is there a way to stop the browser from opening up the excel file? It just displays it in the browser....and when you do a file save as,...it doesn't default to a .xls ...... sure, I can type this in, but my *less than technical clients* will always forget.

I hope I'm not being a bother this morning....

Erika

"Money is not required to buy one necessity of the soul."-Henry David Thoreau

-------------------------------- AIM: WebErika5 Yahoo: WebErika MSN: WebErika AskMe.com Expert: WebErika -------------------------------- Erika L. Walker Vice President RUWebby, LLC 973-626-2412 (c) 973-244-9120 (o) 153 Rutgers Lane Parsippany, NJ 07054 -------------------------------- Website Design/Programming Database Integration Allaire Partner - ColdFusion --------------------------------

-----Original Message----- From: Thomas Chiverton [mailto:thomas.chiverton@exodus.net] Sent: Thursday, April 05, 2001 8:27 AM To: CF-Talk Subject: RE: cf_Query2Excell

As the list strips attachments: <!--- NAME: CF_Query2Excel

AUTHOR: Ali Taleb

DATE: 3 March 2000

REV: 4/4/2001 Thomas Chiverton, thomas.chiverton@exodus.net Improved error handerling for blank fields in rows Takes note of Headers setting and uses that to order columns

DESCRIPTION: Cold Fusion custom tag to dump the result of an SQL select query to a downloadable Microsoft Excel spreadsheet which can have alternating row colors.

USAGE EXAMPLE (1): <CF_Query2Excel Query="GetEmployees">

USAGE EXAMPLE (2): <CF_Query2Excel Query="GetEmployees" Headers="Firt Name, Last Name, Date of Birth" AlternatColor = "ff0000">

USAGE EXAMPLE (3): <CF_Query2Excel Query="GetEmployees" AlternatColor = "green">

Where 'GetEmployees' is the query name.

ATTRIBUTES: Query - (Required) Name of query to be converted to Excel. Headers - (Optional) Comma delimited list of coloumn header names. Defaults to all query fields. Message - (Optional) Prompt link message to download the Excel File. Defaults to 'Download Results to Excel'. AlternateColor - (Optional) The rows can have alternating colours, white and a second colour defined by this attribute. Default is 'White'.

IMPORTANT: - As this tag invokes a download dialogue window (and hence not allow the actual page calling it to be displayed), It is best to place it in a .....cfm page containing just the query followed by the tag, and then linking to that page with an HTML link.

- The 'AlternateColor' Attribute MUST NOT begin with the number symbol '#', otherwise an error will be generated. There is no means of rectifying this in ColdFusion 4.01. as the problem is giving the custom tag attribute variable a value preceeding with '#', rather than just deleting the symbol which is an easy thing to do. As an example, For red you can type: AlternateColor = "red" or AlternateColor = "ff0000" but NOT AlternateColor = "#ff0000". --->

<cfif Not IsDefined("Attributes.Query")> <cfoutput><div align="center"><b>Error! 'Query' attribute is required in your custom tag.</b></div></cfoutput> <cfabort> </cfif>

<cfset QueryName = "Caller." & #Attributes.Query#> <cfset NumRows = Evaluate(QueryName & ".RecordCount")>

<cfparam name="Attributes.Headers" default=#Evaluate(QueryName & ".ColumnList")#> <cfset TheColumnList = #Attributes.Headers#>

<cfif ListLen(Evaluate(QueryName & ".ColumnList")) gt ListLen(Attributes.Headers)> <cfoutput><div align="center"><b>Error! There are more query fields than the items in the 'Headers' list.</cfoutput> <cfabort> <cfelseif ListLen(Evaluate(QueryName & ".ColumnList")) gt ListLen(TheColumnList)> <cfoutput><div align="center"><b>Error! There are more values in the 'Headers' list than the fields returned by the query.</cfoutput> <cfabort> </cfif>

<cfparam name="Attributes.AlternateColor" default="ffffff"> <cfparam name="Attributes.Type" default="application/vnd.ms-excel">

<cfset Counter = 1>

<cfloop query="#QueryName#">

<cfset TheValueList = "">

<cfloop list="#TheColumnList#" index="TheValue"> <cfset TheValue = #Trim(TheValue)#>

<cfset TheValue = #Evaluate(QueryName & "." & TheValue)#> <cfset TheValue = Replace(TheValue, ",", "¦¦", "All")>

<cfset TheValueList = ListAppend(TheValueList,TheValue)>

<cfset temp = SetVariable("ValueList#Counter#", "#TheValueList#")> </cfloop>

<cfset Counter = Counter + 1>

</cfloop>

<cfset AlternateColor = #Attributes.AlternateColor#>

<cfcontent type="#Attributes.Type#">

<cfset CharWidth = 10> <cfset numcols=0> <table border="1"> <tr bgcolor="#C0C0C0"> <cfloop list="#Attributes.Headers#" index="TheColTitle"> <cfset TheString = "#TheColTitle#"> <cfset TheLength = Len(#TheString#) * #charwidth#>

<cfoutput> <th width="#TheLength#"><b>#TheString#</b></th> </cfoutput> <cfset numcols=numcols+1> </cfloop> </tr>

<cfset ColorList = "ffffff,#AlternateColor#">

<cfloop from="1" to="#NumRows#" index="n">

<cfif ColorList is "ffffff,#AlternateColor#"> <cfset ColorList = "#AlternateColor#,ffffff"> <cfelse> <cfset ColorList = "ffffff,#AlternateColor#"> </cfif>

<cfloop list="#ColorList#" index="c"> <cfset CurrentColor = #ListGetAt(c,1)#> </cfloop>

<cfset temp = SetVariable("CurrentList", Evaluate("ValueList#n#"))>

<cfset CurrentList = Replace(CurrentList, ",,", ", ,", "All")> <cfset CurrentList = Replace(CurrentList, ",,", ", ,", "All")>

<cfoutput> <tr bgcolor="#CurrentColor#"> </cfoutput> <cfset num=0> <cfloop list="#CurrentList#" index="TheColValue"> <cfset num=num+1> <cfset TheString "#TheColValue#"> <cfset TheLength = Len(#TheString#) * #charwidth#>

<cfif Len(TheColValue) is 0> <cfset TheString = " "> </cfif>

<cfset TheString = Replace(TheString, "¦¦", ",", "All")>

<cfoutput> <td width="#TheLength#">#TheString#</td> </cfoutput>

</cfloop> <cfif #Evaluate(numcols - num)# neq 0> <cfloop from="1" to ="#Evaluate(numcols - num)#" index="i"> <td> </td> </cfloop> </cfif> </tr> </cfloop> </table>