The error code 80020009 is returned as the result of an addressing exception, which occurs under various circumstances that USUALLY (not always) show up when something isn't correctly registered. Usually, that something is a .DLL or .OCX file.
This implies that the problem is therefore in your References list, which is accessible from a code-editing screen via Tools >> References. Since you are doing something that is activating MS Word, verify that MS Word has been checked in your list of references. To be sure that it is linked, update the reference list as suggested, then try to compile your code again.
Before giving up, verify that in your references list you have no references for which there is an X (i.e. reference is selected) but for which the text next to the reference says "Missing Reference."
One of the other posts told you to make varg1 a string. The code you posted in response didn't do that.
Code:
Dim strFileName, varg1, strDirectoryPath As String
If this is the code you are using now, varg1 is still a Variant. In the above code line, only strDirectoryPath is declared as a string. To declare each variable in that list as a string, you must have an AS String behind EACH variable name. Further, if varg1 is still a variant, it might actually be relevant to the problem.
I found one other reference for that particular error code, claiming an ODBC problem with an SQL backend link. My original interpretation (mis-registered .DLL or .OCX) might have been applicable in the ODBC reference, so it isn't a unique thing, but ... are you using SQL via an ODBC connection?
I am also not fully convinced that this wouldn't be logged SOMEWHERE, because when you see an 8002xxxx error, that is usually a system-level error. It should be in a windows log, windows security log, or application log. FYI the presence of that 8 in the first digit and the ABSENCE of a 1-bit in 5th digit (= the high-order digit of the 16-bit word that is the second word of the split error code) tell me this error should have gone through a full-blown error trap process within Windows.
The code 0x80020009 is actually telling you (in this order) "you have a failure of a non-severe, Microsoft-detected class (the 8 as a full hex digit) in facility 2 of specific type 9." Look up the Wikipedia article on the data structure known as the HRESULT to see how error codes are encoded.
Facility 2 is the COM dispatch facility (in other words, a call through a COM interface, and since you are activating Word, that makes a lot of sense). Error 9 is the code for INVALID_BLOCK (an error in the address of a control block, which I take to be the data structure being passed for the call.) If you change varg1 from Variant, which is what it is because of the way you declared it, to string, it might work better. It might not, either, but we have to remember that Variant is a data type for Access. It might NOT be a valid data type for Word interfaces.
- I tried the correct declaration of the variables as string - didn't work:
Dim strFileName as string
Dim strDirectoryPath as string
Dim varg1 as string
- There are no missing references. That's checked.
- However the problem could be caused by using MySQL ODBC -> But why didn't the error message show when JHB executed the word macro? Probably because he doesn't have the ODBC driver (MySQL ODBC 5.1 Driver) installed?
As I said, it would be POSSIBLE (but not at all guaranteed) that this could have been due to an error with Word touching an ODBC SQL driver; for instance if the Word document was building something using a query input via the MailMerge option, then Word would be activating SQL but the error would be passed to the thing activating Word, and in this case it isn't the GUI.
The error, when deciphered, says something is wrong with the argument or data block for a COM interface, and both Word and SQL/ODBC qualify for that situation. The last thing that comes to mind is whether there is a Run As in effect for either Word or Access but not both. After all, you did say that there was a difference between automated and manual execution. If a selective Run As is in effect, I would suggest a permissions issue on the path you are providing. But if that's not the case, then it has to be something else and at this point I'm not sure where to go.
Well, my files ran without any error on JHB's machine, so I would really see the ODBC driver as cause for this error as this is probably the decisive difference between our PCs.
As a workaround I now create a temporary txt file that I read into the Word file. Of course the direct transfer of parameters would be nicer and likely cause less errors, but I have to be pragmatic here as the ODBC component is vital to my file.
Yeah, I've had to run workarounds more than once myself. I understand the need to move on. Sorry we couldn't track this one down farther, but inconsistent behavior for the testers is kind of a big road-block in debugging.
Thank you for your input!! Regards, Frank
