Topic: Excel fails to terminate after successful vlax-release-object

[Keith™]

As the title says it all, that seems to be the problem I am having .. perhaps someone can tell me what is going on that I am obviously missing.

The code is relatively simple .. write data to a range in the excel file .. everything works great and the application saves and terminates properly, except when I invoke the 'Sort method ...

offending code below

Code: [Select]

;;; Copyright 2009 KBlackie
;;; Usage (sortsheet worksheet method)
;;; worksheet = VLA-OBJECT for an Excel worksheet
;;; method = 12 or 15 (Excel sort method requires 12 parameters for '95/97 and '00, but 15 parameters +'02)
(defun sortsheet(sheet method / rng r1 r2 r3)
  (setq rng (vlax-get-property sheet "UsedRange")
        r1(vlax-get-property sheet "Range" "A1")
        r2(vlax-get-property sheet "Range" "E1")
        r3(vlax-get-property sheet "Range" "G1")
  )
  (if (= method 12)
    (progn
       (vlax-invoke-method rng "Sort" r1 1;Key1 Order1
                                      r2 nil 1 ;Key2 Type Order2
                                      r3 1 ;Key3 Order3
                                      1 ;Header
                                      1 ;CustomOrder
                                      :vlax-false ;Match Case
                                      1 ;Orientation
                                      2 ;SortMethod
       )
    )
    (vlax-invoke-method rng "Sort" r1 1;Key1 Order1
                                   r2 nil 1 ;Key2 Type Order2
                                   r3 1 ;Key3 Order3
                                   1 ;Header
                                   1 ;CustomOrder
                                   :vlax-false ;Match Case
                                   1 ;Orientation
                                   2 ;SortMethod
                                   1 ;DataOption1
                                   1 ;DataOption2
                                   1 ;DataOption3
    )
  )
  (mapcar
    (function
      (lambda (x)
(vl-catch-all-apply
  (function
    (lambda()
      (if (not (vlax-object-released-p x))
(vlax-release-object x)
      )
    )
  )
)
      )
    )
    (list r3 r2 r1 rng)
  )
  (gc)(gc)
)

Commenting out the sort method allows Excel to close, so it likely isn't the range objects causing the dilemna. It has something to do with the sort method, I am almost certain of it, but it fails me on how to fix it ...

[CAB]

Any help HERE

[Keith™]

no help ... the problem only show up IF I use the sort method .. although I did try the various items mentioned and the program remained in memory.

Closed application via (vlax-invoke-method xlapp 'Quit) then released all vla-objects .. still not closed as evidenced by task manager, then I closed the drawing and opened another, still not closed, then closed AutoCAD, still not closed ... however, during this whole process, if I remove the sort method from the mix, it closes just fine.

Interesting caveat though, closing the application manually seems to work fine. I suppose it is a small concession to require the user to actually close excel when the info is written out.

[Lee Mac]

Interesting caveat though, closing the application manually seems to work fine. I suppose it is a small concession to require the user to actually close excel when the info is written out.

Thats what I normally do - can't do everything for 'em

[Keith™]

yeah, but it is nice to be transparent .. we'll see how it turns out ...

I would sort the data prior to writing it, but unfortunately, sorting a 3 lists of 1000 lists using 3 different criteria would be a bit time consuming .. as it is, it already takes a full minute to format the sheet, write out the data, format it, fill in the missing items (based on ascending sort order) and save it.

[kevinpo]

I'm trying to sort my excel file by the 'B' column and I have the first two rows as the header.
How would I sort this?

here is what I have so far using some of your code:

(setq rng (vlax-get-property csht "UsedRange"))
    (setq r1 (vlax-get-property csht "Range" "B3"))   ;these are what I'm not sure what to set
    (setq r2 (vlax-get-property csht "Range" "DA3"))  ; ""
    (setq r3 (vlax-get-property csht "Range" "DA3"))  ; ""
    (vlax-invoke-method rng "Sort" r1 1
                                   r2 nil 1
                                   r3 1
                                   2
                                   1
                                   :vlax-false
                                   1
                                   2
                                   1
                                   1
                                   1
        )

This puts the header at the bottom of the sheet.

[Keith™]

I'm trying to sort my excel file by the 'B' column and I have the first two rows as the header.
How would I sort this?

here is what I have so far using some of your code:

(setq rng (vlax-get-property csht "UsedRange"))
    (setq r1 (vlax-get-property csht "Range" "B3"))   ;these are what I'm not sure what to set
    (setq r2 (vlax-get-property csht "Range" "DA3"))  ; ""
    (setq r3 (vlax-get-property csht "Range" "DA3"))  ; ""
    (vlax-invoke-method rng "Sort" r1 1
                                   r2 nil 1
                                   r3 1
                                   2
                                   1
                                   :vlax-false
                                   1
                                   2
                                   1
                                   1
                                   1
        )

This puts the header at the bottom of the sheet.

You can't use the UsedRange property and do this because the Sort method by default allows that you either have a header or you don't, you can't specify that the header is 2 row high. You would have to set the range (RNG in the code) to include your whole worksheet, minus the header rows. Then invoke the sort method without using the option for a header row.

For the ranges where you aren't sorting by, simply pass nil

i.e.

Code: [Select]

(vlax-invoke-method rng "Sort" r1 1
                                   nil nil 1
                                   nil 1
                                   2
                                   0
                                   :vlax-false
                                   1
                                   2
                                   1
                                   1
                                   1
        )


[kevinpo]

How would I set the range then?
Is there somewhere I can look to reference these functions?

Kevin

[Keith™]

google is about the best way to get the lisp equivalent, but you could reference the VBA stuff in excel to get a little bit of understanding. Whenever I am working with an unfamiliar vlisp component, I normally write the code in VBA first then write it in vlisp using the vla objects. It takes some work, but learnign how to do it will save you lots of headaches.

[Lee Mac]

I agree with Keith here - I have done a little work with Excel through VL, and the VBA help in Excel is a good reference.

Also, worth checking out GetExcel.lsp by Terry Miller.