Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 13
Default Auto Excel workbook close: save= false during an auto subroutine

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
  #2   Report Post  
Posted to microsoft.public.excel.programming
JMB JMB is offline
external usenet poster
 
Posts: 2,062
Default Auto Excel workbook close: save= false during an auto subroutine

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

  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 121
Default Auto Excel workbook close: save= false during an auto subroutine

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.
  #4   Report Post  
Posted to microsoft.public.excel.programming
JMB JMB is offline
external usenet poster
 
Posts: 2,062
Default Auto Excel workbook close: save= false during an auto subrouti

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




  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 13
Default Auto Excel workbook close: save= false during an auto subrouti

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






  #6   Report Post  
Posted to microsoft.public.excel.programming
JMB JMB is offline
external usenet poster
 
Posts: 2,062
Default Auto Excel workbook close: save= false during an auto subrouti

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



  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1
Default Auto Excel workbook close: save= false during an auto subroutine


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

  #8   Report Post  
Posted to microsoft.public.excel.programming
JMB JMB is offline
external usenet poster
 
Posts: 2,062
Default Auto Excel workbook close: save= false during an auto subrouti

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


  #9   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 13
Default Auto Excel workbook close: save= false during an auto subrouti

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









  #10   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1
Default Auto Excel workbook close: save= false during an auto subroutine


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



  #11   Report Post  
Posted to microsoft.public.excel.programming
JMB JMB is offline
external usenet poster
 
Posts: 2,062
Default Auto Excel workbook close: save= false during an auto subrouti

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









Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Auto save and close Frank Excel Worksheet Functions 0 November 19th 09 11:50 AM
Auto Open, Refresh, Save, Close Jimmycooker Excel Discussion (Misc queries) 0 February 6th 06 02:34 PM
Auto-save worksheet on close? [email protected] Excel Discussion (Misc queries) 1 January 2nd 06 03:12 PM
Auto Close Excel Workbook MBlake[_2_] Excel Programming 2 June 2nd 05 09:44 PM
Auto Close and Save scottwilsonx[_7_] Excel Programming 3 July 7th 04 11:09 AM


All times are GMT +1. The time now is 05:21 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"