I'm not the first one in this forum who helplessly tries to have Excel close after a VB app has transferred data to it. I've searched the forum and found posts galore on this issue and have finally written a subroutine based on what I've collected. This subroutine leaves Excel in the Task Manager list (=> ctrl/alt/del). However, if the lines
.Rows("2:2").Select
Selection.Insert Shift:=xlDown
are removed, then it works and Excel is correctly unloaded and appears no longer in the task manager list.
I'm more than puzzled and about to believe that my pc is haunted or jinxed or something. Any help will be appreciated.
VB Code:
Sub JustDoIt() Dim xlApp As Excel.Application Dim xlBook As Excel.Workbook Dim xlWS As Excel.Worksheet On Error Resume Next Set xlApp = GetObject(, "Excel.Application") If Err.Number = 429 Then ' Excel is NOT running, so create a new instance Set xlApp = CreateObject("Excel.Application") End If Set xlBook = xlApp.Workbooks.Open(App.Path & "\TestFile.xls") xlApp.Visible = False xlApp.UserControl = True DoEvents Set xlWS = xlBook.Worksheets(1) With xlWS .Select .Rows("2:2").Select Selection.Insert Shift:=xlDown For n = 1 To 100 .Cells(n, 2).Value = n Next End With Set xlWS = Nothing xlApp.DisplayAlerts = False xlBook.Close SaveChanges:=True Set xlBook = Nothing xlApp.Quit Set xlApp = Nothing End Sub




Reply With Quote