CODEWITHSUNDEEP
vbams-access
sigil
sigil

Reputation: 9556

Wait until Workbook.Close finishes

Using Access 2010, WinXP. I'm trying to iterate through a collection of folders, collecting values from each of the Excel files in each folder to add to a table in my database. The problem is that even though I'm closing the workbook each time, it must not be closing fast enough, because if the same filename appears in two consecutive folders, I get an error message:

Run-time error '1004':
A document with the name 'someWorkbook.xls' is already open.

Here's my code:

sub traverse()

Dim fso As filesystemobject
Dim fObj As File
Dim fldObj As Folder
Dim fCol As Files
Dim xlApp As Excel.Application
dim xlBk as Excel.Workbook

Set xlApp = New Excel.Application
Set fso = New filesystemobject
For Each fldObj In fso.GetFolder("c:\basePath").SubFolders
    Set fCol = fldObj.Files
    For Each fObj In fCol
        If UCase(Left(fso.GetExtensionName(fObj.Name), 3)) = "XLS" Then
           Set xlBk=xlApp.workbooks.open(fObj.path)
           getData(xlBk)
           xlBk.close false
           DoEvents
        End If
    Next
Next

End Sub

I tried quitting the Excel instance and starting a new one in the For Each fObj loop, as follows:

set xlApp=new Excel.Application
Set xlBk=xlApp.workbooks.open(fObj.path)
getData(xlBk)
xlBk.close false
DoEvents
xlApp.quit
DoEvents

but that only ended up opening a whole bunch of Excel instances without quitting them, as when it crashed I checked the task manager and found about 30 Excel instances.

Is there something else I can do to guarantee that my code doesn't proceed until the Excel action triggered by .Close or .Quit has completed?

Upvotes: 2

Views: 4459

Answers (2)

Walter Rauschenberger
Walter Rauschenberger

Reputation: 101

I found several "wait for close" solutions and finally ended up with the following code which satisfied me last but not least because in contrast to Wait Sleep is aplication independant and thus the code may easily be changed to a word document:

Private Sub WaitForClose(ByVal sWbk As String, _
                Optional ByVal oApp As Excel.Application = Nothing)
' -----------------------------------------------------------------
' Precondition: oApp when provided the (or there is an) Excel
' instance with the Workbook sWbk open.
' -----------------------------------------------------------------
Dim bOpen   As Boolean
Dim xlWbk     As Excel.Workbook

   If oApp Is Nothing Then Set oApp = GetObject(sWbk).Application
   Do
      If oApp.Workbooks.Count = 0 Then Exit Sub
      bOpen = False
      For Each xlWbk In oApp.Workbooks
         If xlWbk.FullName = sWbk Then bOpen = True
      Next
      If bOpen Then Sleep 1000
   Loop While bOpen

End Sub

Upvotes: 0

Nick Perkins
Nick Perkins

Reputation: 1327

@sigil Answered his own question in comments.

I found the problem. It was due to a piece of code that I didn't post; the calls to workbooks.open and workbook.close actually happen inside getData, where there is an if-then clause that exits getData without closing the workbook. I added a workbook.close before exit function and it is behaving properly now. Sorry for not posting the code as-is, I thought it would be easier to get help if I simplified it. Next time I'll know better.

Upvotes: 1

Related Questions