It sounds like it may simply be an excel problem...You may want to consider reinstalling excel and see what happens. It is weird that it is giving you a access violation. Have you rebooted recently? It may be trying to reference a workbook that the OS thinks is still open.
Douglas Brown Email: [EMAIL PROTECTED] ----- Original Message ----- From: "Stocke Terri" <[EMAIL PROTECTED]> To: "CF-Talk" <[EMAIL PROTECTED]> Sent: Friday, May 10, 2002 1:49 PM Subject: RE: weird query to excel problem > Nope, they don't get the error. I asked them whether they tried it with > Excel open or closed, and they told me that it works either way for them. > Weird, huh? > > > -----Original Message----- > From: Douglas Brown [mailto:[EMAIL PROTECTED]] > Sent: Friday, May 10, 2002 4:59 PM > To: CF-Talk > Subject: Re: weird query to excel problem > > > I do not notice right off the bat any problems. Have you had the > other people try the same thing? Do they get the same error > running your code on their machine? > > > > > > > > Douglas Brown > Email: [EMAIL PROTECTED] > ----- Original Message ----- > From: "Stocke Terri" <[EMAIL PROTECTED]> > To: "CF-Talk" <[EMAIL PROTECTED]> > Sent: Friday, May 10, 2002 1:35 PM > Subject: RE: weird query to excel problem > > > > 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 > > > > > > > > > > > > > > > ______________________________________________________________________ Get the mailserver that powers this list at http://www.coolfusion.com 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
