Selection.Delete fails when running procedure second time

[sglab]

Hello everyone!

I have a problem that I hope you'll be able to help me with.
I'm using VB6 to create some reports in Excel. Depending on the type of report, there will be different number of columns.

So, I have

xsl as Excel.Application
wbk as Workbook
wsh as Worksheet
[/color blue]

I initialize variables, do database stuff and then drop data into Excel and call formatting procedure:


wsh.Range("A2").CopyFromRecordset ors
Call Format_XSL(wsh, repType)
[/color blue]
----------------------------------

In Format_XSL procedure:

If LCase(projType) = "native" Then
wsheet.Columns("G:K").Select [/color blue]
'MsgBox "The Selection type is " & TypeName(Selection)[/color green]
Selection.Delete Shift:=xlToLeft
End If
[/color blue]
-------------------------
There are more different statements in that procedure. I just showed one to make it quick.

After formatting is done, I save report and dispose of created objects.

Set wsh = Nothing
wbk.Close
Set wbk = Nothing
xsl.Quit
Set xsl = Nothing

MsgBox "Done"
[/color blue]
Everything works fine if I just run it once and exit the application. But if I try to do it more than once, statement:

Selection.Delete Shift:=xlToLeft

fails with error 91.

Any idea what is not right?

Thank you in advance.

 

[SkipVought]

Hi,

You are obviously performing a query and placing the recordset in a worksheet.

"Depending on the type of report, there will be different number of columns."

Rather than DELETE columns, change your SQL to SELECT only the columns you wish to display in your report. But if you must...

    If LCase(projType) = "native" Then
     wsheet.Columns("G:K").Delete Shift:=xlToLeft
    End If

Avoid using the Select and Activate methods.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[sglab]

Hi, Skip

I know I could've used different queries for different types of reports, but I've decided to create a template of report, that would contain all the columns in my single query and just delete unnecessary columns during report creation.
So, maybe that's not the best approach, but I just wanted to figure out what's wrong with using Select or Activate. And looks like I can't not use them, because I do have a lot of formatting in this Excel_Format procedure where I do need to select ranges and do stuff with them.
By the way, I tried to use a line that you dropped in your reply and again, it worked fine first time and failed at the next Select statement I use in the code. And I can't figure out why.

Thanks anyway for your response.

 

[SkipVought]

'By the way, I tried to use a line that you dropped in your reply and again, it worked fine first time and failed at the next Select statement I use in the code."

I did not use SELECT. Use the same technique that I coded for you.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

I think that the problem is that Selection refers to WHAT? Remember, you're in a VB environment, not Excel VBA, where the Selection object has IMPLIED, the Excel application object.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[sglab]

Thanks Skip,

for not giving up on me.

1. 'I did not use SELECT. Use the same technique that I coded for you.

Yeah, I got the idea. Just wanted to check if SELECT was going to work in other parts of the procedure - hence my "by the way" in previous post.
Obviously, it didn't.

2. I understand, that there's a difference as far as environment is concerned and I do understand, that SELECT is a property of Application object and returns Range object. What I don't get is why code works just one time. If there were a terrible flaw in it, it wouldn't work at all, I guess.
I re-initialize all object variables every time I click the button and check if they really hold objects, but each time - after the first - when code gets to SELECT statement -> SELECT returns 'Nothing'.

I'm sorry I've taken much of your time already. Actually, it's not a big deal to try to use your technique or just restart application for a different report type. Just wanted to understand where the problem is.

Thanks Skip and have a great weekend.

 

[SkipVought]

Depending on the circumstances, and you have not fully defined YOUR circumstances, since you ONLY posted the FIRST select and delete and then refered the the second WITHOUT posting any code, a DELETE can result in a loss of reference for a subsequent object reference. I can only surmise.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[sglab]

Hi Skip,

let me first try and re-write my code using your approach and I'll let you know if that worked out. In case it didn't, I'll post entire Format_Excel procedure.

Thnx.

Sergey.