Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
End Excel Application
Can't seem to get Excel to quit with: Application.quit
Can anyone help per the code below?! TIA! Sub Auto_Open() Dim PathSrc As String, PathDest As String Dim srcList As Variant Dim i As Long, sDest As String Dim bkSrc As Workbook, bkDest As Workbook Dim srcList1 As Variant, NumFiles As Long PathSrc = "Y:\Sales\Target Customer\2005 Mainframe Download\" PathDest = "Y:\Sales\Target Customer\2005 Mainframe Download - Main\" Workbooks.Open "C:\Target\Supplant\Supplant.xls" NumFiles = ActiveWorkbook.Worksheets("Sheet1").Range("D1") srcList1 = ActiveWorkbook.Worksheets("Sheet1").Range ("B1").Resize(NumFiles, 1).Value Workbooks("Supplant.xls").Close SaveChanges:=False ReDim srcList(1 To NumFiles) For i = 1 To NumFiles srcList(i) = srcList1(i, 1) Next For i = LBound(srcList) To UBound(srcList) Set bkSrc = Workbooks.Open(PathSrc & srcList(i)) sDest = bkSrc.Name sDest = Left(sDest, Len(sDest) - 4) & "M.xls" Set bkDest = Workbooks.Open(PathDest & sDest) bkSrc.Worksheets(1).Rows(1).Resize(1000).Copy _ Destination:=bkDest.Worksheets(1).Range("A1") bkSrc.Close SaveChanges:=False Application.DisplayAlerts = False bkDest.SaveAs bkDest.FullName, xlWorkbook bkDest.Close SaveChanges:=False Application.DisplayAlerts = True Next Workbooks("RAW VBA.xls").Close SaveChanges:=False Application.Quit End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
End Excel Application
Hi Kenny,
I don't see anything obviously wrong with your code, so let me hazard a guess as to what the problem is. It appears as if your code is running from within the workbook named "RAW VBA.xls". If this is true, then that's causing the problem. As soon as you close a workbook, all VBA code contained within it immediately stops running. Therefore, when you close the "RAW VBA.xls" workbook your code never reaches the line that quits Excel. Try modifying the last two lines of your code like so: Workbooks("RAW VBA.xls").Saved = False Application.Quit This will allow code execution to reach the Application.Close line without prompting you to save the "RAW VBA.xls" workbook. -- Rob Bovey, Excel MVP Application Professionals http://www.appspro.com/ * Take your Excel development skills to the next level. * Professional Excel Development http://www.appspro.com/Books/Books.htm "KENNY" wrote in message ... Can't seem to get Excel to quit with: Application.quit Can anyone help per the code below?! TIA! Sub Auto_Open() Dim PathSrc As String, PathDest As String Dim srcList As Variant Dim i As Long, sDest As String Dim bkSrc As Workbook, bkDest As Workbook Dim srcList1 As Variant, NumFiles As Long PathSrc = "Y:\Sales\Target Customer\2005 Mainframe Download\" PathDest = "Y:\Sales\Target Customer\2005 Mainframe Download - Main\" Workbooks.Open "C:\Target\Supplant\Supplant.xls" NumFiles = ActiveWorkbook.Worksheets("Sheet1").Range("D1") srcList1 = ActiveWorkbook.Worksheets("Sheet1").Range ("B1").Resize(NumFiles, 1).Value Workbooks("Supplant.xls").Close SaveChanges:=False ReDim srcList(1 To NumFiles) For i = 1 To NumFiles srcList(i) = srcList1(i, 1) Next For i = LBound(srcList) To UBound(srcList) Set bkSrc = Workbooks.Open(PathSrc & srcList(i)) sDest = bkSrc.Name sDest = Left(sDest, Len(sDest) - 4) & "M.xls" Set bkDest = Workbooks.Open(PathDest & sDest) bkSrc.Worksheets(1).Rows(1).Resize(1000).Copy _ Destination:=bkDest.Worksheets(1).Range("A1") bkSrc.Close SaveChanges:=False Application.DisplayAlerts = False bkDest.SaveAs bkDest.FullName, xlWorkbook bkDest.Close SaveChanges:=False Application.DisplayAlerts = True Next Workbooks("RAW VBA.xls").Close SaveChanges:=False Application.Quit End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel vx Application | Excel Programming | |||
2 excel application | Excel Programming | |||
macro to close excel application other than application.quit | Excel Programming | |||
Excel Application needs a DLL | Excel Programming | |||
application.quit will not shut off application | Excel Programming |