R
Ronald R. Dodge, Jr.
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
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