Yep, sure thing. Bear with me--I didn't have anything to do with this
implementation, so I'm still just wading through the code on it. And I know
very little about COM objects, so... THANKS!
Here's the link that calls the page:
<a href="app/testing.cfm?ses_id=#url.ses_id#">Excel Report </a>
*****************************************************
Here's the testing.cfm page:
<cfquery name="GetAnything" datasource="#dbname#">
Select *
From cf_fpr.fpr_proj_main
</cfquery>
<html>
<head>
<title>Testing Excel tag</title>
</head>
<body>
<CF_Query2Excel Query="GetAnything"
AlternateColor = "ccccff">
</body>
</html>
******************************************************
Here's the custom tag:
<html>
<head>
<title>SQL Database Dump to Excel</title>
</head>
<body>
<!--- Surpress Output --->
<cfsetting enableCFoutputOnly = "Yes">
<!--- Database user name --->
<CFSET UserName = "username">
<!--- Database password --->
<CFSET Password = "passsword">
<!--- Datasource name --->
<CFSET Datasource = "datasource">
<!--- Excel Filename --->
<CFSET filename = "filename">
<!--- Setup Alpha References -- Add to go beyond 26 --->
<cfset alpha="A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z">
<cfset alpha_array = listToArray(alpha)>
<cfquery name="gettable" datasource="#datasource#">
SELECT sysobjects.id as tableid,
sysobjects.name as tablename,
syscolumns.name as columnname
FROM sysobjects, syscolumns
WHERE sysobjects.type = 'U'
AND sysobjects.id = syscolumns.id
ORDER by sysobjects.id
</cfquery>
<!--- Try to connect to the Excel application object --->
<CFTRY>
<!--- If it exists, connect to it --->
<CFOBJECT
ACTION="CONNECT"
CLASS="Excel.Application"
NAME="objExcel"
TYPE="COM">
<CFCATCH>
<!--- The object doesn't exist, so create it --->
<CFOBJECT
ACTION="CREATE"
CLASS="Excel.Application"
NAME="objExcel"
TYPE="COM">
</CFCATCH>
</CFTRY>
<CFSCRIPT>
// Open Excel in the background
objExcel.Visible = false;
// Disable client alerts such as: 'Save this workbook?'
objExcel.DisplayAlerts = false;
// Define the workbooks object
objWorkBook = objExcel.Workbooks;
// Add a new workbook
objOpenedBook = objWorkBook.Add();
</cfscript>
<!--- Set Defaults --->
<cfset currenttable = "">
<!--- Loop through tables --->
<cfloop query="gettable">
<!--- On table change --->
<cfif gettable.tableid neq currenttable>
<!--- If not first loop --->
<cfif currenttable neq "">
<!--- Process Field List --->
<cfset fieldlist = right(sqlquery, len(sqlquery) -
7)>
<cfset fieldlist = left(fieldlist, len(fieldlist) -
2)>
<!--- Build SQL Query --->
<cfset sqlquery = left(sqlquery, len(sqlquery)-2) &
" FROM " & oldtablename>
<!--- Add & Label Worksheet with Table Names --->
<cfscript>
// Get the WorkSheets' collection
objWorkSheets = objExcel.WorkSheets;
// Add a new worksheet (this will contain
our data)
objWorkSheet = objWorkSheets.Add();
objWorkSheet.Name = "#oldtablename#";
</cfscript>
<!--- Add Column Names --->
<cfset row = 1>
<cfset col = 0>
<cfloop list="#fieldlist#" index="item">
<cfset col = col + 1>
<cfset range = alpha_array[col] & row>
<cfset cellvalue = trim(item)>
<cfscript>
objRange =
objExcel.Range("#range#:#range#");
objRange.value = "#cellvalue#";
</cfscript>
</cfloop>
<!--- Create the ADO objects --->
<CFOBJECT ACTION="CREATE" CLASS="ADODB.Connection"
NAME="oConn" TYPE="COM">
<CFOBJECT ACTION="CREATE" CLASS="ADODB.Recordset"
NAME="oRst" TYPE="COM">
<!--- The SQL to run --->
<CFSET SQL = "#PreserveSingleQuotes(sqlquery)#">
<!--- Echo progress to screen --->
<cfoutput><h1>#oldtablename#</h1></cfoutput>
<cfflush>
<!--- Open the ADO connection and fetch the data
--->
<CFSET oConn.Open(Datasource, UserName, Password,
-1)>
<CFSET oRst.open(SQL, oConn, 3, 1, 1)>
<!--- Create the range and copy the records from ADO
--->
<CFTRY>
<CFSET objRange = objWorkSheet.Range("A2")>
<CFSET objRange.CopyFromRecordset(oRst)>
<cfoutput>Success!</cfoutput>
<CFCATCH>
<cfoutput>Error!</cfoutput>
</CFCATCH>
</CFTRY>
<!--- Kill the ADO objects --->
<CFSET oRst.close()>
<CFSET oConn.close()>
</cfif>
<!--- Reset Query Builder --->
<cfset currenttable = gettable.tableid>
<cfset sqlquery = "SELECT ">
</cfif>
<!--- Add Fields to Query --->
<cfset sqlquery = sqlquery & gettable.columnname & ", ">
<!--- Update "oldtablename" --->
<cfset oldtablename = gettable.tablename>
</cfloop>
<!--- Close & Save Excel --->
<cfscript>
// SaveAs() does not work with workbooks, only with worksheets
objWorkSheet.SaveAs("#filename#",Val(1));
// Close the document
objWorkBook.Close();
// Quit Excel
objExcel.Quit();
// Release the object
objExcel = "Nothing";
</cfscript>
<!--- Surpress Output --->
<cfsetting enableCFoutputOnly = "No">
</body>
</html>
-----Original Message-----
From: Douglas Brown [mailto:[EMAIL PROTECTED]]
Sent: Friday, May 10, 2002 3:18 PM
To: CF-Talk
Subject: Re: weird query to excel problem
Could we see your code please? The query and the calling of the
tag.
Douglas Brown
Email: [EMAIL PROTECTED]
----- Original Message -----
From: "Stocke Terri" <[EMAIL PROTECTED]>
To: "CF-Talk" <[EMAIL PROTECTED]>
Sent: Friday, May 10, 2002 11:52 AM
Subject: weird query to excel problem
> Hey everyone!
>
> I'm on a tight deadline to implement a query to excel feature
for a
> customer. One of our developers used the query2excel custom tag
to do this.
> It works great for her, and it works great for another person in
our
> department who tested it. But when I try to use it, I find the
following:
>
> If I have Excel open when I click on the link to generate the
Excel file,
> everything works fine. BUT...
>
> If I don't have Excel open when I click on the link, I get a Dr.
Watson
> error that states"
>
> "An application error has occurred and an application error log
is being
> generated.
> excel.exe
> Exception: access violation (0xc0000005), Address:
(blahblahblah)"
>
> Why is this, and how do I fix it? It doesn't seem to happen to
everyone.
>
> Thanks!
>
> Terri
>
>
______________________________________________________________________
This list and all House of Fusion resources hosted by CFHosting.com. The place for
dependable ColdFusion Hosting.
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
