I am going to get around to doing something similar. If you gain any insight
please post to the list - good reading.
I am using excel web queries quite a bit to distribute web information to
spreadsheets. Excel has some good reporting capabilities, so I find the
combination quite useful. (I use the ID=sometable table attribute to
identify data tables I want to make available for download to excel.)
? Can you do web queries in the Open Office spreadsheet?
I have been trying to figure out an easy way to post a file directly from
excel. Interested in any ideas.
The suggestion to write some VB to create and post an XML file is pretty
functional. And might be the best way, but I want to keep the client side
pretty generic and have a very basic spreadsheet format for bulk imports. ie
upload a XLS spreadsheet or CSV with any or all of these column headers.
Summary - I am guessing you can use VB to post an open file to the web. Not
sure of the code though. If anyone has any samples it is appeciated.
Eric
From: [EMAIL PROTECTED]
Reply-To: [EMAIL PROTECTED]
To: CF-Talk <[EMAIL PROTECTED]>
Subject: Re[2]: excel as a vehicle to collect and write back data to a
CF-App
Date: Sun, 29 Dec 2002 13:54:03 +0100
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
Your ad could be here. Monies from ads go to support these lists and provide more
resources for the community. http://www.fusionauthority.com/ads.cfm
