VB 2 Excel drives me ever crazier [RESOLVED]

[krtxmrtz]

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.

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

[si_the_geek]

What is Selection? (Selection.Insert Shift:=xlDown)
If you want to use the Excel Selection object you need to specify that. (ie: xlApp.Selection.Insert Shift:=xlDown)

Whenever possible you should not use the Selection object as the user can alter it while your program is running - just use whatever objects you have selected instead, eg:

With xlWS
        .Rows("2:2").Insert Shift:=xlDown
        For n = 1 To 100
            .Cells(n, 2).Value = n
        Next
    End With

[krtxmrtz]

Originally posted by si_the_geek
What is Selection? (Selection.Insert Shift:=xlDown)
If you want to use the Excel Selection object you need to specify that. (ie: xlApp.Selection.Insert Shift:=xlDown)

The reason for that is I directly pasted some code from an Excel macro.
This though does not take care of the problem, Excel is still bewitching the computer memory...

[si_the_geek]

You cant just paste verbatim, you need to precede Excel specific objects with an appropriate reference (as Selection is a member of Application, it needs an application object, in this case xlApp).


Other issues that I can see:

Isn't xlApp.UserControl read only? I thought you couldnt change that, so it would be best to check, and remove it if it is read only.
(I dont use it in any of my programs, and I dont have this issue).

You have "On Error Resume Next". You should NOT use this if there are any unknown issues (such as you have), and should avoid it at all other times unless you dont mind if the entire code after it fails. Try to use proper error handling instead.

[krtxmrtz]

Originally posted by si_the_geek
Isn't xlApp.UserControl read only? I thought you couldnt change that, so it would be best to check, and remove it if it is read only.
(I dont use it in any of my programs, and I dont have this issue).

You have "On Error Resume Next". You should NOT use this if there are any unknown issues (such as you have), and should avoid it at all other times unless you dont mind if the entire code after it fails. Try to use proper error handling instead.

I don't really know what usercontrol is about. I posted about it yesterday in this link
I've removed the references to UserControl but this still does not solve the problem.

The reason for On Error Resume Next is that after

Set xlApp = GetObject(, "Excel.Application")

an error (429) was expected if Excel was not running...

[si_the_geek]

Ok, but it needs to be turned off again after the error, or you get situations like this where you dont know if an error is occuring or not.

Ideally you should have an error handler, but as you havent the easiest way to return to normal error handling is the "Goto 0", eg:

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
    On Error Goto 0   'this [b]should[/b] be to an error handler, rather than 0

[krtxmrtz]

Originally posted by si_the_geek
Ok, but it needs to be turned off again after the error, or you get situations like this where you dont know if an error is occuring or not.
[/Highlight]

You're right, of course. But I'm not going to write the error handling part of the subroutine until I'm sure it works. If I can't solve this issue about Excel not vanishing from memory, then maybe I'll rewrite it afresh saving all data separated by tabs in a text file. It won't be too convenient but this application is for my personal use and I could make do with it for now.

[si_the_geek]

Just use the "On Error Goto 0" as i posted above, it will make the rest of the code run without ignoring errors (I didnt make that very clear before ).

Hopefully then you will see what is causing the problem.

I'm not going to write the error handling part of the subroutine until I'm sure it works

You already have error handling of a sort, it's just the opposite way to what you need for development work. Besides, a proper error handler is much more useful in development than not having one.

[krtxmrtz]

Originally posted by si_the_geek
Just use the "On Error Goto 0" as i posted above, it will make the rest of the code run without ignoring errors (I didnt make that very clear before ).

Hopefully then you will see what is causing the problem.


You already have error handling of a sort, it's just the opposite way to what you need for development work. Besides, a proper error handler is much more useful in development than not having one.

I must admit what you say makes a lot of sense. I have followed your advice and have found error 1004 about the "select" property, still caused by the line

.Rows("2:2").Insert Shift:=xlDown

I wonder what the correct way is to insert a line at row #2. The idea is that the most recent data are always stored at the top of the sheet.

Btw, whether or not the code can be fixed, I've already learned something today !

[si_the_geek]

good stuff

Unfortunately error 1004 is a generic one, it has many different messages/causes/solutions. It might help if you tell us the exact message it shows.

I've just tried it out, and this works for me:

xlWS.Rows("2:2").Insert Shift:=xlDown
or:
xlWS.Rows("2:2").Insert Shift:=-4121 '(xlDown = -4121)

It might help to run this line of code just before it:
xlWS.Activate



(by the way, to insert 100 rows it would be "2:101" instead of "2:2")

[krtxmrtz]

Originally posted by si_the_geek
good stuff

Unfortunately error 1004 is a generic one, it has many different messages/causes/solutions. It might help if you tell us the exact message it shows.

I've just tried it out, and this works for me:

xlWS.Rows("2:2").Insert Shift:=xlDown
or:
xlWS.Rows("2:2").Insert Shift:=-4121 '(xlDown = -4121)

It might help to run this line of code just before it:
xlWS.Activate



(by the way, to insert 100 rows it would be "2:101" instead of "2:2")

At the moment I'm siting in front of another computer where VB is not installed so I'll try out your suggestions at a later time. I'll post the exact text later (the error text comes out in Spanish and I'm not sure an approximate translation will be too useful). At any rate the error occurs at the statement I've mentioned, that's for sure. As far as I can recollect it was something like "the select method failed in the range class" or something along these lines.

[si_the_geek]

I recognise that error

I've just checked, and it is solved by adding the xlWS.Activate line!

You might need to activate the workbook too, in which case this would do it:

...
   Set xlWS = xlBook.Worksheets(1)
 
   xlBook.Activate
   xlWS.Activate
   With xlWS
        .Rows("2:2").Insert Shift:=xlDown
...

[krtxmrtz]

Originally posted by si_the_geek
I recognise that error

I've just checked, and it is solved by adding the xlWS.Activate line!

Now this is becoming ever uncannier.

First, I think the error kept coming out because of a stupid mistake I'd made:

I had wrongly written the line
.Rows("2:2").Insert Shift:=xlDown
as:
.Rows("2:2").Select.Insert Shift:=xlDown

As soon as I corrected this no error was produced when running the code.

YET Excel wouldn't go away from task manager!!!

Thereafter I added the
xlBook.Activate
xlWS.Activate
lines but things were pretty much the same...

I think I'm going to leave this for tomorrow and maybe I'll have to check all the subroutine adding lines at a time.

[krtxmrtz]

I may have finally found the source of trouble.

I had code like this:

With xlws
        .Rows("2:2").Insert Shift:=xlDown
        .Cells(2, 1).Value = Text1.Text
        .Cells(2, 2).Value = Text2.Text
        .Cells(2, 3).Value = Text3.Text
        ...
End With

There were so many lines that a typo had gone unnoticed:

Cells(2,34).Value = Combo4.List(Combo4.ListIndex)

without the dot at the beginning...

After I placed that dot Excel was definitely killed at the end of the subroutine. Now, I've tested this under XP at home, but I was having the trouble with 98 at the office. I'll test it tomorrow.

Strangely enough no error was reported when running the code.
.

[krtxmrtz]

It works under Win98 as well.

[si_the_geek]

Ah, I didnt realise there was more code! It's an easy mistake, and strangely it doesnt always give an error (even though it should).

I'm glad you got it working

[krtxmrtz]

Originally posted by si_the_geek
I'm glad you got it working

I hope this may help other people who run into a similar headache.
Thanks B2U !

[krtxmrtz]

Originally posted by si_the_geek
I'm glad you got it working

I hope this may help other people who run into a similar headache.
Thanks B2U !