No problem at all Brian :)
I too have the code placed at the end of my COM-related code. I've just
noticed, however that you are using the cf_excel2csv tag, which should
render the code redundant (although there's no harm in leaving it there).
I've pasted some code below for you to look at:
________________________________________________
<cfset file="D:\datasource\finance.xls">
<!--- 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>
// Get the workbooks collection //
objBooks = objExcel.Workbooks;
// Open and activate the workbook //
objBook = objBooks.Open(File);
objBook.Activate();
// Get the sheets collection //
objSheets = objBook.WorkSheets;
// Get a named sheet //
objSheet = objSheets.Item("apr");
// Create the range and copy the records from ADO //
objRange = objSheet.Range("B2:B2");
objRange.Value=#quotation.term#;
objRange = objSheet.Range("C2:C2");
objRange.Value=-#monthlyadj#;
objRange = objSheet.Range("D2:D2");
objRange.Value=#loan_amt#;
objRange = objSheet.Range("E2:E2");
objRange.Value=#quotation.apr#/100;
// Save the file //
objSheet.SaveAs(File, Val(1));
// Kill the ADO and Excel objects //
objBook.Close();
</cfscript>
<CFSET CFUSION_DISABLE_DBCONNECTIONS("finance", 1)>
<CFSET CFUSION_DISABLE_DBCONNECTIONS("finance", 0)>
------------------------------------------------------
In your case the custom tag is the wrapper for the cfscript part of my code.
Note mine doesn't convert to csv. The code above is used for initiating a
successive approximation within excel calculating APR for a loan.
HTH,
Dave
-----Original Message-----
From: Brian Ferrigno [mailto:[EMAIL PROTECTED]]
Sent: 30 September 2002 17:48
To: CF-Talk
Subject: RE: COM help
Hi Dave,
Sorry about being a pest but where exactly do you have to place the code you
described below? I placed it after the call to the custom tag that creates
the csv file. Does it need to be before or wrap around the call?
<cf_excel2csv
excelFileIn="C:\Inetpub\wwwroot\clients\mysite\import\uploads
contracts2.xls"
CSVFileOut="C:\Inetpub\wwwroot\clients\mysite\import\uploads\ExcelFile.txt">
>
<CFSET CFUSION_DISABLE_DBCONNECTIONS("datasourcename", 1)>
<CFSET CFUSION_DISABLE_DBCONNECTIONS("datasourcename", 0)>
Thank you very much for the help.
Brian
-----Original Message-----
From: Dave Wilson [mailto:[EMAIL PROTECTED]]
Sent: Monday, September 30, 2002 12:13 PM
To: CF-Talk
Subject: RE: COM help
Brian,
Although I've experienced pretty much what you describe, I've never seen the
process actually killing the CF Service itself.
To fix the problem of inactivity in the excel part of the process on
subsequent attempts, use the following:
<CFSET CFUSION_DISABLE_DBCONNECTIONS("datasourcename", 1)>
<CFSET CFUSION_DISABLE_DBCONNECTIONS("datasourcename", 0)>
Which should help to kill any locked connections to the sheet.
HTH,
Dave
-----Original Message-----
From: Brian Ferrigno [mailto:[EMAIL PROTECTED]]
Sent: 30 September 2002 16:40
To: CF-Talk
Subject: COM help
I need help tracking down an error in using CFOBJECT to create a csv file
from an excel spreadsheet.
I am using CF to open up an excel spreadsheet and then trying to turn the
data into a csv file. The code i'm using works fine the first time the
application is launched but on subsequent tries nothing happens. According
to the company hosting my site COM is causing the CF service to fail then it
has to be restarted. I'm listing the code i'm using below.
Is this an issue with a patch needing to be installed or a problem with the
code below? Any help would much appreciated. Thanks.
-Brian
Stats
------
CF version CF5.0
IIS
Windows 2K server
Code
----
<cftry>
<cfobject
action="CONNECT"
class="Excel.Application"
name="objExcel"
type="COM">
<cfcatch>
<cftry>
<cfobject
action="CREATE"
class="Excel.Application"
name="objExcel"
type="COM">
<cfcatch type="Object">
Cannot create Excel Object
<cfabort>
</cfcatch>
</cftry>
</cfcatch>
</cftry>
<cftry>
<cfscript>
// this code will open up the excel spread sheet and then save it as a CSV
file.
// HTML file path
ExcelFilePath = "#attributes.excelFileIn#";
// Destination path for new Word document
// We leave off the extension since we'll declare the file type later on
CSVFilePath = "#attributes.CSVFileOut#";
// Open Word in the background
objExcel.Visible = false;
// Disable alerts such as: 'Save this document?'
objExcel.DisplayAlerts = false;
// Get the 'Documents' collection
objBooks = objExcel.workbooks;
// Open the HTML document
objBook = objBooks.Open(ExcelFilePath);
// Get the sheets collection
objSheets = objBook.WorkSheets;
// Get the first sheet
objSheet = objSheets.Item(Val(1));
// Get a named sheet
objSheet = objSheets.Item("#attribtues.sheetName#");
/* Save it as a new document -- the extension will automatically
be appended based on the file type we choose.
Some of the file types to convert to:
1 = .xls
3 = .txt (tab delimetered
6 = .csv
*/
objSheet.SaveAs(CSVFilePath, Val(6));
// The next line would convert it to RTF instead
// objSpreadSheet.SaveAs(CSVFilePath,Val(6));
// Close the document
objBook.Close();
// Quit Word
objExcel.Quit();
if (attribtues.showConfirmation EQ 1) {
writeOutput("<font size=""-1""
face=""Verdana,Geneva,Arial,Helvetica,sans-serif""><b>File Created:</b>
""#attributes.CSVFileOut#"" <b>from file:</b>
""#attributes.excelFileIn#""<p></font>");
}
</cfscript>
<cfcatch>
<!--- error creating file --->
<font face="Verdana,Geneva,Arial,Helvetica,sans-serif" size="-1">there was
an error creating your new CSV filesfrom your Excel Spreadsheet</font>
</cfcatch>
</cftry>
______________________________________________________________________
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
