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

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

Regards

Rich

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" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>

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!

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" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>