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
(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.