End Excel Application

[KENNY]

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

[Rob Bovey]

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" <[email protected]> wrote in message
news:[email protected]...
> 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
>