I wonder if anyone has any suggestions to the following?

We have an Excel Spreadsheet that obtains the data to post into cell $A
$2 onwards from a query built in Access.  The end users hit the '!' 
update button to gather the data and see the current state of items 
booked in for service.

The problem that occasionally occurs, seemingly at random, is that the 
results when pasted into Excel by MS Query are suddenly formatted as 
text cells, overwriting the query itself in the spreadsheet and thus 
destroying the link to Access so that no further updates can be done 
(the '!' button is subsequently greyed out).  The end users may also 
save the spreadsheet and so next time around cannot update the data.

BTW it's not as simple as the end users clicking out of the cells where 
the query is resulting in the greyed out '!' update button, the cells 
are text complete with the little green 'tag' top left of the cell (in 
Office 2003).

Work around at the moment is to have a copy of the spreadsheet to 
recover (to save building the query again, a stage too far for the end 
users to cope with).

This problem has persisted for a while and affects Excel Office 2000 and 
Excel Office 2003.  The Access database was designed for Access 2000 but 
now Office 2003 is installed.

The query properties are set to "Insert cells for new data, delete 
unused cells." in case that matters.

Grateful for any suggestions.

- MDW.

On Wed, 16 Jul 2008 11:22:05 -0500, Matt Wilson
 wrote:

>The problem that occasionally occurs, seemingly at random, is that the 
>results when pasted into Excel by MS Query are suddenly formatted as 
>text cells, overwriting the query itself in the spreadsheet and thus 
>destroying the link to Access so that no further updates can be done 
>(the '!' button is subsequently greyed out).  The end users may also 
>save the spreadsheet and so next time around cannot update the data.
>

I've never seen anything like that before in the thousands of Access based
queries that I've done.  That leads me to believe that it's one of two
things: user error or a macro.

User error, usually the  problem, doesn't seem to fit here.  I could see a
user unchecking the "Save Query Definition" checkbox in the Properties
dialog once, but not multiple times.  And it wouldn't explain why you're
getting green triangles.  Removing the definition would definitely disable
the Refresh button, but it would convert numbers into text.

That leaves an errant macro.  First check all of add-ins that you have
installed (Tools > Addins).  Then check for COM Add-ins.

http://www.dailydoseofexcel.com/archives/2008/07/16/determine-which-com-add-ins-are-installed/

I don't know of any commercially available add-ins that would cause this,
but if you want to list any that you have installed, someone may see
something suspect.

Until you get it sorted, it's a pretty trivial macro to set up that
QueryTable.  You could put a button on the sheet that says "Recreate Query"
that runs the macro instead of saving a copy.
-- 
Dick Kusleika
Microsoft MVP-Excel
http://www.dailydoseofexcel.com

Dick Kusleika  wrote in
microsoft.public.excel.querydao: 

> I've never seen anything like that before in the thousands of Access
> based queries that I've done.  That leads me to believe that it's one
> of two things: user error or a macro.

Thanks for your interesting post.  I've not seen anyone else with this 
problem, or at least anything posted about it on searching the Internet, 
either.

The analysis toolpak add-in is installed.  There are no items listed in 
the COM Add-ins dialogue box.

The problem occurs across PCs including my laptop and two other PCs used, 
all XP Pro with Office 2003 (but it used to occur with Office 2000 when we 
had that too).  

I can recreate the problem what appears to be at random without adjusting 
the 'Save Query Definition' checkbox.

The cells are overwritten as text cells immediately after the '!' button 
(or indeed the other refresh all button) is pressed.  

Playing around some more I have now noticed that when this occurs, the 
'Query from MS Access' name is removed from the drop down 'name box' list.  
Looking at the name (insert | name | define) and clicking on the entry 
'Query from MS Access Database' shows =  'Repairs'!$A$2:$L$218 - note some 
spaces (or other non printing character) have appeared between the '=' and 
the range of cells. (The sheet is called Repairs).

If I delete/remove the named range from that dialogue box it 'manually' 
recreates the problem. If I manually edit the refers to and add three or 
four spaces between the '=' and the range it also recreates the problem.

Removing the 'spaces' when the problem occurs I think resolves the missing 
'!' refresh button. 

So something odd with MS Query and interactions with named ranges.

Another thought - talking to end users they suggest 'if we use the 
spreadsheet a lot then it's more likely to occur' and they also said 'it 
appears to happen more frequently if we have the Access database open 
too' (the users use forms in this to enter data). 

In Excel's data range properties 'enable background refresh' is 
ticked/checked but no other parameters under refresh control are set. I 
wonder if this has anything to do with it, where a background refresh is 
done after a period of time of the spreadsheet being opened, which then 
causes the problem with the named ranges?  End users are likely to leave 
the spreadsheet open when busy and then update the records again later.

I might try unticking the 'enable background' option on one of the 
machines to see what happens.

Again thanks for your post, good little mystery isn't it.

I also like the idea of a macro to put the query back, good one.  And that  
http://www.dailydoseofexcel.com/ website looks very interesting, I think I 
might find myself spending a few hours browsing that!

- MDW.

On Thu, 17 Jul 2008 05:02:36 -0500, Matt Wilson
 wrote:

>
>Again thanks for your post, good little mystery isn't it.
>

Yes, it's very interesting.  I would like to try to recreate this if
possible.  I wonder if you'd mind sending me the mdb and xls files to
dkusleika@gmail.com.  Of course I would treat the data as confidential and I
understand if you aren't able to send it.
-- 
Dick Kusleika
Microsoft MVP-Excel
http://www.dailydoseofexcel.com

Dick Kusleika  wrote in 
microsoft.public.excel.querydao:

> Yes, it's very interesting.  I would like to try to recreate this if
> possible.

Thanks for that, you should have an email...

- MDW.

On Thu, 17 Jul 2008 11:40:28 -0500, Matt Wilson
 wrote:

>Dick Kusleika  wrote in 
>microsoft.public.excel.querydao:
>
>> Yes, it's very interesting.  I would like to try to recreate this if
>> possible.
>
>Thanks for that, you should have an email...

Yep, no problem recreating it.  I refreshed about 10 times with nothing.
Then the Refresh button went grey.  I didn't get any green triangles, but
clearly something is wrong here.  I closed without saving and tried to do it
again.

I refreshed 105 times (via UI and macro) and nothing.  Four hours later, I
refreshed again and it broke.  The defined name has the spaces in it just as
you described.  Both times 'security.mdb' was open, but I don't know if
that's statistically significant yet.

It bothers me that I can't do it reliably.  I wonder if it *ever* happens
when Access is closed.  My current theory is that some macro in Access is
screwing things up.  I have no evidence or even a logical sequence of events
that this is true, but since I'm grasping at straws I'm not too worried
about evidence and logic.

I'll keep investigating, but I think the next step will be me submitting a
bug to MS to see what they say.  I'd like to start with a fresh Excel
workbook and mdb file, but I have a feeling I wouldn't be able to recreate
it under those circumstances.

Have you ever rebuilt the mdb file?  What do you think about transfering all
of the worksheets to a new workbook and starting a new mdb?  It sounds like
a big job, but I don't think it would be too bad.
-- 
Dick Kusleika
Microsoft MVP-Excel
http://www.dailydoseofexcel.com

On Sun, 20 Jul 2008 22:33:34 -0500, Dick Kusleika 
wrote:

>
>Yep, no problem recreating it.  I refreshed about 10 times with nothing.
>Then the Refresh button went grey.  I didn't get any green triangles, but
>clearly something is wrong here.  I closed without saving and tried to do it
>again.
>

Steps to reproduce:  Open service.mdb.  Open the xls file.  Run this macro:

Sub testrefresh()
    
    Dim i As Long
    
    For i = 1 To 1000
        Sheet1.QueryTables(1).Refresh False
    Next i
    
End Sub

Close the xls file and Yes to save the changes.  Reopen the xls file.  Click
the refresh button on the External Data toolbar.  It fails every time.

No, this isn't an answer, but we're getting closer.
-- 
Dick

On Sun, 20 Jul 2008 22:58:48 -0500, Dick Kusleika 
wrote:

>On Sun, 20 Jul 2008 22:33:34 -0500, Dick Kusleika 
>wrote:
>
>>
>>Yep, no problem recreating it.  I refreshed about 10 times with nothing.
>>Then the Refresh button went grey.  I didn't get any green triangles, but
>>clearly something is wrong here.  I closed without saving and tried to do it
>>again.
>>

Here's what I've tried, none of which fixed it.
Compact and Repair database
Convert database to Access 2002 - 2003 format
Created a new database and imported all of the objects
Created a new workbook and recreated all of the sheets and all of the
querytables (in code)

Here's what does "work":  If after I reopen the xls, I immediately inspect
the range name (Insert - Name - Define - Cancel).  Then I can refresh *any*
querytable without error.

I've asked some other people to look at and I'll report back when I hear
something.
-- 
Dick

Dick Kusleika  wrote in
microsoft.public.excel.querydao: 

> I've asked some other people to look at and I'll report back when I
> hear something.

Glad you have been able to reproduce this and thanks very much for your 
efforts, sorry that I've not replied for a while been on holiday.

My only comments is that from observations here it does depend on the 
amount of time the Excel spreadsheet has been opened.  

Since telling the end users here to close it when they are not using it 
plus also removing the option for 'enable background refresh' in Excel | 
Data | Import External Data | Data range properties (and having all the 
options for 'Data range control unticked/unchecked) there have been no 
more occurrances - although as you've notice there is a 'random' element 
to it.

Don't you love computers! ;-)

Cheers again,

- MDW.