DraftSight Lisp - Find Excel Sheet

Question

I'm trying to use Lisp to reach the cell level to manipulate the values in a sheet. I'm currently working from this code over on GitHub. However, I'm running into an issue in pulling the sheet name. I can get an object variable for the group of sheets in a workbook, but I cannot access the individual sheets. How does this code need to be written to find the correct sheet?

;;; Global Variables
(setq *ExcelApp% nil)
(setq *ExcelData@ nil)
(setq *ExcelFile$ nil)

;;; Debug Use
(defun C:ExcelTest()
    (princ "\n \n \n")
    (setq FilePath "C:\\DUP\\Temporary.xlsx")
    (vl-load-com)
    (setq NewList (GetExcel FilePath "Sheet1" "3D"))
    (setq GlobalList *DataList@)

    (terpri)
    (princ "New List : ")(princ NewList)(terpri)
    (princ "\n \nGlobal List : ")(princ GlobalList)(terpri)
);defun C:ExcelTest
;-------------------------------------------------------------------------------
; GetExcel - Stores the values from an Excel spreadsheet into *ExcelData@ list
; Arguments: 3
;   ExcelFile$ = Path and filename
;   SheetName$ = Sheet name or nil for not specified
;   MaxRange$ = Maximum cell ID range to include or nil to get the current region from cell A1
; Syntax examples:
; (GetExcel "C:\\Temp\\Temp.xls" "Sheet1" "E19") = Open C:\Temp\Temp.xls on Sheet1 and read up to cell E19
; (GetExcel "C:\\Temp\\Temp.xls" nil "XYZ123") = Open C:\Temp\Temp.xls on current sheet and read up to cell XYZ123
;-------------------------------------------------------------------------------

;;; Pulls values from Excel
(defun GetExcel (ExcelFile$ SheetName$ MaxRange$ / Column# ColumnRow@ Data@ ExcelRange^
    ExcelValue ExcelValue ExcelVariant^ MaxColumn# MaxRow# Range$ Row# workbook Worksheet)
    
    (terpri)
    (princ "GetExcel started...\n")

    ;; Verifies Excel file input
    (if (= (type ExcelFile$) 'STR)
        (if (not (findfile ExcelFile$))
            (progn
                (alert (strcat "Excel file " ExcelFile$ " not found."))
                (exit)
            );progn
        );if
        (progn 
            (alert "Excel file not specified.")
            (exit)
        );progn
    );if
    
    (princ "File verified...\n")

    ;; Garbage collection / Lisp compatability
    (gc)
    
    ;; Initializing Excel variable
    (if (not (setq *ExcelApp% (vlax-get-object "Excel.Application")))
    (progn; True - Error Catch
        (alert "Close all Excel spreadsheets to continue!")
        (vlax-release-object *ExcelApp%)(gc)
    );progn
    );if

    ;; Opening the Excel workbook
    (setq ExcelFile$ (findfile ExcelFile$))
    (setq *ExcelApp% (vlax-get-or-create-object "Excel.Application"))
    (setq workbook (vlax-invoke-method (vlax-get-property *ExcelApp% 'WorkBooks) 'Open ExcelFile$))
    
    (princ "\nWorkbook opened...\n")

    ;; Debug Print
    (princ (vlax-get-property (vlax-get-property *ExcelApp% "Names") "Parent"))
    (princ "\n \n")
    (vlax-dump-object workbook)
    (princ "\n \n")
    (vlax-dump-object (vlax-get-property workbook "Names"))
    (princ "\n \n")
    (vlax-dump-object (vlax-get-property workbook "Sheets"))
    (princ "\n \n")
    (vlax-dump-object (vlax-get-property workbook "Worksheets"))
    (princ "\n \n")
    (vlax-dump-object (vlax-get-property *ExcelApp% "Sheets"))
    (princ "\n \n")
    
    ;; Finds the worksheet in the Excel workbook
    (if (> (sslength SheetName$) 0)
    (vlax-for Worksheet (vlax-get-property workbook "Sheets")
        (princ "Worksheet : ")
        (vlax-dump-object Worksheet)
        (if (= (vlax-get-property Worksheet "Name") SheetName$)
            (vlax-invoke-method Worksheet "Activate")
        );if
    );vlax-for
    );if
    
    (princ "Specific Problem Solved\n")
);defun GetExcel

DraftSight Screen:

Answer 1

Instead of using (vlax-for Worksheet (vlax-get-property workbook "Sheets") to pull each sheet, call for the "Item" and add a number to the end of the vlax-get-property function to pull each sheet from the list of sheets. The updated section of code is shown below.

;; Finds the worksheet in the Excel workbook
(setq Sheets (vlax-get-property Workbook "Sheets"))
(setq shCount (vlax-get-property Sheets "Count"))
(setq iInt1 0)
(if (> (sslength SheetName$) 0)
    ;(vlax-for Worksheet (vlax-get-property workbook "Sheets")
    (while (>= shCount (setq iInt1 (1+ iInt1)))
        (setq Worksheet (vlax-get-property Sheets "Item" iInt1))
        (if (= (vlax-get-property Worksheet "Name") SheetName$)
            (vlax-invoke-method Worksheet "Activate"));if
    );while
    ;);vlax-for
);if