Hi there,
I've got some code to open up an Excel workbook but what I want is for the
code to pause until the Excel workbook is closed.
Sub EditWorkbook(strFile as String)
Dim xlApp As Excel.Application
Dim xlWorkBook As Excel.Workbook
Dim strPath As String
Dim fClosed As Boolean
Set xlApp = New Excel.Application
strPath = "C:\My Documents\" & strFile
Set xlWorkBook = xlApp.Workbooks.Open(strPath)
xlApp.Visible = True
Do Until fClosed = True
If fIsAppRunning("Excel") = False Then
fClosed = True
End If
Loop
MsgBox "Finished"
End Sub
The fIsAppRunning function is from
http://www.mvps.org/access/api/api0007.htm
The workbook opens up OK for editing but when Excel is closed the code
continues to loop through the Do Until Loop statement.
When I type EditWorkbook in the immediate window and then close down Excel,
focus doesn't come back to Access directly until I go Ctrl + Break. When
stepping through the code after that I find out that the fIsAppRunning
function still says that Excel is still running although it's not visible
any more. Going Ctrl + Alt + Del to view what programs are still running, I
see Excel is still in the list. When I click cancel for this close program
list, click the End button and then view the list of open programs again,
Excel is no longer in the list.
I've tried using the ShellAndWait sub routine and also the sSleep sub
routine from the same site.
What is the best way to do this? Does someone have a solution to why Excel
is still running in the background and won't close correctly even when the X
is clicked in Excel.
Stewart