VLisp and Excel: Making Certain Sheet Active

[freedomonek]

Hello,
I need a little help with a small protion of a routine.
I have layer info in an Excel file by several Sheets (labeled by discipline). ie: Architectural, Structural, etc..
Within the Code I first ask for user's input to select discipline. If the user selects "M" for Mechanical, the mechanical sheet would becomes active to gather data.

The Routine works with the with one active sheet in Excel, but the problem is that I can't find the code to make a specific sheet active.

Would you be able to help me.

Thanks!
Kelley

(defun c:test ()
(initget 1 "Architectural Civil Demolition Electrical General Hvac Instrumentation Mechanical Plumbing Structural")
(setq disciplineTrigger (getkword "Select Discipline's Layers to Load: Architectural/Civil/Demolition/Electrical/General\n/Hvac/Instrumentation/Mechanical/Plumbing/Structural: "))
	(cond 
      ((= disciplineTrigger "Architectural")(prompt (strcat "\nDiscipline Selected: " disciplineTrigger)))
	  ((= disciplineTrigger "Civil")(prompt (strcat "\nDiscipline Selected: " disciplineTrigger)))
	  ((= disciplineTrigger "Demolition")(prompt (strcat "\nDiscipline Selected: " disciplineTrigger)))
	  ((= disciplineTrigger "Electrical")(prompt (strcat "\nDiscipline Selected: " disciplineTrigger)))
	  ((= disciplineTrigger "General")(prompt (strcat "\nDiscipline Selected: " disciplineTrigger)))
	  ((= disciplineTrigger "Hvac")(prompt (strcat "\nDiscipline Selected: " disciplineTrigger)))
	  ((= disciplineTrigger "Instrumentation")(prompt (strcat "\nDiscipline Selected: " disciplineTrigger)))
	  ((= disciplineTrigger "Mechanical")(prompt (strcat "\nDiscipline Selected: " disciplineTrigger)))
	  ((= disciplineTrigger "Plumbing")(prompt (strcat "\nDiscipline Selected: " disciplineTrigger)))
	  ((= disciplineTrigger "Structural")(prompt (strcat "\nDiscipline Selected: " disciplineTrigger)))
	)


;;;load all the VL commands, then the set the library to Excel 2007 for XP
  (vl-load-com)
  (vl-arx-import)
  (setq tlbfile (findfile "C:\\Program Files\\Microsoft Office\\Office12\\Excel.exe"))
  (setq tlbfile2 (findfile "C:\\Program Files\\Microsoft Office\\Office12\\XL5EN32.OLB"))
  
  (vlax-import-type-library :tlb-filename tlbfile :methods-prefix "msxl-" :properties-prefix "msxl-" :constants-prefix "msxl-") ;Import Libraries
  (vlax-import-type-library :tlb-filename tlbfile :methods-prefix "vlxls-" :properties-prefix "vlxls-" :constants-prefix "vlxls-") ;Import Libraries
   (vlax-import-type-library :tlb-filename tlbfile :methods-prefix "JXCL-" :properties-prefix "JXCL-" :constants-prefix "JXCL-") ;Import Libraries
  
  (vlax-import-type-library :tlb-filename tlbfile2 :methods-prefix "msxl-" :properties-prefix "msxl-" :constants-prefix "msxl-") ;Import Libraries
  (vlax-import-type-library :tlb-filename tlbfile2 :methods-prefix "vlxls-" :properties-prefix "vlxls-" :constants-prefix "vlxls-") ;Import Libraries
	

  ;;;Access Layers in current drawing
  (or *acad* (setq *acad* (vlax-get-acad-object)))
  (or *doc* (setq *doc* (vla-get-activedocument *acad*)))
  (setq layers (vla-get-layers *doc*))
  
;;;Open the drawaing issue sheet and find the current worksheet
(setq xfile "C:\\WORK\\Cadd Spec Customization\\layerS22.xlsx")
  (cond	
    ((setq fn (findfile xfile))
      (cond
        ((setq appsession (vlax-get-or-create-object "Excel.Application"))
          (vlax-invoke-method (vlax-get-property appsession 'workbooks) 'open fn)
          (vla-put-visible appsession 0)
        )
      )
    )
    (t (alert (strcat "nCannot locate source file: " xfile)))
  )

(setq xlapp appsession) ;active session


;Excel Sheets
 (setq Rtn nil)
	(vlax-for SH (vlax-get-property Xlapp "sheets") 
	(setq Rtn (cons (vlax-get-property sh "Name") Rtn)) )
(reverse Rtn)
(prompt "\n Sheets in file are: \n")
(princ Rtn)(terpri)

;shows sheet active
(prompt "\n Active sheet is: \n")
   (setq shfound (vlax-get-property (msxl-get-ActiveSheet Xlapp) 'name))
(princ shfound)(terpri)



(setq mySht (vlax-get-property(vlax-invoke-method Xlapp "Activate")'name))

  (setq rng (msxl-get-activesheet xlapp))  			;get active sheet

  )

Attached is the sample Excel File.

[kozmosovia]

Code from VLXLS Project, more can be found at http://www.atablex.com/htmls/vlxls-functions.htm

(Defun vlxls-sheet-put-active (XLApp Name / sh)
  (if (null (vlxls-sheet-add xlapp name))
    (vlax-for sh (vlax-get-property XLApp "sheets")
      (if (= (vlax-get-property sh "Name") Name)
	(vlax-invoke-method sh "Activate")
      )
    )
  )
  (equal (vlxls-sheet-get-active xlapp) name)
)

[freedomonek]

Thanks kozmosovia for your code and suggestion.
I really appreciate it.

I tried your subroutine earlier, where i took out the "vlxls-sheet-add", since I just what to make active a predefined sheet, not add one if not found.

I would get an error "no function definition: VLXLS-... varible setting rejected" any time the program reads the subroutine; otherwise it would just say "bad argument Type: VLA-object nil;...".

I'm not sure if I've loaded the VLXLS- library properly, or if I'm using the correct syntax to read the vlxls-sheet-put-active subroutine.

Here's the code with the subroutine:

 (Defun vlxls-sheet-put-active (XLApp Name / sht)

  ;(if (null (vlxls-sheet-add xlapp name))
    (vlax-for sht (vlax-get-property XLApp "sheets")
	  (if (= (vlax-get-property sht "name") Name) 
	  (progn
	  
	  (vlax-invoke-method sht "Activate") )
	  )
	   
    ;)
  )
  (prompt "made it here 04 \n")
  ;(equal (vlxls-sheet-get-active xlapp) name)
)
(defun c:test ()
(initget 1 "Architectural Civil Demolition Electrical General Hvac Instrumentation Mechanical Plumbing Structural")
(setq disciplineTrigger (getkword "Select Discipline's Layers to Load: Architectural/Civil/Demolition/Electrical/General\n/Hvac/Instrumentation/Mechanical/Plumbing/Structural: "))
	(cond 
      ((= disciplineTrigger "Architectural")(prompt (strcat "\nDiscipline Selected: " disciplineTrigger)))
	  ((= disciplineTrigger "Civil")(prompt (strcat "\nDiscipline Selected: " disciplineTrigger)))
	  ((= disciplineTrigger "Demolition")(prompt (strcat "\nDiscipline Selected: " disciplineTrigger)))
	  ((= disciplineTrigger "Electrical")(prompt (strcat "\nDiscipline Selected: " disciplineTrigger)))
	  ((= disciplineTrigger "General")(prompt (strcat "\nDiscipline Selected: " disciplineTrigger)))
	  ((= disciplineTrigger "Hvac")(prompt (strcat "\nDiscipline Selected: " disciplineTrigger)))
	  ((= disciplineTrigger "Instrumentation")(prompt (strcat "\nDiscipline Selected: " disciplineTrigger)))
	  ((= disciplineTrigger "Mechanical")(prompt (strcat "\nDiscipline Selected: " disciplineTrigger)))
	  ((= disciplineTrigger "Plumbing")(prompt (strcat "\nDiscipline Selected: " disciplineTrigger)))
	  ((= disciplineTrigger "Structural")(prompt (strcat "\nDiscipline Selected: " disciplineTrigger)))
	)


;;;load all the VL commands, then the set the library to Excel 2007 for XP
  (vl-load-com)
  (vl-arx-import)
  (setq tlbfile (findfile "C:\\Program Files\\Microsoft Office\\Office12\\Excel.exe"))
  (setq tlbfile2 (findfile "C:\\Program Files\\Microsoft Office\\Office12\\XL5EN32.OLB"))
  
  (vlax-import-type-library :tlb-filename tlbfile :methods-prefix "msxl-" :properties-prefix "msxl-" :constants-prefix "msxl-") ;Import Libraries
  (vlax-import-type-library :tlb-filename tlbfile :methods-prefix "vlxls-" :properties-prefix "vlxls-" :constants-prefix "vlxls-") ;Import Libraries
   (vlax-import-type-library :tlb-filename tlbfile :methods-prefix "JXCL-" :properties-prefix "JXCL-" :constants-prefix "JXCL-") ;Import Libraries
  
  (vlax-import-type-library :tlb-filename tlbfile2 :methods-prefix "msxl-" :properties-prefix "msxl-" :constants-prefix "msxl-") ;Import Libraries
  (vlax-import-type-library :tlb-filename tlbfile2 :methods-prefix "vlxls-" :properties-prefix "vlxls-" :constants-prefix "vlxls-") ;Import Libraries
	

  ;;;Access Layers in current drawing
  (or *acad* (setq *acad* (vlax-get-acad-object)))
  (or *doc* (setq *doc* (vla-get-activedocument *acad*)))
  (setq layers (vla-get-layers *doc*))
  
;;;Open the drawaing issue sheet and find the current worksheet
  (setq xfile "F:\\PEARSONK\\Cadd Spec Customization\\layerS22.xls")
   ;;(setq xfile "C:\\WORK\\Cadd Spec Customization\\layerS22.xlsx")
  (cond	
    ((setq fn (findfile xfile))
      (cond
        ((setq appsession (vlax-get-or-create-object "Excel.Application"))
          (vlax-invoke-method (vlax-get-property appsession 'workbooks) 'open fn)
          (vla-put-visible appsession 0)
        )
      )
    )
    (t (alert (strcat "nCannot locate source file: " xfile)))
  )

(setq xlapp appsession) ;active session


;Excel Sheets
 (setq Rtn nil)
	(vlax-for SH (vlax-get-property Xlapp "sheets") 
	(setq Rtn (cons (vlax-get-property sh "Name") Rtn)) )
(reverse Rtn)
(prompt "\n Sheets in file are: \n")
(princ Rtn)(terpri)

;make sheet active
(prompt "\n Active sheet is: \n")
   (setq shfound (vlax-get-property (msxl-get-ActiveSheet Xlapp) 'name))
(princ shfound)(terpri)


(vlxls-sheet-put-active *xlapp* disciplineTrigger)          ;VLXLS-

;;;(setq mySht (vlax-get-property(vlax-invoke-method Xlapp "Activate")'name))

  (setq rng (msxl-get-activesheet xlapp))  		;get active sheet 

)

Thanks again for your direction.

Kelley

[kozmosovia]

VLXLS is general functions, so will go to see if the sheet name is already exist, if not, the function will automatically create it. you can just follow the codes to add the additional VLXLS-* function from my VLXLS Project and add all relative functions to ur programs.

Of coz in ur case, you have confirmed that the sheet name is already exist, so you do not need to load the whole VLXLS project, just simply modify and diable the certain checking codes in "vlxls-sheet-put-active" as what u did. If the sheet name is not exist, the function will do nothing.

(Defun vlxls-sheet-put-active (XLApp Name / sh)
    (vlax-for sh (vlax-get-property XLApp "sheets")
      (if (= (vlax-get-property sh "Name") Name)
	(vlax-invoke-method sh "Activate")
      )
    )
)

[freedomonek]

Thanks again Kozmosovia

I needed to load your functions below :

• vlxls-app-open
• vlxls-app-quit

Also, i needed to set the XLS file to the *xlapp* variable.

It's working wonderful as expected.
I'm so Happy!
I'll be sure to add your disclaimer and copyright.
Thanks very much for your intellect!

Kelley

[BlackBox]

Code from VLXLS Project, more can be found at http://www.atablex.com/htmls/vlxls-functions.htm

I know it's an old thread, but this is a good link.