TheSwamp
Code Red => AutoLISP (Vanilla / Visual) => Topic started by: Fuccaro 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!
-
Hi Fuccaro
There are a few (V)Lisp functions around Excel. Some of them are maybe usefull for you...
Cheers
Jürg
;
; =============================================================================
; == 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)
)
-
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....)?