Excel charts, memory and opening link updates - tearing my hair out!! 1

[AlexmacG]

AlexmacG (TechnicalUser) Aug 12, 2004
Excel 97.

Cross posted from Excel forum.

I've a rather complicated series of linked workbooks/sheets and graphs show incorrect data values even though the source data series show the correct data 'bracketed', with the correct values such as 300, 400 500, but the chart column (stacked)individual values are incorrect e.g 100,6,55! I've tried recalculation [F9] and from within associated VBA code to no avail.......

Had a similar problem when one chart was linked to data in another workbook and this was cured by moving the chart to the source workbook, but I do not know why!

Is this possible due to to many graphs/data point or just memory. Are there limitations within Excel?


...since my original posting. More info. VBA code controls Excel file opening (not as objects), closing and advanced filtering of the source data in 5 workbooks and then allows a user to print the results via one word document using linked tables and charts from the open workbooks. This loops a number of times to match the number of sets of data

I have solved the problem, only in part, by keeping some graphs in workbooks along with their source data.......but now I have other problems !!!


When opening a word object with linked graphs from the same series of workbook files I now seem to get a message asking if I want the 'prime' workbook (i.e. the one with the code to filter data, open workbooks and the word object and print it) to be re-opened thus discarding any changes[ but not the rest of the open files!](This was after a change of PC which is very similar in spec to mine). If I keep saying no to the re-open prompt all is well eventually but it is frustrating to have to keep on 'keying/clicking OK'!

Also I cannot set the word object to background print without the document layout changing and thereby ruining the report!

Any ideas or methods to avoid all this will be most welcome.

PS
Win 2000 is up to date as is Office pro 97, to the best of my knowledge
Thanks for responses in advance.

 

[SkipVought]

AlexmacG,

What about Update Links?

You might want to consider using MS Query to retrieve the data you need from the Source Workbook. You can configure this workbook to not save the data but you'll have the query configured to retrieve a new set of data whenever the workbook opens. Then you can see exactly what you got for your chart's source data.

Skip,

_{[red]Be advised:[/red]
Alcohol and Calculus do not mix!
If you drink, don't derive!}

 

[AlexmacG]

Good bit of lateral thinking. Unfortunately I also have a problem with MS query and ISAM drivers !! Frustrating, eh?

However I think my problem is now more oriented to the linking of the data. I forgot to say I did try update links from code (and re calculate) but to no effect.

Any more ideas?