Results 1 to 18 of 18

Thread: VB 2 Excel drives me ever crazier [RESOLVED]

Threaded View

  1. #1

    Thread Starter
    vbuggy krtxmrtz's Avatar
    Join Date
    May 2002
    Location
    In a probability cloud
    Posts
    5,573

    VB 2 Excel drives me ever crazier [RESOLVED]

    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:
    1. Sub JustDoIt()
    2.  
    3.     Dim xlApp As Excel.Application
    4.     Dim xlBook As Excel.Workbook
    5.     Dim xlWS As Excel.Worksheet
    6.    
    7.     On Error Resume Next
    8.  
    9.     Set xlApp = GetObject(, "Excel.Application")
    10.  
    11.     If Err.Number = 429 Then
    12.         ' Excel is NOT running, so create a new instance
    13.         Set xlApp = CreateObject("Excel.Application")
    14.     End If
    15.    
    16.     Set xlBook = xlApp.Workbooks.Open(App.Path & "\TestFile.xls")
    17.  
    18.     xlApp.Visible = False
    19.     xlApp.UserControl = True
    20.     DoEvents
    21.  
    22.     Set xlWS = xlBook.Worksheets(1)
    23.  
    24.     With xlWS
    25.         .Select
    26.         .Rows("2:2").Select
    27.         Selection.Insert Shift:=xlDown
    28.         For n = 1 To 100
    29.             .Cells(n, 2).Value = n
    30.         Next
    31.     End With
    32.  
    33.     Set xlWS = Nothing
    34.     xlApp.DisplayAlerts = False
    35.     xlBook.Close SaveChanges:=True
    36.     Set xlBook = Nothing
    37.     xlApp.Quit
    38.     Set xlApp = Nothing
    39.  
    40. End Sub
    Last edited by krtxmrtz; May 26th, 2004 at 02:15 AM.
    Lottery is a tax on people who are bad at maths
    If only mosquitoes sucked fat instead of blood...
    To do is to be (Descartes). To be is to do (Sartre). To be do be do (Sinatra)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width