lisp does not add "," to list until it is reloaded

[myoujou cain]

I have a lisp that reads a bunch of cells from excel, adds a "," to the end of each item, then places the item in a list. when I first start autocad it creats the list but does not add the ",". I reload the lisp, then it adds the commas. and I cant figure out why. the lisp code is below:

 

;;                     GET CELLS FROM EXCEL            ;;
  (defun getCells(fileName sheetName cellName / myXL myBook mySheet myRange cellValue)
    (setq myXL(vlax-get-or-create-object "Excel.Application"))
;;;    (vla-put-visible myXL :vlax-false)
    (vlax-put-property myXL 'DisplayAlerts :vlax-false)
    (setq myBook (vl-catch-all-apply 'vla-open (list (vlax-get-property myXL "WorkBooks") fileName)))
    (setq mySheet (vl-catch-all-apply 'vlax-get-property (list (vlax-get-property myBook "Sheets") "Item" sheetName)))
    (vlax-invoke-method mySheet "Activate")
    (setq myRange (vlax-get-property (vlax-get-property mySheet 'Cells) "Range" cellName))
    (setq cellValue(vlax-variant-value (vlax-get-property myRange 'Value2)))
;;;    (vl-catch-all-apply 'vlax-invoke-method (list myBook "Close"))
;;;    (vl-catch-all-apply 'vlax-invoke-method (list myXL "Quit"))
    (if (not (vlax-object-released-p myRange))(progn(vlax-release-object myRange)(setq myRange nil)))
    (if (not (vlax-object-released-p mySheet))(progn(vlax-release-object mySheet)(setq mySheet nil)))
    (if (not (vlax-object-released-p myBook))(progn(vlax-release-object myBook)(setq myBook nil)))
    (if (not (vlax-object-released-p myXL))(progn(vlax-release-object myXL)(setq myXL nil)))   
    (if(= 'safearray (type cellValue))
      (progn
        (setq tempCellValue(vlax-safearray->list cellValue))
        (setq cellValue(list))
        (if(= (length tempCellValue) 1)
          (progn
            (foreach a tempCellValue
              (if(= (type a) 'LIST)
                (progn
                  (foreach b a
                    (if(= (type b) 'LIST)
                      (setq cellValue(append cellValue (list (vlax-variant-value (car b)))))
                      (setq cellValue(append cellValue (list (vlax-variant-value b))))
                    )
                  )
                )
                (setq cellValue(append cellValue (list (vlax-variant-value a))))
              )
            )
          )
          (progn
            (foreach a tempCellValue
              (setq tmpList(list))
              (foreach b a
  (SETQ VALUE (TYPE(VLAX-VARIANT-VALUE B)))
                (setq tmp (STRCAT (IF(= (TYPE (vlax-variant-value b)) 'STR ) (vlax-variant-value b) (VL-STRING-RIGHT-TRIM "." (VL-STRING-RIGHT-TRIM "0" (RTOS (vlax-variant-value b)))))","))
                (setq tmpList(append tmpList (LIST tmp)))
              )
              (setq cellValue(cons tmpList cellValue))
            )
          )
        )
      )
     )
    cellValue
  )
 

[rlx]

maybe a little rusty (too much work...   )

 

; $fn = filename (string) [*.xls/xlsx] , $sht = name of sheet (string) , $rng = range (string) [i.e. "A1" or "A1:A9"]
(defun getCells ($fn $sht $rng / myXL myBook mySheet myRange cellValues get_cell_values Cell->Str lst->csv release_me)
  (vl-load-com)
  ; not really necessary to put it in separate defun just old (bad?) habbit
  (defun get_cell_values ( %range %XlsApp / ExcelRange range ExcelVariant ExcelValue etype)
    (if %XlsApp
      (progn
    ; change single cell range to extended range : "A1" -> "A1:A1"
    (if (not (wcmatch %range "*:*")) (setq range (strcat %range ":" %range))(setq range %range))
    (setq ExcelRange (vlax-get-property %XlsApp "Range" range)
          ExcelVariant (vl-catch-all-apply (function (lambda ()(vlax-get-property ExcelRange 'Value))))
          ExcelValue (vl-catch-all-apply (function (lambda ()(vlax-variant-value ExcelVariant)))))
    (if ExcelValue
      (setq etype (type ExcelValue)
        ExcelValue
         (cond
           ; format a cell depending on value type (else just use vl-princ-to-string)
           ; simple integer to string
           ((= etype 'INT) (itoa ExcelValue))
           ; real number to string , decimal / precission
           ((= etype 'REAL) (rtos ExcelValue 2 0))
           ; strip spaces
           ((= etype 'STR) (vl-string-trim " " ExcelValue))
           ; make string for every item in array
           ((= etype 'safearray)
            (mapcar
              '(lambda (s / v) (if (eq (setq v (vl-princ-to-string (vlax-variant-value s))) "nil") "Empty" v))
               (car (vlax-safearray->list excelvalue))))
           ; thou shall not pass (without a value)
           (t "Empty")
         )
      )
      ; no value -> return empty string
      (setq ExcelValue "I dunno")
    )
      )
    )
    ;'black hole' (nil) detector ... just making sure I always get a value , even if it's "Empty"
    (if (listp ExcelValue) (setq ExcelValue (subst "Empty" nil ExcelValue)))
    ExcelValue
  )

  ; %l = list of strings , (lst->csv '("a" "b" "c")) -> "a,b,c"
  (defun lst->csv (%l)(apply 'strcat (cdr (apply 'append (mapcar (function (lambda (x) (list "," x))) %l)))))

  ; clean desk policy
  (defun release_me (lst)
    (mapcar '(lambda(x)(if (and (= 'vla-object (type x))(not (vlax-object-released-p x)))(vlax-release-object x))(set (quote x) nil)) lst))

  (cond
    ((or (null $fn) (not (eq (type $fn) 'STR))) (alert "Invalid filename for excel file"))
    ((not (findfile $fn)) (alert (strcat "Unable to find :\n" $fn)))
    ((not (setq myXL (vlax-get-or-create-object "Excel.Application")))
     (alert "Unable to initialize Excel"))
    ((vl-catch-all-error-p (setq myBook (vl-catch-all-apply 'vla-open (list (vlax-get-property myXL "WorkBooks") $fn))))
     (alert "Unable to retrieve workbooks collection"))
    ((vl-catch-all-error-p (setq mySheet (vl-catch-all-apply 'vlax-get-property (list (vlax-get-property myBook "Sheets") "Item" $sht))))
     (if (= (type $sht) 'STR) (alert (strcat "Unable to find sheet : " $sht)) (alert "Sheet not found")))
    (t
     (vlax-invoke-method mySheet "Activate")
     ; (setq myRange (vlax-get-property (vlax-get-property mySheet 'Cells) "Range" $rng))
     (setq cellValues (get_cell_values $rng myXL))
     (release_me (list myRange mySheet myBook myXL))
     (if (vl-consp cellValues) (lst->csv cellValues) (princ "\nNo data"))
    )
  )
)

(defun c:t1 ( / ) (setq result (getcells "d:\\Temp\\Lisp\\getcells-test.xlsx" "DrukMetingen" "A1:Q1")))
(defun c:t2 ( / ) (setq result (getcells "d:\\Temp\\Lisp\\getcells-test.xlsx" "DrukMetingen" "A2:Q2")))

; (c:t1)
; (c:t2)

 

[myoujou cain]

So is this some custom code that you made to do the same thing?

[rlx]

4 minutes ago, myoujou cain said:

So is this some custom code that you made to do the same thing?

 

I have a few routines that do something with excel , not too many, and I always try to make parts generic so I can recycle them more easily. So sometimes a code can be made shorter or simpler but I have not always the time to write a new code. In this case I also did some cut copy & paste and tried to leave parts of your code intact. I also tried to built in as much error checking as I could so you can get a relevant error message when debugging. But there are ton's of lisp routines out there that can work with excel. Many if not most far more better than mine.

[myoujou cain]

I know, what I have is kind of a Frankenstein I made out of a few examples. I will give your code a try. what bugs me the most is I don't understand why or how the code can do one thing when auto loaded and something different when manually loaded. do you have any insight on what could cause that?

 

thanks,

[rlx]

I'm not sure but believe it error'd on  (vlax-put-property myXL 'DisplayAlerts :vlax-false).

With VL-commands sometimes the order of your commands must be done in a specific order and also , not in your case , sometimes it's better to break up a command sequence and first save the result in a variable before proceeding. Also useful during debugging.

 

You can for example get the active document in one command call like

 (setq actDoc (vla-get-activedocument (vlax-get-acad-object))) 

 

usually this is no problem but some commands can give problems when you nest them too much.

When writing your app , use lots of steps and when it works you can try to shorten your code.

[myoujou cain]

I'm not real good with vla commands yet, is there a way to improve this line (break it up or change its order?):

 

(setq tmp (STRCAT (IF(= (TYPE (vlax-variant-value b)) 'STR ) (vlax-variant-value b) (VL-STRING-RIGHT-TRIM "." (VL-STRING-RIGHT-TRIM "0" (RTOS (vlax-variant-value b)))))","))

[myoujou cain]

your code is interesting because it seems to not actualy open the excel document. but at the very end I get a "bad argument type: consp" error. and it only returns the first row in a weird format princ returns: a,b,c,d"a,b,c,d"

 

it should be:

a,b,c,d

f,g,h,i

 

etc.

[rlx]

Have posted while back this routine , RLXls.lsp

Its been a while back but if I remember correctly this one can select a range and paste it as a table. But of course you can bypass the table making part and use the list in another way.

 

when you have this error you talked about , is excel already running at that time or maybe you have another excel file loaded at the time?

 

when you retrieve the value maybe first put it in a variable so you can set a breakpoint there and inspect what is happening (setq myVal ( vlax-variant-value b)) and then check its type and do your stuff with it.

 

Well, workday is done for me , time to go home. Catch you later aligator

 

[myoujou cain]

thank you for all your help, I really appreciate it