I have a series of macros that updates information from Excel to our main DB
program. Main 2 reasons why the operators are not using the interface that
has been provided for us to update directly into our main DB program, it's
not user friendly cause of the various things that the operators has to do
including either have to calculate how much time they spent at the machine
center for each step or put in both start and ending times in text format,
and it's too easy for them to fudge the numbers for those that does know how
to use the system.

I created a program that acts as the main interface for the users and they
only see the main screen and then a pop up wondow for a few other minor
things from the main screen. Granted, the numbers can be fudged to some
degree, but not near as easily with my interface as it can be with the DB's
interface. Given the sharing issues of Excel, I had to create a separate
file for each machine center, which then I created a main file that pulls
the information from each individual machine center file, then updates our
main DB system via the interface. This program that I created was only
meant as a temporary solution as I am working on a more permanent type
solution. At the time that I created the program, I only had 3 weeks to do
it in and have it tested.

Issues that I had to address for updating our main DB system:

Can only use the SendKey method to update the system (I hate to do it this
way, but don't have a choice)
Had to slow down the system between each field entry, update, and screen
change to allow the DB's interface to catch up (see code below).

newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 1
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime
SendKeys "+{F10}", True

This worked fine in Excel 97, but we have recently upgraded to Excel XP
(2002) and it is now getting stuck on the Application.Wait waitTime line. I
had gotten this code directly from Excel VBA's help file. I have done all
of the updates and patches to Office XP as of 8/25/2003.

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

This gives two examples of doing it with the windows API. The first is
simple - uses sleep. The second is more complex. The article explains why
you might prefer the second, but if screen updating isn't an issue in that
second, the first seems workable to me.

http://support.microsoft.com/?id=231298
HOWTO: Use SetWaitableTimer With Visual Basic


A post by Chip Pearson:
http://groups.google.com/groups?thre...GP09.ph x.gbl

I couldn't find anything that said there is a problem with Wait in xl2002,
but I could have missed it.

Regards,
Tom Ogilvy


"Ronald Dodge" wrote in message
...
I have a series of macros that updates information from Excel to our main
DB
program. Main 2 reasons why the operators are not using the interface
that
has been provided for us to update directly into our main DB program, it's
not user friendly cause of the various things that the operators has to do
including either have to calculate how much time they spent at the machine
center for each step or put in both start and ending times in text format,
and it's too easy for them to fudge the numbers for those that does know
how
to use the system.

I created a program that acts as the main interface for the users and they
only see the main screen and then a pop up wondow for a few other minor
things from the main screen. Granted, the numbers can be fudged to some
degree, but not near as easily with my interface as it can be with the
DB's
interface. Given the sharing issues of Excel, I had to create a separate
file for each machine center, which then I created a main file that pulls
the information from each individual machine center file, then updates our
main DB system via the interface. This program that I created was only
meant as a temporary solution as I am working on a more permanent type
solution. At the time that I created the program, I only had 3 weeks to
do
it in and have it tested.

Issues that I had to address for updating our main DB system:

Can only use the SendKey method to update the system (I hate to do it this
way, but don't have a choice)
Had to slow down the system between each field entry, update, and screen
change to allow the DB's interface to catch up (see code below).

newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 1
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime
SendKeys "+{F10}", True

This worked fine in Excel 97, but we have recently upgraded to Excel XP
(2002) and it is now getting stuck on the Application.Wait waitTime line.
I
had gotten this code directly from Excel VBA's help file. I have done all
of the updates and patches to Office XP as of 8/25/2003.

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

Okay, thanks for the time and help. I thought I would bring this to the
group's attention in case anyone else may have ran into this issue given the
wait code is in the help file. I have already used the Sleep Windows API
for speeding up the process of updating the fields as I have each field
entry spaced apart by 200 milliseconds. However, since I have ran into this
issue with the Wait function, I have already started to convert the rest of
my waits to the Sleep API.

Given the different issues that I have ran into (some of which were expected
from the get go), I have been working on creating a more long term solution,
though it's in Access, which is better then Excel for what I'm doing, but at
the same time, it's still not what I would think of being a suitable program
due to the various arbutary (size and number of users) and other limitations
of it and I have had to create my own method of creating events and
properties within the confines of Access workings, so as I not only can meet
the needs of users that tends to use keyboards, but also of users that tends
to use the mouse while being able to provide all of the needed validations
at the needed times. For me to meet these requirements, I have not been
able to use bound forms cause of how the events works in Access. For the
most part, I am using 2 different levels of field data validation (General
level for meeting format requirements as I don't really care for how Access
handles the errors, of which this level catches general errors as the user
inputs, and the specific level which validates the data in the field as the
user goes to the next object.) then the form level for any other validation
that may be needed at that time. You may think to use the BeforeUpdate
event for field specific data validation checks, but that would only work if
you wanted the validation check take place under 100% of the circumstances
including when backing out of the screen or resetting the form. Cause of
this issue, it can be bypassed via keyboard events, if activated by a
keyboard action, but if done by clicking on a command button or some other
mouse action, this creates a problem cause the events for the object that
has the focus, *ALL* of it's related events are ran through before the
"Enter" event is even ran on the object that the mouse action intended to
activate. Cause of this, I have created my own version of VB6's Validate
Event and CausesValidation Property within Access VBA.

--
Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000
"Tom Ogilvy" wrote in message
...
This gives two examples of doing it with the windows API. The first is
simple - uses sleep. The second is more complex. The article explains
why
you might prefer the second, but if screen updating isn't an issue in that
second, the first seems workable to me.

http://support.microsoft.com/?id=231298
HOWTO: Use SetWaitableTimer With Visual Basic


A post by Chip Pearson:

http://groups.google.com/groups?thre...GP09.ph x.gbl

I couldn't find anything that said there is a problem with Wait in xl2002,
but I could have missed it.

Regards,
Tom Ogilvy


"Ronald Dodge" wrote in message
...
I have a series of macros that updates information from Excel to our
main
DB
program. Main 2 reasons why the operators are not using the interface
that
has been provided for us to update directly into our main DB program,
it's
not user friendly cause of the various things that the operators has to
do
including either have to calculate how much time they spent at the
machine
center for each step or put in both start and ending times in text
format,
and it's too easy for them to fudge the numbers for those that does know
how
to use the system.

I created a program that acts as the main interface for the users and
they
only see the main screen and then a pop up wondow for a few other minor
things from the main screen. Granted, the numbers can be fudged to some
degree, but not near as easily with my interface as it can be with the
DB's
interface. Given the sharing issues of Excel, I had to create a
separate
file for each machine center, which then I created a main file that
pulls
the information from each individual machine center file, then updates
our
main DB system via the interface. This program that I created was only
meant as a temporary solution as I am working on a more permanent type
solution. At the time that I created the program, I only had 3 weeks to
do
it in and have it tested.

Issues that I had to address for updating our main DB system:

Can only use the SendKey method to update the system (I hate to do it
this
way, but don't have a choice)
Had to slow down the system between each field entry, update, and screen
change to allow the DB's interface to catch up (see code below).

newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 1
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime
SendKeys "+{F10}", True

This worked fine in Excel 97, but we have recently upgraded to Excel XP
(2002) and it is now getting stuck on the Application.Wait waitTime
line.
I
had gotten this code directly from Excel VBA's help file. I have done
all
of the updates and patches to Office XP as of 8/25/2003.

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