|
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 |
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 |
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. |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
| All times are GMT +1. The time now is 01:42 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com