1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

VBA file handling syntax hangup

Discussion in 'Business Applications' started by Riverglen, Oct 28, 2009.

Thread Status:
Not open for further replies.
Advertisement
  1. Riverglen

    Riverglen Thread Starter

    Joined:
    Aug 28, 2006
    Messages:
    478
    First Name:
    Larry
    Below is the code for an Excel macro that passes a file name to an external program that processes the file. For reasons that are irrelevant to my question, I create a temporary
    file to pass to the external program, and then delete the tempfile. Everything works fine except that I can't figure out the correct syntax for deleting the tempfile at the end of the procedure. Can somebody point out the obvious to me?

    Code follows:

    Sub RunReplayQk()
    '
    ' Pass a selected event file to ReplayQk for execution. The
    ' event file is selected by selecting the event file name in
    ' the spreadsheet.
    '
    Dim ProcID
    Dim ExecutablePath As String
    Dim CommandLine As String
    Dim TempFile As String
    Dim TempFilePath As String
    Dim OriginalFile As String
    Dim OriginalFilePath As String
    Dim fs As Object

    ExecutablePath = Environ("SEISMOPATH") & Range("Notes!D24").Value
    ' Data file path is specified in the RepqlyQk.ini file, down to but not including the specific year
    ' subdirectory, which is built by examining the first two digits of the file name argument.

    OriginalFilePath = Environ("WQPATH") & Range("Notes!D20").Value & Left(ActiveCell.Value, 2) & "\"
    TempFilePath = Environ("WQPATH") & Range("Notes!D20").Value & Left(ActiveCell.Value, 2) & "\"

    OriginalFile = ActiveCell.Value
    TempFile = Left(ActiveCell.Value, 8) & ".RPQ"

    ' Create a coy of the selected data file as TempFile.RPQ
    Set fs = CreateObject("Scripting.FileSystemObject")
    fs.CopyFile (OriginalFilePath & OriginalFile), (TempFilePath & TempFile)

    ' Create and execue the command for executing ReplayQk
    CommandLine = ExecutablePath & " " & TempFile
    ProcID = Shell(PathName:=CommandLine, WindowStyle:=vbMaximizedFocus)

    ' Delete the temporary file
    fs.DeleteFile (TempFilePath & TempFile)

    End Sub
     
  2. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    Try

    Kill(TempFilePath & TempFile)

    Just make sure your variable TempFilePath has a slash mark appended to the end.

    Regards,
    Rollin
     
  3. Riverglen

    Riverglen Thread Starter

    Joined:
    Aug 28, 2006
    Messages:
    478
    First Name:
    Larry
    Rollin,

    Thanks for the suggestion. Didn't work though. The program that should have processed the file reported an error that it couldn't open the file. Strange since that program reports the filespec that it was passed and it was fine. If I then just comment out the Kill statement, everything works, except that the tempfile doesn't get deleted. If my code works to CREATE the stupid tempfile, I don't understand why it should be a problem to DELETE it?
     
  4. Riverglen

    Riverglen Thread Starter

    Joined:
    Aug 28, 2006
    Messages:
    478
    First Name:
    Larry
    I realized I probably asked the wrong question. I think what is happening is that the VBA code continues to execute after launching the external program. It immediately goes on to delete the file that it passed to the external program before that program has a chance to process it.

    So the question should be, how do I suspend execution of the VBA macro until the external program it launched finishes? Only thing I can come up with is to add a message box that asks for permission to delete the file and waits for a user response. Haven't tried this and don't much like having to do it that way. Is there a better answer?
     
  5. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,840
    You could try setting a timer, although you would need to know how long the the program takes to execute.
    I am sure there must be some VBA that tells Excel when the program finishes, perhaps Rollin, AJ or Zack knw what it is.
     
  6. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,840
  7. Riverglen

    Riverglen Thread Starter

    Joined:
    Aug 28, 2006
    Messages:
    478
    First Name:
    Larry
    It isn't really feasible to set a timer because the time is very long and not too predictable. What I'm doing is replaying earthquake data, and the time it takes to replay an event my be up to a half hour or so, or as short a s a minute.

    One factor that may be relevant is that the replay program is OLD. It was originally written in Quick C (16 bit code) to run under MSDOS. It is a small miracle that it runs in Windows at all.
     
  8. Riverglen

    Riverglen Thread Starter

    Joined:
    Aug 28, 2006
    Messages:
    478
    First Name:
    Larry
    OBP,

    Thanks for the links (both seem to point to the same thing?). I took a quick look. The idea seems to be pretty elaborate and I'll have to study it a bit before I try it. At least he's trying to solve the same problem. I'll report back whether it works for me or not.
     
  9. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    Does the external application close down after processing the file or does it stay open? If it closes on it's own you should be able to use VBA to look at the running processes in the task manager and then just use a LOOP with a WAIT statement to stop processing until the application has been closed.

    Regards,
    Rollin
     
  10. Riverglen

    Riverglen Thread Starter

    Joined:
    Aug 28, 2006
    Messages:
    478
    First Name:
    Larry
    Well, the external app runs until the user elects to stop it or the data in the file is exhausted. Either way, the user gets an "enter any key to exit" message and on his entry the app terminates. Sounds like your suggestion would work regadless of how the app terminates. All I'm trying to accomplish is to defer execution final statements that delete the tempfile untill after the external app closes. I'd need some help to try your idea though. I'm not experienced in writing code for the Windows environment.
     
  11. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    Do a google search for Application.Wait + VBA

    Regards,
    Rollin
     
  12. Riverglen

    Riverglen Thread Starter

    Joined:
    Aug 28, 2006
    Messages:
    478
    First Name:
    Larry
    Rollin,

    Everything I found for Application.Wait depends on being able to know how long to wait. I don't know a priori how long it is going to take to play back my file. If the file is short, I don't want the user to have to sit out a long time-out, and if I don't wait long enough, I'll wind up trying to delete the file while it is still in use.

    I made an attempt to use the code that OBP found. Essentially that approach creates an alternative to the "shell" function, ShellWait. I tried to use it in my sheet but got hung up by my poor understanding of VBA. The code defines ShellWait as a public Sub, and the suggestion is to put it into a new module. When I do that I get an error that seems to indicate that the ShellWait function is not in the scope of the macro that I'm calling it from. Based on my reading of the VBA help file, it should be visible. So....?
     
  13. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    You could try something like what I've written below. My example looks for the process "Winword.exe" in the task manager and then enters a loop which waits for 5 seconds and then checks again. This repeats over and over until the process is eventually closed. Just change the portions in red which represent the application name you are checking for and the time delay.

    Code:
    Sub CheckProcess()
     
    Dim objWMIService, colProcess
     
    Set objWMIService = GetObject("winmgmts:" _
    & "{impersonationLevel=impersonate}!\\" _
    & "." & "\root\cimv2")
     
    Do
    Set colProcess = objWMIService.ExecQuery _
    ("Select * from Win32_Process Where Name = " & "[COLOR="red"]'Winword.exe'[/COLOR]")
    If colProcess.Count > 0 Then
    Application.Wait (Now() + TimeValue("[COLOR="Red"]00:00:05[/COLOR]"))
    End If
    Loop Until colProcess.Count < 1
    
    [I]'YOUR CODE TO EXECUTE ONCE EXTERNAL APPLICATION CLOSED[/I]
     
    Set colProcess = Nothing
    Set objWMIService = Nothing
     
    End Sub
    Regards,
    Rollin
     
  14. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    BTW....I got the ShellWait example to work but it only seems to wait until the called application finishes loading. This leads me to believe that the ShellWait function is only useful if the macro needs to wait for the application to completely load before executing the remainder of the code. From what you describe you need to wait on it to close instead so it does not appear to do what you want. Try my code above and let me know how it works.

    Regards,
    Rollin
     
  15. Riverglen

    Riverglen Thread Starter

    Joined:
    Aug 28, 2006
    Messages:
    478
    First Name:
    Larry
    Rollin,

    Thanks. I will try your approach. And I appreciate your effort in taking a look at ShellWait. I didn't expect this little exercise to turn into such a "science project", but at least I may learn something useful.
     
  16. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/872511

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice