Hi Dave, thank you very much.
That helped a lot.
I somehow thought it before I started
this question that the whole process is much
of a pain. :-(
Uwe
Sunday, December 29, 2002, 1:14:02 PM, you wrote:
DB> I have done more or less the same thing with my customers. It's a lot less
DB> taxing on the server to create the spreadsheet from HTML/CSV and have it run
DB> on the client than it is to manipulate it via an MS Office object on the
DB> server, but you end up doing both. I create the look I want for my Excel
DB> spreadsheet in HTML and save it as an HTML file then open it up in an Excel
DB> object on the server:
DB> <!--- Try to connect to the Excel object --->
DB> <CFINCLUDE TEMPLATE="/_PM/payroll/com_get_excel_object.cfm">
DB> <!--- Try to open the HTML file --->
DB> <CFINCLUDE TEMPLATE="/_PM/payroll/com_open_excel_file.cfm">
DB> <CFTRY>
DB> <CFSCRIPT>
DB> /* Get the first sheet */
DB> objWorkSheet = objWorkSheets.Item(Val(1));
DB> objWorkSheet.Name = SheetName;
DB> </CFSCRIPT>
DB> <CFCATCH TYPE="ANY">
DB> <CFINCLUDE
TEMPLATE="/_PM/payroll/com_close_and_quit_excel.cfm">
DB> <CFABORT
DB> SHOWERROR="Error occured while trying to get the
active worksheet! Error
DB> Details: #CFCATCH.MESSAGE# #CFCATCH.DETAIL#"
DB> >
DB> </CFCATCH>
DB> </CFTRY>
DB> Then I just save it as an Excel spreadsheet:
DB> <CFTRY>
DB> <CFSCRIPT>
DB> /* Save the file */
DB> objWorkSheet.SaveAs(ExcelFilePath, Val(1));
DB> </CFSCRIPT>
DB> <CFCATCH TYPE="ANY">
DB> <CFINCLUDE
TEMPLATE="/_PM/payroll/com_close_and_quit_excel.cfm">
DB> <CFABORT
DB> SHOWERROR="Error occured while trying to save the
HTML as an Excel file!
DB> Error Details: #CFCATCH.MESSAGE# #CFCATCH.DETAIL#"
DB> >
DB> </CFCATCH>
DB> </CFTRY>
DB> When the user is done filling in the workbook, they upload it via a form and
DB> then I query the sheet:
DB> <CFFILE
DB> ACTION="UPLOAD"
DB> FILEFIELD="Excel_Spreadsheet_Path"
DB> DESTINATION="#ExcelFilePath#"
DB> NAMECONFLICT="OVERWRITE"
DB> ACCEPT="application/vnd.ms-excel"
DB> >
DB> <CFSCRIPT>
DB> ExcelODBCConnectionString = "Driver={Microsoft Excel Driver
DB> (*.xls)};DriverId=790;Dbq=" & ExcelFilePath & ";DefaultDir=" &
DB> GetDirectoryFromPath(ExcelFilePath) & ";";
DB> </CFSCRIPT>
DB> <CFQUERY
DB> NAME="GetDataSheet"
DB> DBTYPE="dynamic"
DB> BLOCKFACTOR="100"
DB> CONNECTSTRING="#ExcelODBCConnectionString#"
DB> >
DB> SELECT *
DB> FROM "#SheetName#$"
DB> WHERE (Users_ID IS NOT NULL)
DB> </CFQUERY>
DB> It took a long time to figure out how to read the complete set of an
DB> arbitrary number of columns and associate it with the correct row. Also,
DB> validation is a real pain. I'm still working on vbscript validation that I
DB> can convert from HTML, but haven't figured out how to do it yet.
DB> -----Original Message-----
DB> From: CF-Talk [mailto:[EMAIL PROTECTED]]
DB> Sent: Saturday, December 28, 2002 10:34 PM
DB> To: [EMAIL PROTECTED]
DB> Date: Sat, 28 Dec 2002 22:00:48 +0100
DB> From: [EMAIL PROTECTED]
DB> Subject: excel as a vehicle to collect and write back data to a CF-App
DB> Message-ID: <[EMAIL PROTECTED]>
DB> I have a customer,
DB> who runs an application,
DB> where he can show, who
DB> is a a promotor or a consultant
DB> for certain products he is selling.
DB> The application runs on: CF 4.5, Win NT 4 Server
DB> with IIS 4 and MS-Access as a backend.
DB> Updating and deleting data works on
DB> a user-data-record base via web-frontend. Now the
DB> customer wants to deliver certain data, that
DB> has to be edited by certain responsible users.
DB> He wants to deliver the lists, the people should
DB> use in excel. What he wants is, that he collects
DB> all the excel-lists and rewrite the changed records he got
DB> back from the excel-lists and transferring them (via CF) to MS-Access.
DB> Some aspects how to do this are clear, but can somebody
DB> give me some advice on what the pitfalls are on scripting those templates,
DB> maybe
DB> somebody who has done more or less the same ?
DB> Would be great.
DB> Thanks.
DB> Uwe
DB>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription:
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Signup for the Fusion Authority news alert and keep up with the latest news in
ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm
