Author Topic: Excel print from within AutoLisp?  (Read 2880 times)

0 Members and 1 Guest are viewing this topic.

Fuccaro

  • Guest
Excel print from within AutoLisp?
« on: June 20, 2007, 04:16:32 AM »
I work in the mechanical field and the drawings we create are sent to the workshop along with a sheet containing additional information. I wrote a macro in Excel to fill-out the sheet for me. Now the next step would be to print the Excel sheet right from AutoCAD. I wrote a Lisp routine to open the desired document in Excel –but how can I start the "print" command? Is there a way that a Lisp routine opens a document in Excel *and* starts an existing macro?
Thanks!

Jürg Menzi

  • Swamp Rat
  • Posts: 599
  • Oberegg, Switzerland
Re: Excel print from within AutoLisp?
« Reply #1 on: June 20, 2007, 08:15:46 AM »
Hi Fuccaro

There are a few (V)Lisp functions around Excel. Some of them are maybe usefull for you...

Cheers
Jürg
Code: [Select]
;
; =============================================================================
; == Initialize functions =====================================================
; =============================================================================
;
(vl-load-com) ;Initialize ActiveX
;
; == Function MeInitApp
; Initialize the application.
; Arguments [Type]:
;   --- =
; Return [Type]:
;   > Null
; Notes:
;   None
;
(defun MeInitApp ( / ExcTlb TmpVal)
 
; -- Initialize application environment
 (setq vlax-vbDecimal 14
       Me:Ofv (MeGetOfficeVersion)
       TmpVal (vl-registry-read
               (strcat
                "HKEY_LOCAL_MACHINE\\SOFTWARE\\Microsoft\\Office\\"
                Me:Ofv ".0\\Excel\\InstallRoot"
               )
               "Path"
              )
       TmpVal (if (and TmpVal (= (substr TmpVal (strlen TmpVal)) "\\"))
               (substr TmpVal 1 (1- (strlen TmpVal)))
               TmpVal
              )
       ExcTlb (if (> (atof Me:Ofv) 9.0)
               "\\Excel.exe"
               (strcat "\\Excel" Me:Ofv ".olb")
              )
       ExcTlb (if TmpVal (strcat TmpVal ExcTlb))
 )

; -- Check all necessary environment
 (cond
  ((eq Me:Ofv "0")
   (alert  (strcat " Missing Office installation... "
                   "\n Check your Office installation please. "
           )
   )
  )
  ((not (and ExcTlb (findfile ExcTlb)))
   (alert (strcat "System file "
                  "'Excel-Typelibrary' not found"
                  "\n Check your Office installation please. "
          )
   )
  )
  (T
   
; -- Import Excel TypeLibrary
   (if (null exck-xl24HourClock)
    (vlax-Import-Type-Library
     :tlb-filename ExcTlb
     :methods-prefix "excm-"
     :properties-prefix "excp-"
     :constants-prefix "exck-"
    )
   )
  )
 )
 
; -- Copyright - Note (May be never deleted)
 (princ "\n©2001-2005 MENZI ENGINEERING GmbH")
 (princ)
)
;
; =============================================================================
; == Common functions library =================================================
; =============================================================================
;
; == Function MeGetOfficeVersion
; Returns the highest Office version (by Excel).
; Arguments [Type]:
;   --- =
; Return [Type]:
;   > Offive version [STR]
;   > "0" if no valid Office version found
; Notes:
;   - Version search is limited between 8.0 and 20.0
;
(defun MeGetOfficeVersion ( / AppKey MaxVer MinVer RegVal RetVal TmpVal)
 (setq AppKey (strcat "HKEY_LOCAL_MACHINE\\Software"
                      "\\Microsoft\\Office\\"
              )
       MinVer 8.0
       MaxVer 20.0
       RetVal "0"
 )
 (while (>= MaxVer MinVer)
  (setq TmpVal (strcat AppKey (MeFormatNums MaxVer 1) "\\Common\\InstallRoot")
        RegVal (if (> MaxVer 8.0)
                (vl-registry-read TmpVal "Path")
                (vl-registry-read TmpVal "OfficeBin")
               )
        RegVal (if (and RegVal (eq (substr RegVal (strlen RegVal)) "\\"))
                (substr RegVal 1 (1- (strlen RegVal)))
                RegVal
               )
  )
  (if (and RegVal (MeFolderExists RegVal))
   (setq RetVal (rtos MaxVer 2 0)
         MaxVer 0
   )
   (setq MaxVer (1- MaxVer))
  )
 )
 RetVal
)
;
; == Function MeFormatNums
; Formats the precision of decimal numbers.
; Arguments [Type]:
;   Val = Value [REAL/STR]
;   Pre = Precision [INT]
; Return [Type]:
;   > Formatted value [STR]
; Notes:
;   None
;
(defun MeFormatNums (Val Pre / CurStr DecVal PntPos)
 (setq CurStr (if (= (type Val) 'REAL) (rtos Val 2 Pre) Val)
       PntPos (vl-string-position 46 CurStr)
       PntPos (if (> PntPos 0) PntPos (strlen CurStr))
       DecVal (substr CurStr (+ PntPos 2))
 )
 (repeat (- Pre (strlen DecVal))
  (setq DecVal (strcat DecVal "0"))
 )
 (strcat (substr CurStr 1 PntPos) "." DecVal)
)
;
; -- MeFolderExists
; Determine if the requested folder exists.
; Arguments [Typ]:
;   Dir = Folder to check "C:\\Temp\\MyTemp\\AllScrap" [STR]
; Return [Typ]:
;   > T   folder exists
;     nil folder doesn't exist
; Notes:
;   - Requires ScrRun.dll.
;
(defun MeFolderExists (Dir / FilSys RetVal)
 (setq FilSys (vlax-create-object "Scripting.FileSystemObject")
       RetVal (= (vlax-invoke FilSys 'FolderExists Dir) -1)
 )
 (vlax-release-object FilSys)
 RetVal
)
;
; == Function MeVariantVal
; Converts more variant types in a value than vlax-variant-value.
; Arguments [Type]:
;   Itm = VLAX value [VARIANT]
; Return [Type]:
;   > LISP value [VARIANT]
; Notes:
;   None
;
(defun MeVariantVal (Itm / VarTyp)
 (cond
  ((or
    (= vlax-vbCurrency (setq VarTyp (vlax-variant-type Itm)))
    (= vlax-vbDecimal VarTyp)
   )
   (vlax-variant-value
    (vlax-variant-change-type Itm vlax-vbDouble)
   )
  )
  ((= vlax-vbDate VarTyp)
   (MeConvJulianDate (vlax-variant-value Itm) "DD.MO.YYYY")
  )
  ((= vlax-vbBoolean VarTyp)
   (if (= :vlax-true (vlax-variant-value Itm)) "True" "False")
  )
  (T (vlax-variant-value Itm))
 )
)
;
; == Function MeConvJulianDate
; Convert julian date/time to standard format.
; Arguments [Type]:
;   Val = Date/Time Julian [REAL]
;   Mde = Conversion Mode, eg "MO/DD/YYYY HH:MM" [STR] *)
; Return [Type]:
;   > Formatted date/time [STR]
; Notes:
;   *) Details for mode see Diesel, edtime
;
(defun MeConvJulianDate (Val Mde / TmpVal)
 (setq TmpVal (if (minusp (- Val 2415019.0)) (+ Val 2415019.0) Val))
 (menucmd (strcat "M=$(edtime," (rtos TmpVal) "," Mde ")"))
)
;
; =============================================================================
; == Excel functions library ==================================================
; =============================================================================
;
; == Function MeExcGetCell
; Returns the cell Object by position.
; Arguments [Type]:
;   Rng = Sheet-Objekt [OBJECT]
;   Row = Row [INT]
;   Col = Column [INT]
; Return [Type]:
;   > Cell object [OBJECT]
; Notes:
;   None
;
(defun MeExcGetCell (Rng Row Col)
 (vlax-Variant-value
  (excp-get-Item
   (excp-get-Cells Rng)
   (vlax-Make-Variant Row)
   (vlax-Make-Variant Col)
  )
 )
)
;
; == Function MeExcGetCellVal
; Returns the value of a cell object by position.
; Arguments [Type]:
;   Row = Row [INT]
;   Col = Column [INT]
; Return [Type]:
;   > Value of the cell object [ALL]
; Notes:
;   None
;
(defun MeExcGetCellVal (Row Col)
 (MeVariantVal
  (excp-get-Value
   (MeExcGetCell
    (excp-get-ActiveSheet Me:ExO)
    Row Col
   )
  )
 )
)
;
; == Function MeExcGetRowVals
; Returns all values of a row by position.
; Arguments [Type]:
;   Row = Row [INT]
;   Col = First column [INT]
;   Nuc = Number of columns [INT]
; Return [Type]:
;   > Value list [LIST]
; Notes:
;   None
;
(defun MeExcGetRowVals (Row Col Nuc / NxtCol RetVal)
 (setq NxtCol Col)
 (repeat Nuc
  (setq RetVal (cons (MeExcGetCellVal Row NxtCol) RetVal)
        NxtCol (1+ NxtCol)
  )
 )
 (reverse RetVal)
)
;
; == Function MeExcGetRngValsByRows
; Returns all values of a range by row control.
; Arguments [Type]:
;   Row = First row [INT]
;   Col = First column [INT]
;   Nuc = Number of columns [INT]
; Return [Type]:
;   > Value list '((A1 A2 A3...) (B1 B2 B3...)) [LIST]
; Notes:
;   None
;
(defun MeExcGetRngValsByRows (Row Col Nuc / NxtRow RetVal TmpVal)
 (setq NxtRow Row)
 (while (apply 'or (setq TmpVal (MeExcGetRowVals NxtRow Col Nuc)))
  (setq RetVal (cons TmpVal RetVal)
        NxtRow (1+ NxtRow)
  )
 )
 (reverse RetVal)
)
;
; == Function MeExcInterfaceInit
; Initialize the Excel interface.
; Arguments [Type]:
;   Fil = Name and path of worksheet [STR]
; Return [Type]:
;   > Null
; Notes:
;   None
;
(defun MeExcInterfaceInit (Fil / ExcFil)
 (setq Me:ExO (vlax-get-or-create-object (strcat "Excel.Application." Me:Ofv))
       ExcFil (substr ExcFil 1 (vl-string-search "." Fil))
       Me:Wbk (vlax-invoke-method
               (vlax-get-property Me:ExO 'Workbooks)
               'Open ExcFil
              )
 )
 (princ)
)
;
; == Function MeExcInterfaceClose
; Close the Excel interface.
; Arguments [Type]:
;   Sav = Save flag:
;         > :vlax-true  = save worksheet
;         > :vlax-false = no save
; Return [Type]:
;   > Null
; Notes:
;   None
;
(defun MeExcInterfaceClose (Sav)
 (vlax-invoke-method Me:Wbk 'Close Sav)
 (if (not (vlax-object-released-p Me:Wbk)) (vlax-release-object Me:Wbk))
 (vlax-invoke-method Me:ExO 'Quit)
 (if (not (vlax-object-released-p Me:ExO)) (vlax-release-object Me:ExO))
 (setq Me:Wbk nil
       Me:ExO nil
 )
 (gc)
 (princ)
)
A computer's human touch is its unscrupulousness!
MENZI ENGINEERING GmbH
Current A2k16... A2k24 - Start R2.18

uncoolperson

  • Guest
Re: Excel print from within AutoLisp?
« Reply #2 on: June 20, 2007, 10:08:56 AM »
I work in the mechanical field and the drawings we create are sent to the workshop along with a sheet containing additional information. I wrote a macro in Excel to fill-out the sheet for me. Now the next step would be to print the Excel sheet right from AutoCAD. I wrote a Lisp routine to open the desired document in Excel –but how can I start the "print" command? Is there a way that a Lisp routine opens a document in Excel *and* starts an existing macro?
Thanks!


could you get excel (in some vba) to print on opening if something (like say autocad is open or something....)?