|
|
Magikaru,
Thanks for your query. I've learned something new. I was delighted to see this
work:
<cfquery name="excelDBhack" datasource="testAccessDsn">
select top 7 * from [Sheet1$] in 'C:\Documents and
Settings\BKBK\Desktop\excel_2003_tutorial_english.xls'[Excel 8.0;]
</cfquery>
<cfdump var="#excelDBhack#">
Unfortunately I couldn't find much information about it on the web. At least,
not structured information about queries against an Excel sheet via an MS
Access datasource. I'll sure look into it further.
In any case, this sheds new light on your original question. The reason the
data in the formula cells remains unchanged is because the query copies only
the data, not the formulas. One way out would be to forget Excel and proceed
further with database techniques.
For example, you could use a query of a query to update the data in the
formula cells. I'll illustrate with my own query. It has the columns [i]jan,
feb, mar, apr[/i] and a [/i]total[/i] column representing the formula [i]jan +
feb + mar + apr[/i]. To get the updated total, I simply did
<cfquery name="updatedTBL" dbtype="query">
select jan, feb, mar, apr, (jan+feb+mar+apr) as updatedTotal
from excelDBhack
</cfquery>
|
|