[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.
Re: Problem with VB6 and Excel
try
Code:
Selection.Insert Shift:=xlToRight
intead of .Columns.Insert Shift:=xlToRight
or
Code:
.Columns("D:e").Insert Shift:=xlToRight[/B]
:wave:
Re: Problem with VB6 and Excel
Great! it works man. Thanks so much!
Re: Problem with VB6 and Excel
You are Welcome. :)
IF this is resolved, Please mark the Thread as Resolved.
:wave:
Re: [RESOLVED] Problem with VB6 and Excel
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
Re: [RESOLVED] Problem with VB6 and Excel
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 ?
Re: [RESOLVED] Problem with VB6 and Excel
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)
Re: [RESOLVED] Problem with VB6 and Excel
Thanks again, Thats very useful
:wave:
Re: [RESOLVED] Problem with VB6 and Excel
That helps a lot Mr. Thanks so much.