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

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

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.

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

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

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

Have a similar problem and will appreciate if you post the complete cod

--
roum
-----------------------------------------------------------------------
roumi's Profile: http://www.excelforum.com/member.php...fo&userid=2414
View this thread: http://www.excelforum.com/showthread.php?threadid=37730

for the object variable? right after you open a workbook, it becomes the
activeworkbook.

Const Path As String = <your path
Dim WkBk As Workbook

< your code

Workbooks.Open Filename:=Path
Set WkBk = ActiveWorkbook

<your code

WkBk.Close SaveChanges:=False (or True)





"roumi" wrote:


Have a similar problem and will appreciate if you post the complete code


--
roumi
------------------------------------------------------------------------
roumi's Profile: http://www.excelforum.com/member.php...o&userid=24140
View this thread: http://www.excelforum.com/showthread...hreadid=377308

Thanks again.

I actually used your suggestions for Alerts = False
and I also split the filepath name string from the directory part of the
pathname in order to be able to close the variable file name.

These were useful suggestions that will save me from workbook cluttering
from the various files that I pull.

Below is a copy of the VBA code for those who had requested it:



Sub MTDMcr()
'
' MTDMcr Macro
' Macro recorded 4/5/2005 by *****'



Dim userNT, path1, path2, path3, month3, month2, LDR As String
Dim Year, month, DOM, Cbreak As Integer

userNT = InputBox("Enter your Windows NT username", "")
Year = InputBox("Enter the 4 digit year as integer", "")
month = InputBox("Enter the month as integer", "")
DOM = InputBox("MTD to run for what day of the month?", "")
LDR = InputBox("For what day did you run the report last? If you had not
run it for this month, enter 0", "")
Cbreak = 0

Select Case month
Case Is = 1
month2 = "01"
month3 = "January"
Case Is = 2
month2 = "02"
month3 = "February"
Case Is = 3
month2 = "03"
month3 = "March"
Case Is = 4
month2 = "04"
month3 = "April"
Case Is = 5
month2 = "05"
month3 = "May"
Case Is = 6
month2 = "06"
month3 = "June"
Case Is = 7
month2 = "07"
month3 = "July"
Case Is = 8
month2 = "08"
month3 = "August"
Case Is = 9
month2 = "09"
month3 = "September"
Case Is = 10
month2 = "10"
month3 = "October"
Case Is = 11
month2 = "11"
month3 = "November"
Case Is = 12
month2 = "12"
month3 = "December"
Case Else ' For Invalid Entries
MsgBox ("Please enter a valid month no. (1 - 12)")
End ' End Program
End Select

' LDR Value
If LDR = 31 Then
LDR = LDR
Else
LDR = LDR + 1
End If


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



path1 = "C:\Documents and Settings\" & userNT & "\My Documents\Adherence
Rpts\" & month3 & "\"
path2 = "ADH" & Year & "." & month2 & "." & LDR & ".xls"
path3 = path1 & path2



While Cbreak < 2 And LDR <= DOM

If LDR = 31 Then
Cbreak = Cbreak + 1 ' Allow to run day 31 once
End If
' extract
Workbooks.Open Filename:=path3
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

'remember to close each one after use
Application.DisplayAlerts = False
Workbooks(path2).Close SaveChanges:=False
Application.DisplayAlerts = False

If LDR = 31 Then
LDR = LDR
Else
LDR = LDR + 1 ' LDR Value increment
End If
If LDR = 31 Then
Cbreak = Cbreak + 1 ' Break control
End If

If LDR 0 And LDR < 10 Then 'LDR format control
LDR = "0" & LDR
Else
LDR = LDR
End If

path2 = "ADH" & Year & "." & month2 & "." & LDR & ".xls"
path3 = path1 & path2
' end extract
Wend

' Remove Sentinel from RwMTD2 file and redo Subtotals for MTD
Sheets("Subtotals").Select
Cells.Select
Selection.Delete Shift:=xlUp
Range("A1").Select
Sheets("MTD Data").Select
Cells.Select
Selection.Copy
Sheets("Subtotals").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending,
Key2:=Range("C2") _
, Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom
Range("a1").Select
Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(8, 9,
10, _
11, 12, 13, 14, 15), Replace:=True, PageBreaks:=False,
SummaryBelowData:=True
ActiveSheet.Outline.ShowLevels RowLevels:=2
Range("A1").Select


MsgBox ("Same this file using the same name. Close all others without saving")
End Sub

Thank you!!!


--
roumi
------------------------------------------------------------------------
roumi's Profile: http://www.excelforum.com/member.php...o&userid=24140
View this thread: http://www.excelforum.com/showthread...hreadid=377308

you're welcome. i'm glad it helped.


"tomwashere2" wrote:

Thanks again.

I actually used your suggestions for Alerts = False
and I also split the filepath name string from the directory part of the
pathname in order to be able to close the variable file name.

These were useful suggestions that will save me from workbook cluttering
from the various files that I pull.

Below is a copy of the VBA code for those who had requested it:



Sub MTDMcr()
'
' MTDMcr Macro
' Macro recorded 4/5/2005 by *****'



Dim userNT, path1, path2, path3, month3, month2, LDR As String
Dim Year, month, DOM, Cbreak As Integer

userNT = InputBox("Enter your Windows NT username", "")
Year = InputBox("Enter the 4 digit year as integer", "")
month = InputBox("Enter the month as integer", "")
DOM = InputBox("MTD to run for what day of the month?", "")
LDR = InputBox("For what day did you run the report last? If you had not
run it for this month, enter 0", "")
Cbreak = 0

Select Case month
Case Is = 1
month2 = "01"
month3 = "January"
Case Is = 2
month2 = "02"
month3 = "February"
Case Is = 3
month2 = "03"
month3 = "March"
Case Is = 4
month2 = "04"
month3 = "April"
Case Is = 5
month2 = "05"
month3 = "May"
Case Is = 6
month2 = "06"
month3 = "June"
Case Is = 7
month2 = "07"
month3 = "July"
Case Is = 8
month2 = "08"
month3 = "August"
Case Is = 9
month2 = "09"
month3 = "September"
Case Is = 10
month2 = "10"
month3 = "October"
Case Is = 11
month2 = "11"
month3 = "November"
Case Is = 12
month2 = "12"
month3 = "December"
Case Else ' For Invalid Entries
MsgBox ("Please enter a valid month no. (1 - 12)")
End ' End Program
End Select

' LDR Value
If LDR = 31 Then
LDR = LDR
Else
LDR = LDR + 1
End If


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



path1 = "C:\Documents and Settings\" & userNT & "\My Documents\Adherence
Rpts\" & month3 & "\"
path2 = "ADH" & Year & "." & month2 & "." & LDR & ".xls"
path3 = path1 & path2



While Cbreak < 2 And LDR <= DOM

If LDR = 31 Then
Cbreak = Cbreak + 1 ' Allow to run day 31 once
End If
' extract
Workbooks.Open Filename:=path3
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

'remember to close each one after use
Application.DisplayAlerts = False
Workbooks(path2).Close SaveChanges:=False
Application.DisplayAlerts = False

If LDR = 31 Then
LDR = LDR
Else
LDR = LDR + 1 ' LDR Value increment
End If
If LDR = 31 Then
Cbreak = Cbreak + 1 ' Break control
End If

If LDR 0 And LDR < 10 Then 'LDR format control
LDR = "0" & LDR
Else
LDR = LDR
End If

path2 = "ADH" & Year & "." & month2 & "." & LDR & ".xls"
path3 = path1 & path2
' end extract
Wend

' Remove Sentinel from RwMTD2 file and redo Subtotals for MTD
Sheets("Subtotals").Select
Cells.Select
Selection.Delete Shift:=xlUp
Range("A1").Select
Sheets("MTD Data").Select
Cells.Select
Selection.Copy
Sheets("Subtotals").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending,
Key2:=Range("C2") _
, Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom
Range("a1").Select
Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(8, 9,
10, _
11, 12, 13, 14, 15), Replace:=True, PageBreaks:=False,
SummaryBelowData:=True
ActiveSheet.Outline.ShowLevels RowLevels:=2
Range("A1").Select


MsgBox ("Same this file using the same name. Close all others without saving")
End Sub