Modify Excel Options using AutoLISP ?

[jbreard]

Hello,

 

I'm working in track layout design and I've written quite a number of programms to edit polyline vertices coordinates and some more refined analysis that write their results onto Excel sheets. They work just fine but when they are called and some (not so) heavy Excel spreadsheets are already open they take ages to execute. The problem comes from the "automatic re calculation" option in Excel : for each value written into a cell, all opened spreadsheets are fully re calculated (which is the basic option in Excel). It is of course possible to get around this by either closing all heavy spreadsheets or by manually disabling the automatic recalculation option in Excel before executing the LISP programm. The first option is not very productive if you have multiple spreadsheets open for your work at end, the other is quite dangerous when people not familiar with Excel use the programm.

 

My question is this : is there a way with AutoLISP to change such options directly in Excel during the execution time of the routine  and set it back the way it was originaly (like the getvar/setvar method for AutoCAD) ? My guess is maybe VBA is the right answer. If so, a newbie question. Is it possible in an AutoLISP routine to call a sub-routine written in VBA for AutoCAD ?

 

I hope I was clear enough in my explanations. I wish you a good night (or a good day) to you all. Cheers,

 

Jacques

[jbreard]

Hi there,

 

After some research, the 2 following VBA routines seem to do the job :

 

Public Sub ManualCalculation()

With Application

.Calculation = xlManual

End With
End Sub

Public Sub AutomaticCalculation()

With Application

.Calculation = xlAutomatic

End With
End Sub

I see that "afralisp.net" has a section on VBA. For those who know about it, will I find my answer by reading it ?

 

Thanks again and cheers,

 

Jacques

[dlanorh]

1 hour ago, jbreard said:

My question is this : is there a way with AutoLISP to change such options directly in Excel during the execution time of the routine  and set it back the way it was originaly (like the getvar/setvar method for AutoCAD) ? My guess is maybe VBA is the right answer. If so, a newbie question. Is it possible in an AutoLISP routine to call a sub-routine written in VBA for AutoCAD ?

 

You should be able to access the properties and method via lisp, although it wouldn't be as simple as getvar / setvar.

 

In a blank drawing copy and paste this to the command line, then press enter

(setq xlApp (vlax-get-or-create-object "Excel.Application"))

Then this

 

(vlax-dump-object xlapp T)

Pressing F2 to switch to the text window will show you all the properties and methods available for the Excel Application. One of these properties is the Calculation property. In VBA this can be toggled between xlManual (Off) and xlAutomatic (On). Lisp has no ideal about the inbuilt Excel constants and so displays the Integer value  -4105

 

from this page   https://docs.microsoft.com/en-us/dotnet/api/microsoft.office.interop.excel.constants?view=excel-pia

we can see that xlAutomatic = -4105 and xlManual = -4135 so toggling this property between these two values should toggle Calculation ON and Off.

This can be achieved using visual lisp

 

(vlax-put-property xlapp 'calculation -4135);Off
(vlax-put-property xlapp 'calculation -4105);On

 

[jbreard]

It works great! I will rummage through the

(vlax-dump-object xlapp T)

I didn't hope for a minute it would take only one line . Thanks a lot for your most welcome help !

 

Jacques

[Grrr]

12 minutes ago, dlanorh said:

from this page   https://docs.microsoft.com/en-us/dotnet/api/microsoft.office.interop.excel.constants?view=excel-pia

we can see that xlAutomatic = -4105 and xlManual = -4135

 

Nice link, FWIW the objects/methods/properties/constants and events in the Excel applicaiton can be observed through the built-in VBAIDE,

by running it and creating a new module, then right click->Object Browser

 

 

[dlanorh]

9 hours ago, Grrr said:

 

Nice link, FWIW the objects/methods/properties/constants and events in the Excel applicaiton can be observed through the built-in VBAIDE,

by running it and creating a new module, then right click->Object Browser

 

 

 

Unfortunately my company no longer allows all things VBA in AutoCAD.    But still allows it's use in Excel  Go fugure. I'm constantly asked why i have excel open whilst working in Autocad

[Grrr]

3 hours ago, dlanorh said:

Unfortunately my company no longer allows all things VBA in AutoCAD.    But still allows it's use in Excel  Go fugure. I'm constantly asked why i have excel open whilst working in Autocad

 

In the worst case, you can utilise the type library -

(setq ImportExcelTypeLibrary ; did ya know that, "Excel.exe" is the type library, thats why "Excel.olb" doesn't exist
  (lambda ( / IName ICLSID IServer tlb )
    ; http://www.theswamp.org/index.php?topic=53368.msg580916#msg580916
    ; http://www.theswamp.org/index.php?topic=53389.msg581045#msg581045
    (or xlm-AccrInt ; User warning: assignment to protected symbol: xlm-AccrInt <- #<SUBR @0000005022ddfd68 nil>
      (and
        (setq IName "Excel.Application")
        (setq ICLSID (vl-registry-read (strcat "HKEY_CLASSES_ROOT\\" IName "\\CLSID")))
        (setq IServer (vl-registry-read (strcat "HKEY_CLASSES_ROOT\\CLSID\\" ICLSID "\\LocalServer32")))
        (setq tlb (findfile (vl-string-right-trim "\/automation" IServer)))
        (vlax-import-type-library 
          :tlb-filename tlb
          :methods-prefix "xlm-"
          :properties-prefix  "xlp-"
          :constants-prefix "xlc-"
        ); vlax-import-type-library
      ); and
    ); or
  ); lambda
); setq ImportExcelTypeLibrary

(ImportExcelTypeLibrary)

(setq ExcelConstants ; Inspect'n'pretty print, or use assoc
  (apply (function append)
    (mapcar (function (lambda (x) (if (wcmatch (vl-symbol-name x) "xlc-*") (list (list x (eval x)))))) (atoms-family 0))
  )
)

;|
(assoc 'xlc-xlCalculationAutomatic ExcelConstants) >> (xlc-xlCalculationAutomatic -4105)
(assoc 'xlc-xlCalculationManual ExcelConstants) >> (xlc-xlCalculationManual -4135)
|;

Although it won't be so comfortable as using the Object browser.

 

FWIW, for the rest MS Office applications -

(setq ImportMSOTypeLibraries
  (lambda nil
    (mapcar
      (function
        (lambda (x f / tlb) 
          (or (eval f)
            (if (findfile (setq tlb (strcat "C:\\Program Files\\Microsoft Office\\Office15\\" (strcase x) ".OLB")))
              (progn 
                (setq x (substr x 1 3))
                (vlax-import-type-library 
                  :tlb-filename tlb
                  :methods-prefix (strcat x "-m-")
                  :properties-prefix (strcat x "-p-") 
                  :constants-prefix (strcat x "-c-") 
                ); vlax-import-type-library
              ); progn
            ); if 
          ); or
        ); lambda
      ); function 
      '("MSWord" "MSAcc" "MSOutl" "MSPpt") ; Word, Access, Outlook, PowerPoint
      '(MSW-m-Accept MSA-m-Add MSO-m-Activate MSP-m-Activate) ; The very first User warning: assignment to protected symbol
    ); mapcar
  ); lambda
); setq ImportMSOTypeLibraries

; (ImportMSOTypeLibraries)

;|
(assoc 'PowerPoint MSOconstants)
(assoc 'Word MSOconstants)
(assoc 'Outlook MSOconstants)
|;
(setq MSOconstants
  (  
    (lambda ( / L n )
      (foreach x (apply (function append) (mapcar (function (lambda (x) (if (wcmatch (vl-symbol-name x) "MS@-c-*") (list x)))) (atoms-family 0)))
        (if (setq n (vl-position (substr (vl-symbol-name x) 3 1) '("W" "A" "O" "P")))
          (progn
            (setq x (list x (eval x)))
            (setq L 
              (cons 
                (nth n
                  (list 
                    (list x nil nil nil)
                    (list nil x nil nil)
                    (list nil nil x nil)
                    (list nil nil nil x)
                  )
                )
                L
              )
            ); setq L
          ); progn
        ); if
      ); foreach
      (setq L (mapcar (function (lambda (x) (vl-remove nil x))) (apply 'mapcar (cons 'list L))))
      (mapcar 'cons '(Word Access Outlook PowerPoint) L)
    )
  )
); setq MSOconstants