G
Greg Wilson
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
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