Excel/Access Delimma - Suggestions Welcome

[Simon Jester]

I have inherited an Excel workbook with several worksheets containing
charts that get their source data via MSQuery from an Access
database. The users require that this stay in Excel so I have to find
a way to make it work.

The problem I am having is that whenever the user opens the workbook
they are prompted to tell it whether or not to allow updates. The sad
part of this is that we cannot rely on the numerous users to not
accidently push the wrong button and allow it to update, and thus
destroy the current data. I have to find a way that the user can open
the workbook, let it update, save the workbook, then be able to open
it again in the future without updating (and thus changing) the data
until the next month when the process starts over.

I tried finding a way to shut off the update prompt, but couldn't come
up with anything. I tried having the user save the workbook as a web
page, but it still asked if the user wanted to update the data. I
tried selecting the entire workbook, copying it, then doing a paste
special to values - I couldn't get that to work either. Does anyone
know of a way that my user can open the original workbook, save the
updated results as another file that does not try to update the data
every time it is opened? I'm open to suggestions.

Any suggestions would be extremely appreciated.

Thanks!
S.J.

 

[Gord Dibben]

Edit>Links>Break Links is probably all you need before you save.


Gord Dibben MS Excel MVP

 

[Simon Jester]

Thanks for your suggestion, I looked at edit - links, but it is grayed
out and after more reading about it, this data isn't linked from one
worksheet to another or one workbook to another, the data is external
imported data and I need to stop the "query refresh" warning from
appearing. Maybe - likely - I am misunderstanding what you are
saying.

 

[Gord Dibben]

Your original post stated the "update links" message was coming up.

My reply was directed toward that issue.

A message to "refresh query" is not the same as updating links.

Is the message actually "this workbook contains queries to external data that
refresh automatically" then asks you to enable or disable the auto-refresh?

Go to Data>Import External Data>Data Range Properties>Refresh Control. Uncheck
"Refresh data upon opening".

Save the workbook.

You can refresh manually if you choose at any time.


Gord

 

[Simon Jester]

Thanks again and sorry for my poor wording. I tried what you said,
but the "Query Refresh" screen still comes up.
From the research I have done, I have about come to the conclusion
that what I want to do can't be done. I am going to have to find some
kind of other
way of doing thid.
I do appreciate your help.
Thanks,
SJ

 

[Gord Dibben]

Don't know which version of Office you are running, but turning off the message
is certainly doable in version 2003.

I can experiment with a query from an Access DB and disable the message as I
pointed out.


Gord