Run-time error '-2147221080 (800401a8)': Automation error
 

Windows XP, SP3
MS Office 2002, SP3

I have ran into this issue with one of the Excel files that I have setup VBA
code to summarize data base on the data in another file. Before I get into
that, I will first show a basic layout of the production reporting system I
have put into place.


Several client side files, which when the data transcriber enters data into
the file (In this case, the line coordinators are entering the data into the
file via userforms), the data is stored within the file and saved when the
file has been idle for at least a certain number of seconds.

One server side file, which this file's main job is to gather all of the new
data from each of the different client side files. It maintains the data
and even takes care of any backups that needs to be performed as needed, so
as to help minimize the chance of lost data. I have done this with the
manufacturing side and had no problems and I'm now working on the
fulfillment side and have come into a glitch, not so much with this server
side file, but with the file that summarizes data based on this server side
file.

Now I have created a file to summarize the data. Under the following
scenario, I get the error message as shown in the subject:

Server file gets updated and saved.
Summarized file opens the server side file (if not already open) as read
only, and then updates it (in the event that it was already open)
The very next line of code to be executed outside of going to different
objects is the following:

Set m_wshPayCodes =
l_wbkFulfillmentCodes.Names("rngPayCodes_HeaderRow ").RefersToRange.Parent

I initially attempted to put in a DoEvents command line, but that didn't
help. I also put in a Sleep command line with the declared function as a
call to the Windows API and even set it to 8000 milliseconds (8 seconds),
which I know it doesn't take the workbook no 8 seconds to open on my system,
and I still get the above error message. When the program does run
successfully, it's completed within 2 seconds. The server side workbook
file take no more than 4 seconds at most to open.


Now if I click on "End" of the debugger dialog box, and then click on the
command button again, it works fine as there was no new updated data added
to that file within that very short time period. Even when I have set the
Sleep command to just 20 milliseconds and then click on End, then click on
the command button again, it still works fine that second time around.


The only other thing I have seen out there with this error message is at the
site of:

http://www.mrexcel.com/forum/showthread.php?t=373704

The only commonality that I see between these 2 cases is the range name bit.

As for the question that was asked by another user to the first person to
run into this error message, is this a workbook or worksheet level range
name? As for my case, I know it's a workbook level as I have set it up as a
workbook level on purpose to fit into the programming rules that I have been
following along with allowing different worksheets to see those ranges as
corporate caused a lot of work for me when they had me change something and
the thing about it, they have no idea how much work they caused me on the
account of the formula setup. After that, I was like the only way to get
around that issue was to use range names, but very quickly, I learned one
can't exceed 32768 or so defined names without possibly running into
stability issues and absolutely can't exceed and save the workbook with more
than 65536 defined names without causing the workbook to go into repair mode
when it's opened up again getting rid of all defined names, formats, charts,
and whatever else as it will only keep the data and formulas. Yeah, yeah,
yeah, the specification help file states the number of defined names is only
limited to what is allowed within RAM, but that just simply isn't true.
With these kinds of issues, that's the number one reason why I have taken so
much of my calculations from fromulas to VBA. I mean it's gotten to the
point that I can't even use formulas all that much anymore, and of course to
take all of those calculations to VBA, that meant I have to learn the last
major component of VBA which is Object Oriented Programming including
learning how to not only use the events, but also how to set them up, both
at the individual level and at the collection level, so as to allow the
programs to be event driven with having the code fully modulated.


One may ask why don't I go to a database environment. I would if I had that
realistic choice, but I don't as I don't have that kind of authority at
work.

The only database application I could even remotely use to set this stuff up
would be Access, but given it's connection issues with a 24 hour job shop
and multiple users work environment, it loses it's connection at some point
of time when the backups on the server where the BE (Back End) Access file
is saved at is taking place. Given the restrictions one must know which
mode the data is in prior to feeding the data into Access, I ended up having
to use unbound forms to get around that issue. With regards to this
connection issue, I attempted to address it, but to no reasonable
resolution, I was forced to no longer to use Access, thus Excel is my only
application to use as Excel isn't so sensitive to the connection issue like
Access appears to be.

I like to know if anyone else have ran into this automation error issue and
more importantly, how to resolve this issue. The workbook that I have put
workbook level range names in is well under 50 defined names.

--
Thanks,

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000

Run-time error '-2147221080 (800401a8)': Automation error
 

As it turns out, I ended up trapping the error, setup the main procedure to
be ran again just a second later via Application.OnTime, and end the
procedure right then and there. The procedure then runs again just that 1
second later and it works fine. Doesn't really resolve the original issue,
but it at least hides the error from the user.

--
Thanks,

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000
"Ronald R. Dodge, Jr." wrote in message
...
Windows XP, SP3
MS Office 2002, SP3

I have ran into this issue with one of the Excel files that I have setup
VBA code to summarize data base on the data in another file. Before I get
into that, I will first show a basic layout of the production reporting
system I have put into place.


Several client side files, which when the data transcriber enters data
into the file (In this case, the line coordinators are entering the data
into the file via userforms), the data is stored within the file and saved
when the file has been idle for at least a certain number of seconds.

One server side file, which this file's main job is to gather all of the
new data from each of the different client side files. It maintains the
data and even takes care of any backups that needs to be performed as
needed, so as to help minimize the chance of lost data. I have done this
with the manufacturing side and had no problems and I'm now working on the
fulfillment side and have come into a glitch, not so much with this server
side file, but with the file that summarizes data based on this server
side file.

Now I have created a file to summarize the data. Under the following
scenario, I get the error message as shown in the subject:

Server file gets updated and saved.
Summarized file opens the server side file (if not already open) as read
only, and then updates it (in the event that it was already open)
The very next line of code to be executed outside of going to different
objects is the following:

Set m_wshPayCodes =
l_wbkFulfillmentCodes.Names("rngPayCodes_HeaderRow ").RefersToRange.Parent

I initially attempted to put in a DoEvents command line, but that didn't
help. I also put in a Sleep command line with the declared function as a
call to the Windows API and even set it to 8000 milliseconds (8 seconds),
which I know it doesn't take the workbook no 8 seconds to open on my
system, and I still get the above error message. When the program does
run successfully, it's completed within 2 seconds. The server side
workbook file take no more than 4 seconds at most to open.


Now if I click on "End" of the debugger dialog box, and then click on the
command button again, it works fine as there was no new updated data added
to that file within that very short time period. Even when I have set the
Sleep command to just 20 milliseconds and then click on End, then click on
the command button again, it still works fine that second time around.


The only other thing I have seen out there with this error message is at
the site of:

http://www.mrexcel.com/forum/showthread.php?t=373704

The only commonality that I see between these 2 cases is the range name
bit.

As for the question that was asked by another user to the first person to
run into this error message, is this a workbook or worksheet level range
name? As for my case, I know it's a workbook level as I have set it up as
a workbook level on purpose to fit into the programming rules that I have
been following along with allowing different worksheets to see those
ranges as corporate caused a lot of work for me when they had me change
something and the thing about it, they have no idea how much work they
caused me on the account of the formula setup. After that, I was like the
only way to get around that issue was to use range names, but very
quickly, I learned one can't exceed 32768 or so defined names without
possibly running into stability issues and absolutely can't exceed and
save the workbook with more than 65536 defined names without causing the
workbook to go into repair mode when it's opened up again getting rid of
all defined names, formats, charts, and whatever else as it will only keep
the data and formulas. Yeah, yeah, yeah, the specification help file
states the number of defined names is only limited to what is allowed
within RAM, but that just simply isn't true. With these kinds of issues,
that's the number one reason why I have taken so much of my calculations
from fromulas to VBA. I mean it's gotten to the point that I can't even
use formulas all that much anymore, and of course to take all of those
calculations to VBA, that meant I have to learn the last major component
of VBA which is Object Oriented Programming including learning how to not
only use the events, but also how to set them up, both at the individual
level and at the collection level, so as to allow the programs to be event
driven with having the code fully modulated.


One may ask why don't I go to a database environment. I would if I had
that realistic choice, but I don't as I don't have that kind of authority
at work.

The only database application I could even remotely use to set this stuff
up would be Access, but given it's connection issues with a 24 hour job
shop and multiple users work environment, it loses it's connection at some
point of time when the backups on the server where the BE (Back End)
Access file is saved at is taking place. Given the restrictions one must
know which mode the data is in prior to feeding the data into Access, I
ended up having to use unbound forms to get around that issue. With
regards to this connection issue, I attempted to address it, but to no
reasonable resolution, I was forced to no longer to use Access, thus Excel
is my only application to use as Excel isn't so sensitive to the
connection issue like Access appears to be.

I like to know if anyone else have ran into this automation error issue
and more importantly, how to resolve this issue. The workbook that I have
put workbook level range names in is well under 50 defined names.

--
Thanks,

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000