VBA table sort by date fails intermittently

[julian.ladbury]

I have a product whose entire reason for existence is to sort
chronological tables. It was developed and tested using Word 2003, and
also tested on 2000 and 2002. It works fine.
Imagine my dismay when demonstrating it to possible customers yesterday
only to find that the sort feature did nothing!

I saved a copy of the offending file and brought it back to my testbed.
There was no problem sorting it, using both 2003 and 2000. The strange
thing is, the failure at the demo was under 2000 SR3 - the exact same
level of my 2000 testbed. Has anyone any idea what other Word or
Windows modules / release levels could be relevant to any further
investigation?

Looking through this forum, I feel my symptoms are the same as those in
thread
http://groups.google.com/group/micr...0648a4e12?q=sort+date&rnum=4#0c8c8800648a4e12
Sadly this thread was never resolved.

All ideas gratefully appreciated.

 

[Greg]

Julian,

This it just a stab, but are you certain that there is something there
to sort? See:
http://gregmaxey.mvps.org/Replace_With_Paragraph.htm

 

[julian.ladbury]

Greg,
Thanks for the response, but yes there is something to sort. The
identical file works fine on my two testbeds, but failed on one
particular machine.

 

[Cindy M -WordMVP-]

OK, I just skimmed the thread you mention. The questions asked there are similar
to what I would ask. Since you don't provide any of that information, I will ask
them again, in a slightly different form:

1. With which language is the table content formatted (Tools/Language/Set
language)

2. What are the date format settings in the Windows Control Panel, both on your
test machine and the machine you were using for the presentation?

3. Do all the machines have the same version of Windows installed?

4. Is the sort being performed manually, or using VBA code?

My inclination is that there may have been some problem with the Windows settings
on the presentation machine.

I have a product whose entire reason for existence is to sort
chronological tables. It was developed and tested using Word 2003, and
also tested on 2000 and 2002. It works fine.
Imagine my dismay when demonstrating it to possible customers yesterday
only to find that the sort feature did nothing!

I saved a copy of the offending file and brought it back to my testbed.
There was no problem sorting it, using both 2003 and 2000. The strange
thing is, the failure at the demo was under 2000 SR3 - the exact same
level of my 2000 testbed. Has anyone any idea what other Word or
Windows modules / release levels could be relevant to any further
investigation?

Looking through this forum, I feel my symptoms are the same as those in
thread
http://groups.google.com/group/microsoft.public.word.tables/browse_thread/thread/
f789b0d65fb5bbb7/0c8c8800648a4e12?q=sort+date&rnum=4#0c8c8800648a4e12
Sadly this thread was never resolved.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 8 2004)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or reply
in the newsgroup and not by e-mail

 

[julian.ladbury]

Cindy,
Thanks for your interest. Taking your questions in order:
1. On my Word 2003 testbed, with the ofending file open,
Tools/Language/Set Language shows English(UK) highlighted and 'Detect
Language Automatically' is checked
2. Control Panel / Regional Options is set to English (United Kingdom)
on my testbed. I do not know what the demo machine settings were and am
unlikely to be able to find out. But if I stumble over the problem
again, I will be sure to check this.
3. Again, I don't know what Windows was running at the demo. My testbed
is XP SP2 with all the latest Office Update maintenance.
4. The sort is done in VBA.
Because of my lack of knowledge about the demo machine (I could hardly
ask the assembled throng to watch me diagnose an obscure problem in the
product I was selling!) I realise I am unlikely to get to the bottom of
this right away: but your pointers are useful. Thanks! Any other ideas
you or others might have will be gratefully received.

 

[Tony Jollans]

A couple of things spring to my mind, both of which seem unlikely ...

1. Are you sure the Sort statement in VBA was actually executed?
2. Is there any chance the dates could have been in order according to the
date format on the customer machine?

Only slightly more likely - how have you coded your sort? Might a subset of
cells have been selected at the time and the "Sort Column Only" option be
switched on?

 

[julian.ladbury]

Tony,
1. The sort is executed by pressing a custom toolbar control. The
problem could be that for some reason the connection between this
button and the sort routine was broken in some way. However, I feel it
is more likely that the sort itself did not execute properly, for the
following reason.

When developing the routine, there was a circumstance in which the sort
failed under Word 2003 (but not 2000). Microsoft determined the cause
to be a failure of VBA to initialise the sort parameters properly (the
circumvention was to explicitly set the LanguageId to what it already
was:
Set curTable = ThisDocument.Tables(1)
Call curTable.Sort(excludeHeader:=True,
LanguageID:=curTable.Range.LanguageID, _
FieldNumber:=1, SortFieldType:=wdSortFieldDate,
sortOrder:=wdSortOrderAscending)
I suspect something similar may be going on here.

2. No, the dates would be out of order in any language.

The sort is coded much the same as the fragment above. No cells were
selected. I had certainly not selected 'Sort Column Only' - your post
is the first time I have come across it! Can it be a 'sticky' setting
across Word sessions, and thus possibly hung over from someone else?
(This is a rhetorical question - I will investigate it myself, I'm not
asking you to!)

 

[Tony Jollans]

My vote would go for the toolbar control not firing the code. Difficult to
say more without knowing exactly what you have set up.

AFAIK, the sort coluimn only option is not saved acros sessions but is saved
within a session. Off the top of my head I'm not sure how the option set in
the UI might affect a VBA execution but don't think it will have any effect
on explicitly sorting the table.