LISP write to Sheet1 in Excel

[DavidGraham]

I'm trying to write data to Sheet1 in Excel using a LISP routine.

 

I found the following code to write to Sheet2:

(defun pl:excel-ac ()
  (setq OEX (vlax-get-or-create-object "Excel.Application")) ;_Object Excell
  (setq OWRB (vlax-get-property OEX 'Workbooks)) ;_Object Workbook
  (setq AWB (vlax-invoke-method OWRB 'Add)) ;_Active Workbook
  (setq ASH (vlax-get-property AWB "Worksheets")) ;_Active WorkSheet
  (setq MySheet (vlax-invoke-method ASH 'Add)) ;_Sheet

)

 I have also found the following code to name a sheet as 'NewSheet'

(vl-load-com)
  (setq excel-app (vlax-create-object "excel.application"))
  (vlax-put-property excel-app 'Visible :vlax-true)
  (setq wb-collection (vlax-get excel-app "workbooks"))
  (setq wb (vlax-invoke-method wb-collection 'Add))
  (setq	sht (vlax-invoke-method
	      (vlax-get-property excel-app 'Worksheets)
	      'Add
	    )
  )
  (vlax-put-property sht 'Name "NewSheet")

How do I write into Sheet1?

[rkmcswain]

Check out this lisp code. It probably contains everything you need.

 

 

[Grrr]

 (setq ASH (vlax-get-property AWB "Worksheets")) ;_Active WorkSheet

The above code comment is false, because you actually obtain the Worksheets collection and not the active worksheet.

 

So instead of including a new Sheet object to the collection....

(setq MySheet (vlax-invoke-method ASH 'Add)) ;_Sheet

...You can try accessing the first sheet (Sheet1) via the 'Item' property (which is 1-based) :

(vlax-get-property ASH 'Item 1)

Or the item property/method can accept the object's name property as argument (for each object, which is inside of the collection). So:

(vlax-get-property ASH 'Item "Sheet1")

Usually Item is known as a method, but here (in MS Excel) its used as a property.

[DavidGraham]

Thanks, I just needed to change the code to get the 1st sheet (Sheet1) then rename it to "Spool".

(setq OEX (vlax-get-or-create-object "Excel.Application"))
					; Object Excel
  (setq OWRB (vlax-get-property OEX 'Workbooks)) ; Object Workbook
  (setq AWB (vlax-invoke-method OWRB 'Add)) ; Active Workbook
  (setq ASH (vlax-get-property AWB "Worksheets")) ; Active WorkSheet
  (setq MySheet (vlax-get-property ASH 'Item 1)) ; get 1st sheet (Sheet 1)
  (vlax-put-property MySheet 'Name "Spool") ; Sheet - Spool