[RESOLVED] Problem with VB6 and Excel

[Bahy]

Hi all!
What i am trying to do is to insert some columns on the left side of the D column. But i was stuck in the error number 1004. And i really don't know how to debug.
My codes are below

  Set xlsApp = New Excel.Application
        Set xlsWkb = xlsApp.Workbooks.Open(filename)
        Set xlsSht = xlsWkb.Sheets(1)
        
        tenTemp = xlsSht.Name
        
        With xlsSht
            .Rows("1:5").Delete
            .Columns("D:e").Select
            .Columns.Insert Shift:=xlToRight
end with

The error was flagged right on the line of the Insert method.
If anyone have done this before, please tell me how to debug this.

Thank you so much.

[zeezee]

try

Selection.Insert Shift:=xlToRight

intead of .Columns.Insert Shift:=xlToRight

or

.Columns("D:e").Insert Shift:=xlToRight[/B]

[Bahy]

Great! it works man. Thanks so much!

[zeezee]

You are Welcome.
IF this is resolved, Please mark the Thread as Resolved.

[si_the_geek]

Nooo!!!!

Don't ever use Selection or ActiveAnything from your VB program (in fact, avoid them everywhere!), for a couple of major reasons:

• technically they don't actually exist in VB (unless you specify the parent object). Using them without a parent will appear to work - but you are very likely to get errors, such as your routine having "random" errors if you run it twice, and/or an extra hidden copy of Excel will opened (and will stay open when you exit your program).
• The Selection (as well as what is Active) can change at any moment, because of what the user or another program is doing - you cannot be certain that you are working with the correct item.

It is easy to remove the use of Selection, by removing the .Select and Selection as underlined here:

            .Columns("D:e").Select
            Selection.Insert Shift:=xlToRight

..which becomes:

            .Columns("D:e").Insert Shift:=xlToRight

[zeezee]

Thanks Si, I didnt know this. Actually what I did was record a macro and see the code.
So in your way ,can we always remove active and selection ?

[si_the_geek]

Recording a macro is a very good way of finding the code - but you need to/should 'correct' a few things (even within Excel itself!).

There is a general explanation of how to do that in the Macros section of my Excel Tutorial (link in my signature), but what I posted above has always worked for .Select/Selection (there may be exceptions, but I haven't found one yet).

As for removing ActiveSheet etc, that can be a bit more awkward.. but generally you would simply replace them with a specific object (such as xlsSht in post #1 above)

[zeezee]

Thanks again, Thats very useful

[Bahy]

That helps a lot Mr. Thanks so much.