Shell command and exe file conundrum
 

Hi all,

I'm hoping some kind soul with experience in this area can
give me an assist. I accepted the task only yesterday. I
need it to work for Tuesday. I am not at work and so can't
test my proposed solution.

Note that I can accomplish nearly all things mentioned
except for dealing with the complications of the Shell
command and the exe file. I give more information than
would appear necessary to assist with completely different
approaches.

BACKGROUND:
We have a test devise that has a data logger feature. The
test devise is used on a seasonal basis; i.e. will go
unused for up to nearly a year. When it is used it is by
someone with little to no experience or they have
forgotten. It is often used during night shift when there
is no one available to assist. Results of the tests must
be available asap during the shift. Important decisions
are based on them.

CURRENT OPERATION:
File XYZ.exe interfaces with the test device. When all the
right buttons are pushed it extracts the data and passes
it to text file ABC.txt.

GOAL:
1) From within Excel, execute XYZ.exe.
2) Import the data from ABC.txt and populate a specific
range of cells. This range will vary depending on user
selections.
3) Crunch the numbers and produce a graph and a report.
4) Do the above in only one step.

PROBLEM:
I am using a macro that uses the Shell command to execute
file XYZ.exe. When I do this the exe file runs independent
of the macro. It cannot (apparently) be controlled nor can
its status be queried. Consequentially, the macro gets
ahead of the exe file. The macro therefore populates the
cells using old data before ABC.txt can be updated. I
don't want to go to a two step solution because I need
maximum simplicity for the user.

PROPOSED SOLUTION:
1) Have the macro first obtain the Last Modified info of
ABC.txt.
2) Execute file XYZ.exe using the Shell command.
3) Have the macro go into a loop which continually queries
the Last Modified info of ABC.txt. When it is different
from the original then this signifies that XYZ.exe has
completed. Therefore exit the loop.
4) Go on to extract the data from ABC.txt and populate the
cells.

PROPOSED CODE SNIPPET:
I'm not at work and so can't test the following code:

DefDir = ThisWorkbook.Path & "\"
s = CStr(FileDateTime(DefDir & "ABC.txt"))
t = s
retval = Shell(DefDir & "XYZ.exe", 1)
Do Until t < s
t = CStr(FileDateTime(DefDir & "ABC.txt"))
Loop
Open DefDir & "ABC.txt" For Input As #1
Do While Not EOF(1)
Line Input #1, x
'Code that copies x to worksheet cells
Loop
Close

Much appreciative of any insights. Different approaches
welcome.

Greg

Shell command and exe file conundrum
 

http://support.microsoft.com/?id=214248
XL2000: How to Force Macro Code to Wait for Outside Procedure

http://support.microsoft.com/?id=129796
HOWTO: 32-Bit App Can Determine When a Shelled Process Ends

http://support.microsoft.com/?id=147392
XL: How to Force Macro Code to Wait for Outside Procedure

--
Regards,
Tom Ogilvy


"Greg Wilson" wrote in message
...
Hi all,

I'm hoping some kind soul with experience in this area can
give me an assist. I accepted the task only yesterday. I
need it to work for Tuesday. I am not at work and so can't
test my proposed solution.

Note that I can accomplish nearly all things mentioned
except for dealing with the complications of the Shell
command and the exe file. I give more information than
would appear necessary to assist with completely different
approaches.

BACKGROUND:
We have a test devise that has a data logger feature. The
test devise is used on a seasonal basis; i.e. will go
unused for up to nearly a year. When it is used it is by
someone with little to no experience or they have
forgotten. It is often used during night shift when there
is no one available to assist. Results of the tests must
be available asap during the shift. Important decisions
are based on them.

CURRENT OPERATION:
File XYZ.exe interfaces with the test device. When all the
right buttons are pushed it extracts the data and passes
it to text file ABC.txt.

GOAL:
1) From within Excel, execute XYZ.exe.
2) Import the data from ABC.txt and populate a specific
range of cells. This range will vary depending on user
selections.
3) Crunch the numbers and produce a graph and a report.
4) Do the above in only one step.

PROBLEM:
I am using a macro that uses the Shell command to execute
file XYZ.exe. When I do this the exe file runs independent
of the macro. It cannot (apparently) be controlled nor can
its status be queried. Consequentially, the macro gets
ahead of the exe file. The macro therefore populates the
cells using old data before ABC.txt can be updated. I
don't want to go to a two step solution because I need
maximum simplicity for the user.

PROPOSED SOLUTION:
1) Have the macro first obtain the Last Modified info of
ABC.txt.
2) Execute file XYZ.exe using the Shell command.
3) Have the macro go into a loop which continually queries
the Last Modified info of ABC.txt. When it is different
from the original then this signifies that XYZ.exe has
completed. Therefore exit the loop.
4) Go on to extract the data from ABC.txt and populate the
cells.

PROPOSED CODE SNIPPET:
I'm not at work and so can't test the following code:

DefDir = ThisWorkbook.Path & "\"
s = CStr(FileDateTime(DefDir & "ABC.txt"))
t = s
retval = Shell(DefDir & "XYZ.exe", 1)
Do Until t < s
t = CStr(FileDateTime(DefDir & "ABC.txt"))
Loop
Open DefDir & "ABC.txt" For Input As #1
Do While Not EOF(1)
Line Input #1, x
'Code that copies x to worksheet cells
Loop
Close

Much appreciative of any insights. Different approaches
welcome.

Greg

Shell command and exe file conundrum
 

You keep us constantly in a state of amazment Tom. To be
honest, the fact that you were active this morning had a
major influence on the timing of my post.

I checked them out and the second one seems to be the
ticket although it's rather complex. It works as clean as
a whistle though. It should really impress the boss (Yeah
right. Writing Windows from scratch maybe).

The others require me to adapt the exe file to create a
flag file. I can't change the exe file - it's beyond my
skill level. Just a grunt in a construction materials
testing lab.

I'm on to implementing the second one.

Thanks millions !!!

Greg


-----Original Message-----

http://support.microsoft.com/?id=214248
XL2000: How to Force Macro Code to Wait for Outside
Procedure

http://support.microsoft.com/?id=129796
HOWTO: 32-Bit App Can Determine When a Shelled Process
Ends

http://support.microsoft.com/?id=147392
XL: How to Force Macro Code to Wait for Outside Procedure

--
Regards,
Tom Ogilvy


"Greg Wilson" wrote
in message
...
Hi all,

I'm hoping some kind soul with experience in this area
can
give me an assist. I accepted the task only yesterday. I
need it to work for Tuesday. I am not at work and so
can't
test my proposed solution.

Note that I can accomplish nearly all things mentioned
except for dealing with the complications of the Shell
command and the exe file. I give more information than
would appear necessary to assist with completely
different
approaches.

BACKGROUND:
We have a test devise that has a data logger feature.
The
test devise is used on a seasonal basis; i.e. will go
unused for up to nearly a year. When it is used it is by
someone with little to no experience or they have
forgotten. It is often used during night shift when
there
is no one available to assist. Results of the tests must
be available asap during the shift. Important decisions
are based on them.

CURRENT OPERATION:
File XYZ.exe interfaces with the test device. When all
the
right buttons are pushed it extracts the data and passes
it to text file ABC.txt.

GOAL:
1) From within Excel, execute XYZ.exe.
2) Import the data from ABC.txt and populate a specific
range of cells. This range will vary depending on user
selections.
3) Crunch the numbers and produce a graph and a report.
4) Do the above in only one step.

PROBLEM:
I am using a macro that uses the Shell command to
execute
file XYZ.exe. When I do this the exe file runs
independent
of the macro. It cannot (apparently) be controlled nor
can
its status be queried. Consequentially, the macro gets
ahead of the exe file. The macro therefore populates the
cells using old data before ABC.txt can be updated. I
don't want to go to a two step solution because I need
maximum simplicity for the user.

PROPOSED SOLUTION:
1) Have the macro first obtain the Last Modified info of
ABC.txt.
2) Execute file XYZ.exe using the Shell command.
3) Have the macro go into a loop which continually
queries
the Last Modified info of ABC.txt. When it is different
from the original then this signifies that XYZ.exe has
completed. Therefore exit the loop.
4) Go on to extract the data from ABC.txt and populate
the
cells.

PROPOSED CODE SNIPPET:
I'm not at work and so can't test the following code:

DefDir = ThisWorkbook.Path & "\"
s = CStr(FileDateTime(DefDir & "ABC.txt"))
t = s
retval = Shell(DefDir & "XYZ.exe", 1)
Do Until t < s
t = CStr(FileDateTime(DefDir & "ABC.txt"))
Loop
Open DefDir & "ABC.txt" For Input As #1
Do While Not EOF(1)
Line Input #1, x
'Code that copies x to worksheet cells
Loop
Close

Much appreciative of any insights. Different approaches
welcome.

Greg


.