I have a Lisp code from a community member that compiles different pipe dxfs into 1 single file. It also inserts the pipes drawing number and pipe number as it is compiling. The data is extracted from an excel spread sheet and is cross referenced with the dxfs file name (also the drawing number). This Lisp only works if the data in the excel starts at Column A row 2. How can I change the code for it work even if the data starts at column B row 26? Thank you. Please see the code and excel below.
(Defun c:MakeThisHAppen ( / _Entnext ExtractFromXls el moveAll fileName XL-App Workbooks exData f textObj moveAll)
(defun _Entnext ( e )
(if (setq e (entnext e)) (cons e (_Entnext e)))
)
(defun ExtractFromXls (st app col / ncol val dataCollection)
(repeat col
(setq ncol (cons col ncol))
(setq col (1- col))
)
(while
(vl-every 'eval
(setq val
(mapcar '(lambda (i / cv)
(if (Setq cv (vlax-get-property (vlax-get (vlax-get app "ActiveSheet") 'Cells) 'Item st i))
(vlax-variant-value
(vlax-get-property
(vlax-variant-value cv)
'Value
)
)
)
) ncol)
)
)
(setq dataCollection (cons (mapcar 'vl-princ-to-string val) dataCollection))
(setq st (1+ st))
)
dataCollection
)
(if (and
(setq Directory (acet-ui-pickdir
"Select Project Folder" (if Directory Directory (getvar 'dwgprefix))))
(setq moveAll (ssadd)
dxfFiles (vl-directory-files directory "*.dxf" 1))
(setq fileName (getfiled "Excel Spreadsheet File"
(if fileName fileName (getvar 'dwgprefix))"XLSX;XLS" 16 ))
(setq XL-App (vlax-get-or-create-object "Excel.Application"))
(Setq Workbooks (vlax-invoke-method (vlax-get-property XL-App 'WorkBooks)
'Open fileName ))
(setq exData (extractfromxls 1 XL-App 2))
)
(progn
(foreach fname dxfFiles
(if (Setq f (Assoc (strcase (vl-filename-base fname)) exData))
(progn
(setq el (entlast))
(command "_DXFIN" (strcat directory "\\" fname) '(0.0 0.0 0.0))
(if (null el)(and (setq el (entnext)) (ssadd el moveAll)))
(mapcar '(lambda ( x ) (ssadd x moveAll)) (_Entnext el))
(foreach strdata (list
'((0.0 0.0 0.0) 12 "Proj-123")
(list '(0.0 -30.00 0.0) 12
(strcat "Dwg no: " (vl-filename-base fname)
)
)
(list '(0.0 -45.0 0.0) 12 (strcat "Pipe no: " (Cadr f))
)
)
(setq textObj (entmakex (list (cons 0 "TEXT")
(cons 10 (car strdata))
(cons 40 (cadr strdata))
(cons 1 (caddr strdata))
)
)
)
(ssadd textObj moveAll))
(command "_move" moveAll "" "_non" "0,0,0" "0,500,0")
)
(princ (Strcat "\n" (vl-filename-base fname) " Not found on source list"))
)
)
(vlax-invoke-method Workbooks 'close fileName )
(vlax-release-object XL-App)
(vlax-release-object Workbooks)
)
(Vl-some '(lambda (l)
(if (null (eval (Car l)))(print (cadr l))))
'((Directory "No folder selected/Cancelled by user")
(dxfFiles "No DXF files found on selected folder")
(fileName "No xls:x;sx file found on selected folder / Cancelled by user")
(XL-App "Excel application not found")
)
)
)
(princ)
)
