I have used DigDB for over a year using Excel XP 2002 with no problems whatsoever. I recently upgraded (clean) to Office 2007 and have encountered this error. I use DigDB to parse data from county property database queries for mailing lists. I have to do quite a bit of splitting, trimming, sorting and converson. The datasets I regularly handle are >20,000 r x 50 c. The one that caused the problem is 1700 r x 20 c. Here's what happened:
After splitting all the homeowners' names into first, last for both owners (from a messy format including lots of /'s and ,'s), I merge them into a mailable format (i.e. JOE & KAREN SMITH). I then do a PROPER CASE conversion of all names fields, one column at a time. In this case, I have the 'combined' field, F1, L1, F2, L2, and D3 fileds, where F=first name, L=last, 1=owner 2, 2=owner 2, D3=other names. Before each Proper Case covnersion I performed, I would sort the field for which I was going to perform the conversion. I was using Excel 2007's sort function, making sure I highlighted ALL worksheet fields before doing so.
Everything was fine until I sorted the F2 field. It did not sort properly at all - there was a section of blanks first (I sorted A to Z), then a section of properly ordered F2 entries, then blanks, then another section of ordered entries, then blanks again. I treid sorting by D3 then L2 then F2 anf got the same result. When I tried to use the DigDB sort function, I got the pop-up error window stating "Run Time Error '6': Overflow".
I tried saving as a .txt tab-delimited file, and when I tried to re-open that, excel told me that the file format appeared to be in SYLK format but was saved as a text file, which I thought was really wierd. So I told Excel to open as a text, tab-del file, which it did and the problem was still there. Saving as a SYLK file didn't work either, nor did saving to a 2002 excel file and taking it to my other PC with office XP and savig as text, SYLK, etc, and then re-importing into 2007.
What I eventually figured out is that if I take the excel 2007 version (.xlsx) and save as an Excel 97-2003 file (.xls), close the file, and re-openit, then DigDB's sort function works just fine - the error goes away completely. If you just save the .xslx as .xls, the error is still there - you have to close it and re-open it in 'Compatibility Mode'. However, the second I convert it back over to .xlsx format, the error is present.
Strangely enough, the other, larger database, which had no issues prior to this, now has the exact same problem, as does every other .xlsx file I try the sort function on. Again, the error is not present in .xls Compatibility Mode.
So is this an Excel 2007 issue or a DibDG issue with Excel 2007?
