<cfset TheString "#TheColValue#">

should be

<cfset TheString = "#TheColValue#">

Looks pretty cool!

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>