Excel XP, Win XP
The code below does as follows:
Opens the "....MASTER..." file.
Loops through 22 files in the C:\Temp folder.
Opens each file.
Copies the lone sheet to the MASTER file.
Closes each file.
The MASTER file is left open (like I want).
It all works fine.
Problem: The Project Explorer window in the VBE shows that the LAST of the
22 files is still open. I cannot access the file in any way that I know of.
The fact that the file is open does not bother my project in any way. I can
close that file only by closing Excel.
Question: Why is that lone last file open (in the VBE)?
Thanks for your time. Otto
Sub CombineBooks()
ThePath = "C:\Temp\"
Set MASTERwb = Workbooks.Open(ThePath & "Daily Error report
MASTER.xls")
ChDir ThePath
TheFile = Dir("*.csv")
Do While TheFile < ""
If LCase(TheFile) < LCase("Daily Error report MASTER.xls") Then
Application.DisplayAlerts = False
Set wb = Workbooks.Open(ThePath & "\" & TheFile)
Application.DisplayAlerts = True
wb.Worksheets(1).Copy Befo=MASTERwb.Worksheets(1)
MASTERwb.Worksheets(1).Name = Left(TheFile, Len(TheFile) -
4)
wb.Close SaveChanges:=False
End If
TheFile = Dir
Loop
End Sub

I just found a significant point of interest. That file appears in the VBE,
yes, but ONLY if the VBE was open at the time of code execution It does not
appear in the VBE if the VBE was closed (and I open the VBE after code
execution to see). It would appear then that the "problem" I stated is a
non-problem. I still would like your opinion on this. Thanks for your
time. Otto
"Otto Moehrbach" wrote in message
...
Excel XP, Win XP
The code below does as follows:
Opens the "....MASTER..." file.
Loops through 22 files in the C:\Temp folder.
Opens each file.
Copies the lone sheet to the MASTER file.
Closes each file.
The MASTER file is left open (like I want).
It all works fine.
Problem: The Project Explorer window in the VBE shows that the LAST of
the 22 files is still open. I cannot access the file in any way that I
know of. The fact that the file is open does not bother my project in any
way. I can close that file only by closing Excel.
Question: Why is that lone last file open (in the VBE)?
Thanks for your time. Otto
Sub CombineBooks()
ThePath = "C:\Temp\"
Set MASTERwb = Workbooks.Open(ThePath & "Daily Error report
MASTER.xls")
ChDir ThePath
TheFile = Dir("*.csv")
Do While TheFile < ""
If LCase(TheFile) < LCase("Daily Error report MASTER.xls")
Then
Application.DisplayAlerts = False
Set wb = Workbooks.Open(ThePath & "\" & TheFile)
Application.DisplayAlerts = True
wb.Worksheets(1).Copy Befo=MASTERwb.Worksheets(1)
MASTERwb.Worksheets(1).Name = Left(TheFile,
Len(TheFile) - 4)
wb.Close SaveChanges:=False
End If
TheFile = Dir
Loop
End Sub

Tom
You did it again! That took care of the little "ghost". Thanks. Otto
"Tom Ogilvy" wrote in message
...
at the end try

Loop
set wb = Nothing
End Sub

Since wb isn't declared locally, perhaps it has been declared at the
module
level and is holding a reference to the last file opened.

--
Regards,
Tom Ogilvy


"Otto Moehrbach" wrote:

Excel XP, Win XP
The code below does as follows:
Opens the "....MASTER..." file.
Loops through 22 files in the C:\Temp folder.
Opens each file.
Copies the lone sheet to the MASTER file.
Closes each file.
The MASTER file is left open (like I want).
It all works fine.
Problem: The Project Explorer window in the VBE shows that the LAST of
the
22 files is still open. I cannot access the file in any way that I know
of.
The fact that the file is open does not bother my project in any way. I
can
close that file only by closing Excel.
Question: Why is that lone last file open (in the VBE)?
Thanks for your time. Otto
Sub CombineBooks()
ThePath = "C:\Temp\"
Set MASTERwb = Workbooks.Open(ThePath & "Daily Error report
MASTER.xls")
ChDir ThePath
TheFile = Dir("*.csv")
Do While TheFile < ""
If LCase(TheFile) < LCase("Daily Error report MASTER.xls")
Then
Application.DisplayAlerts = False
Set wb = Workbooks.Open(ThePath & "\" & TheFile)
Application.DisplayAlerts = True
wb.Worksheets(1).Copy Befo=MASTERwb.Worksheets(1)
MASTERwb.Worksheets(1).Name = Left(TheFile,
Len(TheFile) -
4)
wb.Close SaveChanges:=False
End If
TheFile = Dir
Loop
End Sub