Re: Problem updating Excel using ADO

Have come across a component called XLSReadWriteII which allows read / write
access to Excel but more importantly it also gives you a calculate command
to force Excel to recalculate. That seems to be working nicely now!


"Betsy" <betsy.a.tainer@xxxxxxxxxxxxx> wrote in message
news:427a3fc8$1@xxxxxxxxxxxxxxxxxxxxxxxxx
> ok... let's call it a good place to start.
>
> There use to be a place in excel... (working from past memory) where you
> could tell it to refresh/recall external references/data/links (I think
> they
> called it links... when a spread*** had references to other
> spreadsheets... do you think that is what needs to happen? I can't seem
> to
> locate it... but I'm in a newer version of excel and am looking at a blank
> spread***, it might not give me that option... as I recall it was NOT
> under tools... (it was a long time ago)
>
> I'm pretty sure that unless the sheets are opened on the same desktop that
> the source file will have to be saved for the destination to read it's new
> values. Did you 'save' the spread*** that you made the changes in...
> maybe
> that needs to happen before the other one can pickup the changes.
>
> I'm thinking some combination of those will patch it up... you might have
> to
> write a script in excel that will tell it to refresh external references
> and
> run that script with a call out of Delphi... (yucky stuff! ... but
> doable.)
>
> b
> "S Campbell" <sandra.campbell@xxxxxxxxxxxxx> wrote in message
> news:4279d4e9@xxxxxxxxxxxxxxxxxxxxxxxxx
>> Unfortunately it's already set at automatic. I've had a look at all the
>> Excel options but couldn't see anything obvious - that's why I'm not sure
>> whether this is an excel problem or an ADO problem or me!!
>>
>>
>> "Betsy" <betsy.a.tainer@xxxxxxxxxxxxx> wrote in message
>> news:42795a12$1@xxxxxxxxxxxxxxxxxxxxxxxxx
>> > Hey S,
>> >
>> > I'd guess that in Excel under Tools | Options on the Calculation tab
> that
>> > you have 'Manual' selected with 'Recal... before save checked.
>> >
>> > Try changing that to 'Automatic' and see where it gets you...
>> >
>> > b
>> > "S Campbell" <sandra.campbell@xxxxxxxxxxxxx> wrote in message
>> > news:4278f6f7$1@xxxxxxxxxxxxxxxxxxxxxxxxx
>> >> I'm not sure if this problem is due to excel itself, or how I'm using
>> >> ADO.
>> >>
>> >> I have a spread*** with a couple of tables, the first has a few rows
> of
>> >> fixed numbers, and the second has values calculated using formulae
>> > including
>> >> values in the first table.
>> >>
>> >> In my Delphi application, I have set up an ADO connection to the
>> >> spread***, and I am reading the first table into an ADODataset. I'm
>> > then
>> >> using :-
>> >>
>> >> with adodataset1 do
>> >> begin
>> >> edit;
>> >> fields[5].Value := 900;
>> >> post;
>> >> end;
>> >>
>> >> to change one of the values in the table. The change is reflected in
>> >> a
>> > grid
>> >> and if I open up excel, I can see it's changed there also.
>> >>
>> >> However, if in my application I make the above change, then open the
>> > second
>> >> dataset which references the second table in the excel spread*** (
>> >> which
>> >> uses the value I've just changed in its formulae), the original values
>> >> are
>> >> still there. The formulae either aren't using the new value to
> calucate
>> >> their true values, or it's not 'getting' the new value.
>> >>
>> >> Wierdly enough, when I open the spread*** aftering doing the above,
> it
>> >> seems to recacluate as it's opening, as I can see the table changing
>> >> to
>> > new
>> >> values ( reflecting the change from before). It also asks me to save
> my
>> >> changes when I close the spead*** again ( if I don't, my application
>> > sees
>> >> the change in table 1 but not on the second table, but if I do save
>> >> it,
>> > the
>> >> application sees the changes on both tables??????).
>> >>
>> >> Does anyone have any idea what is going on here? I have used the edit
> ..
>> >> post calls to change cells in the spread*** with no difficulty, but
>> >> I
>> >> can't seem to be able to make the spread*** update calulcated
>> >> values.
>> >>
>> >> I have been struggling with this all day so I would greatly appreciate
>> >> any
>> >> help on this!
>> >>
>> >> Thanks
>> >>
>> >>
>> >
>> >
>>
>>
>
>


.