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.

"(E-Mail Removed)" 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" <(E-Mail Removed)> wrote in message
newsFB9EF4F-99E3-4E4B-AB4F-(E-Mail Removed)...
> 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.
>
> "(E-Mail Removed)" 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!