Excel Sort behaving funnie

[JenSGT]

Developing a form with filter parameters which will generate a excel spreadsheet when click on the Excel button. At the same time, the form has capability to decide the sort for 2 levels which is then pass to excel using VBA.

The sort VBA code works fine whenever I opened the form for the first time and click on the Excel button. Subsequent clicks of the Excel button will render the sort to not work.

I have tested on early binding, and late binding. Still gives the same behavior.

Why does VBA allow the sort code to work the first time? and subsequent times, the code can execute but does not display the result I want?

Here is my sort code:

Selection.Sort Key 1:=Range(strLevel1Sort), Order1:= xlAscending, Key 2:=Range(strLevel2Sort), Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, Matchcase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal
Active Window.SmallScroll Down:= -6

TIA..

 

[boblarson]

You are using code which needs to be attached to an object (Selection, ActiveWindow) and because you don't, Access opens up another Excel instance, from which you can't reference, and keeps it open until you close Access or kill it from the Task Manager. Because it has already opened this instance, it can't do the next one and therefore fails.

What you need to do is to reference your code correctly. For example, if you have an Excel Application object (let's say named objXL) then you would go

objXL.Selection...etc.

objXL.ActiveWindow...etc.

that way when you close and set your objects to nothing, they are really destroyed and therefore can work again when you run the code again.

 

[JenSGT]

Your advise works.

Thanks, Bob.