Clarify Excel error messages, VBA references
From James in CT:
I unzipped using windows, copied the file to another folder and when I
opened it I got:
Microsoft visual basic CVS-EEP data entry database
With a dialog box: compile error-can't find project or library.
There are a bunch of screens behind it.
One screen that comes up is error handler code that I can't close
I can't get out without shutting down my computer
I do have excel ( Office professional XP) and ACCESS 2002
Could it be that windows file extractor is less reliable than winzip?
I could try winzip.
#1 Updated by Michael Lee over 12 years ago
I think this has to do with the Excel library that is referenced in the Visual Basic Project. There are other ways to add a reference, which I should check into. Also, I should install only Excel and Access 2002 on a computer and check to see if I can run the entry tool like that. Currently, I've only tried it with these combinations:
Access 2002 / Excel 2003
Access 2003 / Excel 2003
Not sure if we want to try the Access 2000 / Excel 2000 stuff or not, probably IS worth it, if I can find the CDs for those programs. With a new office coming out soon, we are likely to need to continue to monitor this, or at least find a better way of finding the right excel library to use, or skip that functionality if it isn't available. Debugging error is not what we want to see!
#2 Updated by Michael Lee over 12 years ago
Access 2003 / no Excel at all yields error:
Your CVS-EEP Data Entry database or project contains a missing or broken reference to the file 'EXCEL.EXE' version 1.5.
*To ensure that your database or project works properly, you must fix this reference.
The application you are attempting to use has a reference it cannot resolve to an object, type library, DLL, or external database. Either the object, type library, DLL, or database was deleted or its name has changed. Examine the Available references list in the References dialog box in the Visual Basic Editor (Tools menu) to determine if any action is required. If you did not create this application, contact the programmer or administrator of the system.
If the reference listed in the Available references list is preceded with "MISSING:" clear the check box to remove the reference if it is no longer required. If you still need to use the reference, clear the check box entry for "MISSING: <referencename>" in the Available references list, and then create a new reference to the file using the Browse... button. If this is an installed database application, you may need to reinstall or repair the application.
More information about this error message online.
However, you cannot access the references from Visual Basic and it constantly throws errors for functions, even chr(13), so you have to kill the app manually (bad)!
#4 Updated by Michael Lee over 12 years ago
once a reference is broken, it seems unable to be fixed:
how unhappy. The only solution, it seems to me, is to distribute the database without the reference to Excel and add that in once it becomes needed. If it can't be added, then tell the user to install excel. First, it should try to add Excel 2003, then Excel 2002, then Excel 2000, then it should move up from Excel 2003 to try to get whatever new fangled version that there is out there. If it still can't get anything at 100 or so, it could ask the user for help. Not sure how much help the user will be.
#7 Updated by Michael Lee over 12 years ago
Need to be careful how stuff is linked to excel functions, as even a reference to a function that needs excel needs to compile and can't, so it's better to load excel earlier than later, but if it's not loaded, functions could still be affected. Best to use "eval()" to link to excel, as this doesn't affect compilation errors.
#8 Updated by Michael Lee over 12 years ago
OK, better yet, don't reference excel explicitly like Excel.Something, instead just use "As Object"
This is slower, but better than causing compiling errors. Plus, this functionality is not used that often, and takes a bit of time to complete. I don't think we would notice the lesser speed.
Also now have a function that registers Excel automatically. Unfortunately, that has to complete before anything else can run. Perhaps I can try to run using eval("goRegisterExcel") but I don't think so.
#9 Updated by Michael Lee over 12 years ago
updated code to not reference Excel.Objects and to call eval("function()") when something later down the line requires excel. I need to test that these functions barf correctly (ie with a message) instead of throwing compile errors when excel isn't available.
I have also added a feature that turns off the excel library referencing if you want to (ie to move to new machine) and will do so before distributing the db.
Need to check to see how my Access libraries move if they are in a weird place (ie install access to C:\Program Files\WeirdPlaceForAccess\" and see how it does.
#12 Updated by Michael Lee over 12 years ago
I have now tested the setup with a myriad of possibilities, and everything seems to work ok. I will dereference Excel before distributing the app, and it will find Excel on its own, if it's on the computer. If it isn't on the computer, it gives sensible error messages.
The only problem is when it gets opened on one computer, then moved to another with a different version of excel. There is a manual fix that I could suggest, as well as you could go back to the first computer, dereference excel, and then move it along. I don't think that will be a big problem there. In any case, it dies modestly gracefully, telling you it needs to quit, ok, error, ok. it's gone. No killing apps or restarting computers needed!
TEST: DONE broken reference (with Excel)
TEST: DONE no excel
TEST: DONE excel somewhere weird - finds it even on a different drive.
TEST: DONE: access somewhere weird - finds everything ok.
Access 2003 x Excel 2003 : done LITU
Access 2002 x Excel 2003 ; done CHEY
Access 2002 x Excel 2002 ; done LITU
Access 2003 x Excel 2002 : DONE LITU
To do: make sure error messages are clear.