updating cells which containing formulae via odbc
 

I have a spreadsheet which was provided by a 3rd party to allow import
into their financials sql server database - unfortunately editing the
spreadsheet would break our support contract. It has a few columns
which have "current" and "revised" headings, which by default have a
formula in the "revised" column which simply sets it equal to the
"current" column. It was intended at design time that humans type over
the "revised" cells with real numbers, save the sheet and it gets
uploaded to the database. This works fine.

However, it has suddenly become desirable to update these cells via a
program which is exporting from a project management database via a
Java API/ODBC! No problem if the "revised" cells have already been
overwritten with numbers by humans, but if they still contain the
original formula, odbc resolutely says "field not updatable".

Does anyone have any possible hints before my brain implodes - or am I
wasting my time?

Thanks for any comments

ade


--
arl
------------------------------------------------------------------------
arl's Profile: http://www.excelforum.com/member.php...o&userid=24917
View this thread: http://www.excelforum.com/showthread...hreadid=384564

updating cells which containing formulae via odbc
 

Tried using the delete method on the cell before you update it ??

Regards

Rich


--
Rich_z
------------------------------------------------------------------------
Rich_z's Profile: http://www.excelforum.com/member.php...o&userid=24737
View this thread: http://www.excelforum.com/showthread...hreadid=384564

updating cells which containing formulae via odbc
 

Didn't you say that editing the spreadsheet would break your support
contract?

If you need to replace formulas with values via ODBC then why not remove the
problem by doing a one-time "copy pastespecial values" on the
spreadsheet to replace all the formulas with values?

Tim.

--
Tim Williams
Palo Alto, CA


"arl" wrote in message
...

I have a spreadsheet which was provided by a 3rd party to allow import
into their financials sql server database - unfortunately editing the
spreadsheet would break our support contract. It has a few columns
which have "current" and "revised" headings, which by default have a
formula in the "revised" column which simply sets it equal to the
"current" column. It was intended at design time that humans type over
the "revised" cells with real numbers, save the sheet and it gets
uploaded to the database. This works fine.

However, it has suddenly become desirable to update these cells via a
program which is exporting from a project management database via a
Java API/ODBC! No problem if the "revised" cells have already been
overwritten with numbers by humans, but if they still contain the
original formula, odbc resolutely says "field not updatable".

Does anyone have any possible hints before my brain implodes - or am I
wasting my time?

Thanks for any comments

ade


--
arl
------------------------------------------------------------------------
arl's Profile:
http://www.excelforum.com/member.php...o&userid=24917
View this thread: http://www.excelforum.com/showthread...hreadid=384564

updating cells which containing formulae via odbc
 

thanks for the replies. First off yes I did try delete, but the excel
odbc driver reports "delete is not supported by this ISAM", and indeed,
this is confirmed on the MSDN website.

secondly, yes, I did say editing the spreadsheet would break our
support contract, but what I guess I didn't communicate very well is
that entering data into unprotected fields doesn't count as editing,
while pasting values into the formulae cells ... well, I could maybe
get away with that, now I come to think of it - trouble is I can't
really change the template file which is where the problem lies. Is
there any way of doing this via ODBC rather than manually doing it for
each spreadsheet generated from the template?

As you may have guessed, I'm not an Excel expert, and any help is
greatly appreciated!


--
arl
------------------------------------------------------------------------
arl's Profile: http://www.excelforum.com/member.php...o&userid=24917
View this thread: http://www.excelforum.com/showthread...hreadid=384564

updating cells which containing formulae via odbc
 

I guess I'm not clear on the layout of the problem workbook, or the process
you're trying to establish, so I couldn't really offer any more advice other
than
1. Try some method other than ODBC, such as a component which supports
writing to Excel files. Quite a few available for COM programming, but I'm
not a Java person so I have no idea what's available in that area.
2. Ask the 3rd party to provide a modified form of the template with values
instead of formulas in the "revised" fields. They shouldn't have a problem
with this.

Tim.




--
Tim Williams
Palo Alto, CA


"arl" wrote in message
...

thanks for the replies. First off yes I did try delete, but the excel
odbc driver reports "delete is not supported by this ISAM", and indeed,
this is confirmed on the MSDN website.

secondly, yes, I did say editing the spreadsheet would break our
support contract, but what I guess I didn't communicate very well is
that entering data into unprotected fields doesn't count as editing,
while pasting values into the formulae cells ... well, I could maybe
get away with that, now I come to think of it - trouble is I can't
really change the template file which is where the problem lies. Is
there any way of doing this via ODBC rather than manually doing it for
each spreadsheet generated from the template?

As you may have guessed, I'm not an Excel expert, and any help is
greatly appreciated!


--
arl
------------------------------------------------------------------------
arl's Profile:
http://www.excelforum.com/member.php...o&userid=24917
View this thread: http://www.excelforum.com/showthread...hreadid=384564