Topic: First time working with Excel

[T.Willey]

I have writen a routine to grab all the cell information from a workbook from an existing excel file.  This is just a learning experience.  I have read post about closing excel, and people having problems with it.  I thought I got all the information right because when the routine is finished excel is not in my task manager anymore, but if it errors it is still there.  I close it the same way with the error function, and the main funtion.  I don't understand why this is happening.  If you see anything else that I did wrong, or should maybe have done another way, please post and explain, as this is a learning routine for me.  It is not as complete as I want it, but I wanted to figure out the closing aspect of it first.

Thanks in advance.
Tim

Code: [Select]

(defun c:GrabAlleXcelCells (/ XcelApp XcelWbCol tmpXcelObj tmpShtCol tmpList DiaRtn ShtName tmpSht ShtClmCol ShtClmArea
                              ArCnt tmpArea CurReg tmpList2 Left Top cnt ColSt tmpCol EndList MultiSafearray->List
                              *error* SingleSelect)

; Lets you select an excell file, and then a sheet from the workbook, and grabs all the cells, and sorts them with
;  location codes. Example returned (("B" 2 "Slot 1")("B" 3 "Harland Old Print No Number"))

(defun *error* (msg)
(prompt (strcat " Error -> " msg))
(vlax-invoke XcelApp 'Quit)
(vl-catch-all-apply 'vlax-release-object '(CurReg tmpArea ShtClmArea ShtClmCol tmpSht tmpShtCol tmpXcelObj XcelWbCol XcelApp))
)
;----------------------------------------------------------------------------------

(defun MultiSafearray->List (MultiArray / RtnList)
; Turns a multi safearray variant into a list of lists.

(foreach i (vlax-safearray->list (variant-value MultiArray))
 (setq RtnList
  (cons
   (mapcar '(lambda (x) (vlax-variant-value x)) i)
   RtnList
  )
 )
)
RtnList
)
;-----------------------------------------------------------------------------------
(defun SingleSelect (Listof Message Toggle / DiaLoad tmpStr tmpTog tmpList)

(setq DiaLoad (load_dialog "MyDialogs.dcl"))
(if (new_dialog "SingleSelect" DiaLOad)
 (progn
  (start_list "listbox" 3)
  (mapcar 'add_list Listof)
  (end_list)
  (if Message
   (set_tile "text1" Message)
  )
  (if (not Toggle)
   (mode_tile "toggle1" 1)
  )
  (action_tile "listbox"
   "(if (= $reason 4)
    (progn
     (setq tmpStr (get_tile \"listbox\"))
     (if Toggle
      (setq tmpTog (get_tile \"toggle1\"))
     )
     (done_dialog 1)
    )
   )"
  )     
  (action_tile "accept"
   "(progn
    (setq tmpStr (get_tile \"listbox\"))
    (if Toggle
     (setq tmpTog (get_tile \"toggle1\"))
    )
    (done_dialog 1)
   )"
  )
  (action_tile "cancel" "(done_dialog 0)")
  (if (= (start_dialog) 1)
   (progn
    (setq tmpList (read (strcat "(" tmpStr ")")))
    (if (= tmpTog "1")
     (cons T tmpList)
     tmpList
    )
   )
  )
 )
)
)
;---------------------------------------------------------------------------------

(setq XcelApp (vlax-get-or-create-object "Excel.Application"))
(setq XcelWbCol (vlax-get XcelApp 'Workbooks))
(setq tmpXcelObj
 (vlax-invoke
  XcelWbCol
  'Open
  (getfiled "Select Excel file to open." "" "xls" 4)
 )
)
(setq tmpShtCol (vlax-get tmpXcelObj 'Sheets))
(setq tmpList nil)
(vlax-for i tmpShtCol
 (setq tmpList (cons (vlax-get-property i 'Name) tmpList))
)
(setq tmpList (vl-sort tmpList '<))
(setq DiaRtn (SingleSelect tmpList "Select sheet to work off of." nil))
(setq ShtName (nth (car DiaRtn) tmpList))
(setq tmpSht (vlax-get-property tmpShtCol 'Item ShtName))
(setq ShtClmCol (vlax-get-property tmpSht 'Columns))
(setq ShtClmArea (vlax-get-property ShtClmCol 'Areas))
(setq ArCnt (vlax-get-property ShtClmArea 'Count))
(setq tmpArea (vlax-get-property ShtClmArea 'Item 1))
(setq CurReg (vlax-get-property tmpArea 'CurrentRegion))
(setq tmpList2 (MultiSafearray->List (vlax-get-property CurReg 'Value)))
(setq Left (vlax-variant-value (vlax-get-property CurReg 'Left)))
(setq Top (vlax-variant-value (vlax-get-property CurReg 'Top)))
(setq cnt 1)
(if
 (and
  (equal Top 0.0)
  (equal Left 0.0)
 )
 (setq ColSt "A")
)
(foreach i (reverse tmpList2)
 (setq tmpCol ColSt)
 (foreach ii i
  (if ii
   (setq EndList (cons (list tmpCol cnt ii) EndList))
  )
  (setq tmpCol (chr (1+ (ascii tmpCol))))
 )
 (setq cnt (1+ cnt))
)
(vlax-invoke XcelApp 'Quit)
(mapcar '(lambda (x) (vlax-release-object (eval x))) '(CurReg tmpArea ShtClmArea ShtClmCol tmpSht tmpShtCol tmpXcelObj XcelWbCol XcelApp))
(setq EndList
 (vl-sort
  EndList
  '(lambda (a b)
   (if (= (car a) (car b))
    (< (cadr a) (cadr b))
    (< (car a) (car b))
   )
  )
 )
)
)

ps. Attaching the dcl file and the lisp routine.

[Jeff_M]

Tim,
The first thing I see is that the (vlax-release-......in the error handler is NOT the same as the one in the main function......and it will throw an error that is caught by the vl-catch-all so you don't see it.
Try placing the exact same line into the error handler and it should work.

[T.Willey]

I tried it with both of these lines (one at a time), and it still leaves excel in task manager.  I'm so lost as to why.

Code: [Select]

(mapcar '(lambda (x) (vlax-release-object (eval x))) '(CurReg tmpArea ShtClmArea ShtClmCol tmpSht tmpShtCol tmpXcelObj XcelWbCol XcelApp))
;(vl-catch-all-apply '(lambda (x) (vlax-release-object (eval x))) '(CurReg tmpArea ShtClmArea ShtClmCol tmpSht tmpShtCol tmpXcelObj XcelWbCol XcelApp))


[Jeff_M]

How about a sample Excel file? I'm getting errors opening any of my test files.

I gotta surveying here in a few minutes so it may be a while before my next response......

[T.Willey]

Thanks for all/any help.  Here is the excel file I was using.  I was testing "Sheet1".  I got an error only when testing "Sheet3" and that is because it has nothing one it.

To get the file to work, just erase the ".lsp" from the end.

[Jürg Menzi]

Hi Tim

I've upload some sample functions they are maybe usefull for you:
ExcelSample.lsp

[CAB]

Tim,
FYI, you can eliminate duplicate code by changing the error routine to this

Code: [Select]

(defun *error* (msg)
  (and (/= msg "") (prompt (strcat " Error -> " msg)))
  (vlax-invoke XcelApp 'Quit)
  (vl-catch-all-apply 'vlax-release-object '(CurReg tmpArea ShtClmArea ShtClmCol tmpSht tmpShtCol tmpXcelObj XcelWbCol XcelApp))
)
Ant then call it like this as part of your normal exit.

Code: [Select]

(*error* "") ; replaces the Quit & release code
(setq EndList
  (vl-sort EndList 
    '(lambda (a b)
       (if (= (car a) (car b))
         (< (cadr a) (cadr b))
         (< (car a) (car b))
       )
     )
   )
)

[Jeff_M]

OK, I got it to work......you are calling the "multisafearray->list" on an object that can return an empty array which causes it to fail.....
Add this: (vlax-invoke tmpXcelObj 'close) to the 2 places in your code just before the call to Quit the Excel app. This worked for me to remove Excel from the running apps list......

Oh, and what CAB suggests is a good idea for any app that requires objects to be released.....

[T.Willey]

I tried it with what CAB had said, and a modified version is what worked for me.  I tried what CAB and Jeff talked about, and I couldn't get it to work, but upon testing this works when it errors, and when it does not.  Jeff with my testing I didn't need a call to close the excel object.  Is that something I should do anyway though?

Code: [Select]

(defun *error* (msg)
(if (/= msg "")
 (prompt (strcat " Error -> " msg))
)
(vlax-invoke XcelApp 'Quit)
(mapcar
 '(lambda (x) (vl-catch-all-apply 'vlax-release-object (list (eval x))))
 '(CurReg tmpArea ShtClmArea ShtClmCol tmpSht tmpShtCol tmpXcelObj XcelWbCol XcelApp)
)
)

I placed the (*error* "") just as CAB has mentioned in his post.

Thank you both.

Jurg I just noticed your post.  Don't know how I missed it.  Thanks, and I will review it now, and I know it will be a big help.