Page 1 of 2 12 LastLast
Results 1 to 10 of 17

Thread: interacting with excel

  1. #1
    Member
    Join Date
    2009-09
    Posts
    32
    Login to Give a bone
    0

    Default interacting with excel

    Using visual lisp how can we preform following operations

    > adding row/column

    > merging/unmerging cells

    > deleting row/column

    I have searched excel object model & internet but cannot find some helpful material

    any code snipped/or useful internet link will be appreciated.

    thanks.

  2. #2
    I could stop if I wanted to
    Join Date
    2007-08
    Posts
    201
    Login to Give a bone
    0

    Default Re: interacting with excel

    You can have a look to Terry Miller's GetExcell.lsp to see how to activate a sheet in a file, then use (vlax-dump-object ...) to see properties and methods available for rows, columns, cells ...

  3. #3
    AUGI Addict fixo's Avatar
    Join Date
    2005-05
    Location
    Pietari, Venäjä
    Posts
    1,269
    Login to Give a bone
    0

    Default Re: interacting with excel

    Quote Originally Posted by mdsalman2003 View Post
    Using visual lisp how can we preform following operations

    > adding row/column

    > merging/unmerging cells

    > deleting row/column

    I have searched excel object model & internet but cannot find some helpful material

    any code snipped/or useful internet link will be appreciated.

    thanks.
    For example delete column:

    Code:
    ;;;================== delete column ====================
    (defun C:DCOL (/ Col Cols ColNum ExcelApp FilePath Sht ShtName Wbk)
     
    (vl-load-com)
     
    (setq FilePath (getfiled "Select Excel file :"
          (getvar "dwgprefix")
          "xls"
          16
       )
      )
      (setq ShtName (getstring "\nEnter sheet name : "))
     
      (setq ColNum (getint "\nEnter column number to delete : "))
     
      (setq ExcelApp (vlax-get-or-create-object "Excel.Application"))
     
      (vla-put-visible ExcelApp :vlax-true)
     
      (vlax-put-property ExcelApp "DisplayAlerts" :vlax-false)
     
      (setq Wbk (vl-catch-all-apply
           'vla-open
           (list (vlax-get-property ExcelApp "WorkBooks") FilePath)
         )
      )
      (setq Sht (vl-catch-all-apply
           'vlax-get-property
           (list (vlax-get-property Wbk "Sheets")
          "Item"
          ShtName
           )
         )
      )
      (vlax-invoke-method Sht "Activate")
     
      (setq Cols (vlax-get-property Sht "Columns")
     )
      (setq Col (vlax-variant-value
           (vlax-get-property Cols "Item" ColNum))
     )
     
      (vlax-invoke-method Col "Delete")
      (vlax-put-property ExcelApp "DisplayAlerts" :vlax-true)
     
      (vl-catch-all-apply
        'vlax-invoke-method
        (list Wbk "Save")
      ) 
      (vl-catch-all-apply
        'vlax-invoke-method
        (list Wbk "Close")
      )
      (vl-catch-all-apply
        'vlax-invoke-method
        (list ExcelApp "Quit")
      )
      (mapcar
        (function
          (lambda (x)
     (vl-catch-all-apply
       (function (lambda ()
            (progn
       (if (not (vlax-object-released-p x))
         (progn
           (vlax-release-object x)
           (setq x nil)
         )
       )
            )
          )
       )
     )
          )
        )
        (list Col Cols Sht Wbk ExcelApp)
      )
      (gc)
      (gc)
      (princ)
      )
    Or add row
    Code:
    ;;;================== add row ====================
    (defun C:AROW (/ Col Cols RowNum ExcelApp FilePath Sht ShtName Wbk)
      
    (vl-load-com)
      
    (setq	FilePath (getfiled "Select Excel file :"
    			   (getvar "dwgprefix")
    			   "xls"
    			   16
    		 )
      )
    
      (setq ShtName (getstring "\nEnter sheet name : "))
      
      (setq RowNum (getint "\nEnter a row number to insert one row after: "))
      
      (setq ExcelApp (vlax-get-or-create-object "Excel.Application"))
      
      (vla-put-visible ExcelApp :vlax-true)
      
      (vlax-put-property ExcelApp "DisplayAlerts" :vlax-false)
      
      (setq	Wbk (vl-catch-all-apply
    	      'vla-open
    	      (list (vlax-get-property ExcelApp "WorkBooks") FilePath)
    	    )
      )
      (setq	Sht (vl-catch-all-apply
    	      'vlax-get-property
    	      (list (vlax-get-property Wbk "Sheets")
    		    "Item"
    		    ShtName
    	      )
    	    )
      )
      (vlax-invoke-method Sht "Activate")
      
      (setq Rows (vlax-get-property Sht "Rows")
    	)
      (setq Row (vlax-variant-value
    	      (vlax-get-property Rows "Item" RowNum))
    	)
     
      (vl-catch-all-apply
        'vlax-invoke-method
        (list Row "Insert")
        )
    
      (vlax-put-property ExcelApp "DisplayAlerts" :vlax-true)
      
      (vl-catch-all-apply
        'vlax-invoke-method
        (list Wbk "Save")
      )	
      (vl-catch-all-apply
        'vlax-invoke-method
        (list Wbk "Close")
      )
    
      (vl-catch-all-apply
        'vlax-invoke-method
        (list ExcelApp "Quit")
      )
    
      (mapcar
        (function
          (lambda (x)
    	(vl-catch-all-apply
    	  (function (lambda ()
    		      (progn
    			(if (not (vlax-object-released-p x))
    			  (progn
    			    (vlax-release-object x)
    			    (setq x nil)
    			  )
    			)
    		      )
    		    )
    	  )
    	)
          )
        )
    
        (list Row Rows Sht Wbk ExcelApp)
      )
      (gc)
      (gc)
      (princ)
      )
    See also Merge/Unmerge methods for the Range object

    ~'J'~
    Last edited by fixo; 2009-10-11 at 10:31 AM. Reason: another example was added

  4. #4
    100 Club jgardner.79905's Avatar
    Join Date
    2004-12
    Posts
    153
    Login to Give a bone
    0

    Default Re: interacting with excel

    J

    Do you know how to "add" or "rename" a tab within the Excel file?

    Jason

  5. #5
    Administrator BlackBox's Avatar
    Join Date
    2009-11
    Posts
    5,714
    Login to Give a bone
    0

    Default Re: interacting with excel

    Quote Originally Posted by jgardner.79905 View Post
    Do you know how to "add" or "rename" a tab within the Excel file?
    Under the Worksheets Object, look for the Add method, and under the Worksheet object look into the Name property.
    "How we think determines what we do, and what we do determines what we get."

    Sincpac C3D ~ Autodesk Exchange Apps

    Computer Specs:
    Dell Precision 3660, Core i9-12900K 5.2GHz, 64GB DDR5 RAM, PCIe 4.0 M.2 SSD (RAID 0), 16GB NVIDIA RTX A4000

  6. #6
    AUGI Addict fixo's Avatar
    Join Date
    2005-05
    Location
    Pietari, Venäjä
    Posts
    1,269
    Login to Give a bone
    0

    Default Re: interacting with excel

    [quote=jgardner.79905;1107927]J

    Do you know how to "add" or "rename" a tab within the Excel file?

    Jason[/quote
    Change to your suit:
    Code:
     
    (vl-load-com)
    ;;local defuns
    (defun ren_excel_tab (FilePath shtName newName / ExcelApp  Sht  Wbk)
    (setq ExcelApp (vlax-get-or-create-object "Excel.Application"))
    (vla-put-visible ExcelApp :vlax-true)
    (vlax-put-property ExcelApp 'DisplayAlerts :vlax-false)
    (setq Wbk (vl-catch-all-apply
         'vla-open
         (list (vlax-get-property ExcelApp "WorkBooks") FilePath)
         )
          )
    ;;;(setq Sht (vl-catch-all-apply
    ;;;     'vlax-get-property
    ;;;     (list (vlax-get-property Wbk "Sheets")
    ;;;    "Item"
    ;;;    shtName
    ;;;    )
    ;;;     )
    ;;;      )
      (vlax-for Sht (vlax-get-property Wbk "Sheets")
        (if (eq(vlax-get-property Sht "Name") shtName)
          (progn
     
      (vlax-invoke-method Sht "Activate")
     
      (vlax-put-property Sht 'Name newName)
      )
      )
        )
     
    (vlax-put-property ExcelApp 'DisplayAlerts :vlax-true)
     
    (vl-catch-all-apply
      'vlax-invoke-method
      (list Wbk "Close" :vlax-true)
      )
    (vl-catch-all-apply
      'vlax-invoke-method
      (list ExcelApp "Quit")
      )
    (mapcar
      (function
        (lambda (x)
          (vl-catch-all-apply
     (function (lambda ()
          (progn
            (if (not (vlax-object-released-p x))
       (progn
         (vlax-release-object x)
         (setq x nil)
         )
       )
            )
          )
        )
     )
          )
        )
      (list  Sht Wbk ExcelApp)
      )
      (gc)
      (gc)
      (gc)
      (princ)
    )
    (defun add_excel_tab (FilePath shtName  / ExcelApp  Sht  Shts Wbk)
    (setq ExcelApp (vlax-get-or-create-object "Excel.Application"))
    (vla-put-visible ExcelApp :vlax-true)
    (vlax-put-property ExcelApp 'DisplayAlerts :vlax-false)
    (setq Wbk (vl-catch-all-apply
         'vla-open
         (list (vlax-get-property ExcelApp "WorkBooks") FilePath)
         )
          )
    ;;;
      (setq Shts (vlax-get-property Wbk "Sheets"))
      (vlax-invoke-method Shts 'Add)
      (setq Sht (vl-catch-all-apply
         'vlax-get-property
         (list (vlax-get-property Wbk "Sheets")
        "Item"
        (vlax-get-property Shts "Count")
        )
         )
          )
     
      (vlax-put-property Sht 'Name shtName)
     
     
    (vlax-put-property ExcelApp 'DisplayAlerts :vlax-true)
     
    (vl-catch-all-apply
      'vlax-invoke-method
      (list Wbk "Close" :vlax-true)
      )
    (vl-catch-all-apply
      'vlax-invoke-method
      (list ExcelApp "Quit")
      )
    (mapcar
      (function
        (lambda (x)
          (vl-catch-all-apply
     (function (lambda ()
          (progn
            (if (not (vlax-object-released-p x))
       (progn
         (vlax-release-object x)
         (setq x nil)
         )
       )
            )
          )
        )
     )
          )
        )
      (list  Sht Shts Wbk ExcelApp)
      )
      (gc)
      (gc)
      (gc)
      (princ)
    )
    ;; main program
    (defun C:Xman (/ fname newname oldname rename_excel_tab oldName)
     
     (setq fname (getfiled "Select Excel File:" (getvar "dwgprefix") "XLS" 8)
           oldName (getstring T "\nEnter an old tab name:"))
           newName (getstring T "\nEnter a newtab name:"))
           )
     
      ;;Rename Tab:
    ;;;(ren_excel_tab fname oldName newName)
      ;;;add tab:
      (add_excel_tab fname newName)
      (princ)
      )
    (prompt
      "\n\t\t>>>\tType Xman  to execute\t<<<")
    (prin1)

  7. #7
    Administrator BlackBox's Avatar
    Join Date
    2009-11
    Posts
    5,714
    Login to Give a bone
    0

    Default Re: interacting with excel

    :: OFF TOPIC ::

    Fixo,

    How does one prevent Outlook from displaying an alert, when accessing the Recipients collection for a new Mail Item object?

    I saw your usage of (vlax-put-property ExcelApp 'DisplayAlerts :vlax-false) and had hoped that would work, with my OutlookApp object, but sadly this returned an error:

    Code:
    ; error: ActiveX Server returned the error: unknown name: DISPLAYALERTS
    _$
    Admittedly, I did not verify the property is available to the OutlookApp object prior to testing (for expediency).

    Any suggestion would be greatly appreciated!
    Last edited by RenderMan; 2010-11-03 at 07:26 PM.
    "How we think determines what we do, and what we do determines what we get."

    Sincpac C3D ~ Autodesk Exchange Apps

    Computer Specs:
    Dell Precision 3660, Core i9-12900K 5.2GHz, 64GB DDR5 RAM, PCIe 4.0 M.2 SSD (RAID 0), 16GB NVIDIA RTX A4000

  8. #8
    AUGI Addict fixo's Avatar
    Join Date
    2005-05
    Location
    Pietari, Venäjä
    Posts
    1,269
    Login to Give a bone
    0

    Default Re: interacting with excel

    Quote Originally Posted by RenderMan View Post
    :: OFF TOPIC ::

    Fixo,

    How does one prevent Outlook from displaying an alert, when accessing the Recipients collection for a new Mail Item object?

    I saw your usage of (vlax-put-property ExcelApp 'DisplayAlerts :vlax-false) and had hoped that would work, with my OutlookApp object, but sadly this returned an error:

    Code:
    ; error: ActiveX Server returned the error: unknown name: DISPLAYALERTS
    _$
    Admittedly, I did not verify the property is available to the OutlookApp object prior to testing (for expediency).

    Any suggestion would be greatly appreciated!
    I have not have an expeience with outlook sorry
    take look ath this page
    it may help
    http://www.rondebruin.nl/mail/prevent.htm

  9. #9
    Administrator BlackBox's Avatar
    Join Date
    2009-11
    Posts
    5,714
    Login to Give a bone
    0

    Default Re: interacting with excel

    Quote Originally Posted by fixo View Post
    I have not have an expeience with outlook sorry
    take look ath this page
    it may help
    http://www.rondebruin.nl/mail/prevent.htm
    My office is using Office 2003 right now, so there's no easy solution. We're getting new PC's in a few weeks, and they will come loaded with Office 2007 (or newer?) I believe. I'll take another crack at it then.

    Thanks for the link, Fixo!
    "How we think determines what we do, and what we do determines what we get."

    Sincpac C3D ~ Autodesk Exchange Apps

    Computer Specs:
    Dell Precision 3660, Core i9-12900K 5.2GHz, 64GB DDR5 RAM, PCIe 4.0 M.2 SSD (RAID 0), 16GB NVIDIA RTX A4000

  10. #10
    AUGI Addict fixo's Avatar
    Join Date
    2005-05
    Location
    Pietari, Venäjä
    Posts
    1,269
    Login to Give a bone
    0

    Default Re: interacting with excel

    Quote Originally Posted by RenderMan View Post
    My office is using Office 2003 right now, so there's no easy solution. We're getting new PC's in a few weeks, and they will come loaded with Office 2007 (or newer?) I believe. I'll take another crack at it then.

    Thanks for the link, Fixo!
    No problem,
    Just an idea:
    You might want to search for some info on this site:
    http://www.vbaexpress.com/forum/forumdisplay.php?f=18
    than convert it from VBA on Lisp

Page 1 of 2 12 LastLast

Similar Threads

  1. Replies: 5
    Last Post: 2013-10-16, 05:39 PM
  2. Interacting with Revit through a UserForm
    By dangtrang37325793 in forum Revit - API
    Replies: 10
    Last Post: 2013-10-03, 08:10 AM
  3. Replies: 1
    Last Post: 2012-08-08, 05:50 PM
  4. Replies: 1
    Last Post: 2009-04-29, 11:03 AM
  5. in-place truss interacting with stick symbols
    By ck.107547 in forum Revit Structure - General
    Replies: 5
    Last Post: 2006-07-06, 12:02 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •