Stability of Code

[ryguy7272]

I am wondering how stable this code is:
http://word.mvps.org/FAQs/InterDev/FillListBoxFromXLDAO.htm

It seems to be temperamental. Some days it works fine and some days it has
some hiccups (for lack of a better term). I seem to get the message
"Microsoft Jet database engine could not find the object 'List'. Make sure
that the object exists and that you spell the path and name correctly." When
I hit debug the line 'UserForm1.Show' is highlighted. As far as I can tell,
the 'List' and the path, and basically everything, are fine. The problem
seems to occur pretty randomly. The data just won't populate in the ListBox
sometimes; if the ListBox does display the data it loads into the template
just fine. There just seems to be some stability issues between Excel and
the ListBox. Sometimes it works and sometimes it doesn't (in the same Word
documents). Some days are better than others. Has anyone out there
experienced any similar issues? Are there any fixes?

Regards,
Ryan--

 

[Shauna Kelly]

Hi Ryan

I use this code (or, rather, code that builds on this basic idea)
extensively. I find it works reliably 100% of the time -- but only if
there are no glitches in the data.

If the are problems in the data (eg, a string where there should be a
number, or some strange characters or something like that) then the DAO
will just stop picking up any data after the problem.

To diagnose the problem, have a look at the recordset using the Locals
window. Try to identify the last piece of data that was correctly read.
The problem is almost certainly with it, or with the next piece of data.

In any case, for professional work, you would need to add appropriate
diagnostics and error checking to the sample code provided.

Hope this helps.

Shauna Kelly. Microsoft MVP.
http://www.shaunakelly.com/word

 

[ryguy7272]

When a problem occurs, the VBE opens and 'UserForm1.Show' is highlighted
yellow. The Locals Windows shows AutoNew (my sub) and under Value, the
message reads <No Variables>. It seems like it is getting stuck reading the
very first piece of data.

I'm trying to ensure data integrity on the Excel side. I'm going to try to
look into this today, if time permits. By the way Shauna, what would those
'appropriate diagnostics' be?

Regards,
Ryan---

 

[ryguy7272]

Well, I added several named ranges into my workbook and created a macro to
insert rows and copy/paste special these names into a list that I am using as
a ‘database’ to warehouse the data to feed the Word templates. I think it is
working; at least it seems to be stable now. I am still curious to know
about those ‘appropriate diagnostics’. Is there a web site out there
somewhere that talks about this?

Cordially,
Ryan---