Accessing file after program run from Shell completes

  • Hello. I am running the following script in a excel:

    The idea is to run an external program, which produces an output file which excel then opens and reads. I was having a problem that excel was trying to access the file while it was still being closed by the shell'd program, hence the delay loop (as per: By waiting until teh file could be copied, the program is waiting until it is written and closed. The problem is that it still does not work. I get a "Permission Denied" error when I try to open the file. Any suggestions?

  • Re: shell problem with excel macro

    May be it takes time to...
    1) open
    2) read the content
    3) output the file
    4) close the files

    Try using Timer function after your shell command to pause your code for some time and then continue..

  • Re: shell problem with excel macro

    yes, I think you are right that it needs time. The problem is 1) the program takes a long time to open and close, so it's hard to predict how much time it needs 2) the amount of time it will need varies depending on several things and 3) I call this routine several times within the program, so delaying more than the minimum will really slow things down. I would like the program to run as fast as possible without unnecessary or unreasonable delays. That is why I was doing it this way.

  • Re: Accessing file after program run from Shell completes

    I think the issue may be that you are not completely adhering to the solution provided by MS. Their intention is that the Shell'd program creates a flag file when it has completed and then the VBA tests for the existance of that flag file.

    You are testing to see if the file created by your Shell'd program can be copied (available for output) that does not guarantee that the file is available to open for input.

    While not a very elegant solution (these things often are not), you can try:

    Shell ("mprog.exe outfile.txt")
    On Error Resume Next
        Open "outfile.txt" For Input As #1
        If Err.Number = 0 Then Exit Do
    Loop Until False
    On Error GoTo 0
  • Re: Accessing file after program run from Shell completes

    Hi Rob-
    That was a good idea, although, for some reason, I was still getting hung in the Do loop. In teh meantime, I found some code that does work, though:

    Sub LoopUntilFileIsAvailable()
    Dim f As Integer
    f = FreeFile
    On Error Resume Next
    Open "c:\test\test.txt" For Input Lock Read As f
    Loop Until FreeFile <> f
    On Error GoTo 0
    Close f
    End Sub

    This, with some minor mods seems to do the trick. Thank you very much for the help!

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!