Running MS Query returns results formatted as text
Matt Wilson
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.
Dick Kusleika
<fz...@userot21andtakethistextoutmnzobbqyr.qrzba.pb.hx> 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
Matt Wilson
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.
Dick Kusleika
<fz...@userot21andtakethistextoutmnzobbqyr.qrzba.pb.hx> 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
dkus...@gmail.com. Of course I would treat the data as confidential and I
understand if you aren't able to send it.
Matt Wilson
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.
Dick Kusleika
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
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
Dick Kusleika
wrote:
>On Sun, 20 Jul 2008 22:33:34 -0500, Dick Kusleika <dkus...@gmail.com>
>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
Matt Wilson
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.