i would suggest trying the following. you can disable excels messages with

application.displayalerts = false

before your code that generates the message
and at the end of your code

application.displayalerts = true

this is also good for deleting sheets (and suppressing the "are you sure")
as well as closing a workbook and suppressing ("do you want to save").

just make sure to turn it back on if you normally place any reliance on
those safety nets



"tomwashere2" wrote:

To JMB and Will Benson

Thank you Thank YOU!!!


This saves me from Workbook cluttering.

One Last Question for you guys:

Is there a way to auto answer to the follow up question that Excel furnishes
with each closed file. And that question is, "Would you like to save the
information on the clipboard?"...

My answer would of course be no.







"JMB" wrote:

I figured he posted only a portion of the code and had already defined path3
for the first iteration. He did say he tried the close method with the
specific filename and it worked, so it must have been defined somewhere.

Split and Join are nice for separating paths from filenames.

On the filename issue, he could also capture the filename right after
opening the file

x = activeworkbook.name
..
..
..
workbooks(x).close savechanges:=false


Hopefully, this'll post w/o "We're sorry........" (fingers crossed)


"William Benson" wrote:

How can tomwashere2 refer to Path3 before he gets to the code where it is
assigned. Isn't it = "" in the first iteration?

By the way, I love Split, I never knew about it.

B.

"JMB" wrote in message
...
i think there is an issue with path3. it is the complete path right?
(C:\temp\xyz.xls). to close the workbook, i believe you'll need to
separate
the workbook name from the rest of the path.

x = Split(path3, "\", -1, vbTextCompare)
Workbooks(x(UBound(x))).Close savechanges:=False

Or, set an object variable = to the file you opened (lets say WkBk) and
use

WkBk.Close SaveChanges:=False

"tomwashere2" wrote:

I have a subroutine (macro) that automatically pulls data from various
workbooks. I would like to auto close each workbook when I am done
extracting the data.

The vba code to do this according to Excel help is:

Workbooks("BOOK1.XLS").Close SaveChanges:=False

The code works when I specify the name such as "BOOK1.XLS" . The problem
is
that the file names vary depending on dates etc. So I assign a variable
to
identify the names each time it is passed through the loop and
incremented.
Therefore the name of each stays the same through a string variable named
"path3" as follows:

While LDR <= DOM

Workbooks.Open Filename:=path3
' extract
Selection.RemoveSubtotal
Range("A2:O2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Windows("RwMTD.xls").Activate
Sheets("MTD Data").Select
Range("A1").Select
Selection.End(xlDown).Select
ActiveSheet.Paste
Range("A1").Select

' This would be the point at which I would want to close the (Path3)
file

' LDR Value
LDR = LDR + 1
If LDR < 0 Or LDR 31 Then
MsgBox ("Please enter a valid number for the day of month
i.e.(1-31)")
End
Else
If LDR 0 And LDR < 10 Then
LDR = "0" & LDR
Else
LDR = LDR
End If
End If
path2 = "\rpt." & Year & "." & month2 & "." & LDR & ".xls"
path3 = path1 & path2
' end extract

Wend