[RESOLVED] Problem with VB6 and Excel

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

Code:

---

  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.

try

Code:

---

Selection.Insert Shift:=xlToRight

---

intead of .Columns.Insert Shift:=xlToRight

or

Code:

---

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

---

:wave:

Great! it works man. Thanks so much!

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

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:

Code:

---

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

---

..which becomes:

Code:

---

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

---

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

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)

Thanks again, Thats very useful
:wave:

That helps a lot Mr. Thanks so much.