Greetings all,

I need to pick your collective brains again since I couldn't find this
exact issue in the archives. I've got a workbook which looks in a
particular directory on a fileserver, and loops through each .xls
file: opening, copying some key bits, pasting, and then closing each.
There's roughly 50 excel files in the directory that this workbook
deals with. Everything seems to work fine except at the end of the
run, there's usually a half-dozen files left open. All the others were
successfully closed, but not these brave few. This has become
something of a proud nail for me - it's really only a minor
inconvenience, but it's starting to bug the heck out of me. ;^)

Here's what I'm doing (Excel 2007 by the way):

Sub PullData()
'declarations and other stuff snipped

File = Dir(CStr(SrcDir & "\*.xls*"))
Do While Len(File) 0

'open each workbook
Set srcWbk = Workbooks.Open(SrcDir & "\" & File)

'does a bunch of copy/paste stuff here, snipped


srcWbk.Close True
File = Dir

Loop
End Sub


Any thoughts? That srcWbk.Close bit just seems to get ignored around
15% of the time.

thanks!

Here is one scenario that could cause such a condition:

There is a line: Application.DisplayAlerts = False
There are changes in the culprit files that occur that would normally
trigger the "Do you want to save changes" message but you don't see it
because the Application.DisplayAlerts = True command is after the
srcWbk.Close command.

Just a guess, but it has to be something close to that.

" wrote:

Greetings all,

I need to pick your collective brains again since I couldn't find this
exact issue in the archives. I've got a workbook which looks in a
particular directory on a fileserver, and loops through each .xls
file: opening, copying some key bits, pasting, and then closing each.
There's roughly 50 excel files in the directory that this workbook
deals with. Everything seems to work fine except at the end of the
run, there's usually a half-dozen files left open. All the others were
successfully closed, but not these brave few. This has become
something of a proud nail for me - it's really only a minor
inconvenience, but it's starting to bug the heck out of me. ;^)

Here's what I'm doing (Excel 2007 by the way):

Sub PullData()
'declarations and other stuff snipped

File = Dir(CStr(SrcDir & "\*.xls*"))
Do While Len(File) 0

'open each workbook
Set srcWbk = Workbooks.Open(SrcDir & "\" & File)

'does a bunch of copy/paste stuff here, snipped


srcWbk.Close True
File = Dir

Loop
End Sub


Any thoughts? That srcWbk.Close bit just seems to get ignored around
15% of the time.

thanks!

Or, it could be that the network becomes temporarily unavailable and ignores
your close/save instruction. You could use Wait or Timer to test whether
the file is still open after a certain interval and, if so, try to close
again, counting the times you try to close up to, say, 10. Of course, that
would slow down your program. James

"JLGWhiz" wrote in message
...
Here is one scenario that could cause such a condition:

There is a line: Application.DisplayAlerts = False
There are changes in the culprit files that occur that would normally
trigger the "Do you want to save changes" message but you don't see it
because the Application.DisplayAlerts = True command is after the
srcWbk.Close command.

Just a guess, but it has to be something close to that.

" wrote:

Greetings all,

I need to pick your collective brains again since I couldn't find this
exact issue in the archives. I've got a workbook which looks in a
particular directory on a fileserver, and loops through each .xls
file: opening, copying some key bits, pasting, and then closing each.
There's roughly 50 excel files in the directory that this workbook
deals with. Everything seems to work fine except at the end of the
run, there's usually a half-dozen files left open. All the others were
successfully closed, but not these brave few. This has become
something of a proud nail for me - it's really only a minor
inconvenience, but it's starting to bug the heck out of me. ;^)

Here's what I'm doing (Excel 2007 by the way):

Sub PullData()
'declarations and other stuff snipped

File = Dir(CStr(SrcDir & "\*.xls*"))
Do While Len(File) 0

'open each workbook
Set srcWbk = Workbooks.Open(SrcDir & "\" & File)

'does a bunch of copy/paste stuff here, snipped


srcWbk.Close True
File = Dir

Loop
End Sub


Any thoughts? That srcWbk.Close bit just seems to get ignored around
15% of the time.

thanks!

Hey gang, thanks for the input. I made one change that seems to have
fixed it:

srcWbk.Close SaveChanges:=False

After looking into what changes were being made in the source file,
it's clear none needed to be saved. Problem solved!

thanks!