Topic: Open Excel CSV file with Lisp

[ziko30]

I have modified a lisp routine by Leonid Nemirovsky (http://home.pacifier.com/~nemi) to suit my purposes. After I have written data to an “excel csv” file, how do I immediately launch excel by opening the file I have just written data to. I can launch excel using an external command set in my acad.pgp but I can’t quite get the STARTAPP & PATH coded right to open the specific file I want.  The excel file is saved as the DrawingName.csv. (so it’s the same name as the current drawing save for extension).  I got my fish, help me get it off the hook. 

[Sebb77]

http://web2.airmail.net/terrycad/AutoLISP-Code.htm

As many others i use this powerful lisp -- getexcel-- to read and write in excel. You'll find OPENEXCEL function, with which you can open file with echo or not.

[CAB]

Is this any help?
http://www.theswamp.org/index.php?topic=20409.msg257580#msg257580

[gile]

Hi,

You can try this one.
Double quotes are needed if the filename countains spaces.
Change the Excel.exe path to the right one.

Code: [Select]

(startapp "C:\\Program Files\\Microsoft Office\\Office11\\Excel.exe"
  (strcat "\""
  (getvar 'dwgprefix)
  (vl-filename-base (getvar 'dwgname))
  ".csv\""
  )
)

[ziko30]

Thank you all for your time.
This is just fantastic!   I looked into the 3 responses and tried Gile’s suggestion first…  It worked perfect the first time. Thanks again Sebb77, CAB  & Gile.
Happy AutoLisping All! 

[ronjonp]

Thank you all for your time.
This is just fantastic!   I looked into the 3 responses and tried Gile’s suggestion first…  It worked perfect the first time. Thanks again Sebb77, CAB  & Gile.
Happy AutoLisping All! 

You could use this as well to open anything:

Code: [Select]

(defun openfile (file / sh)
  (setq sh (vla-getinterfaceobject (vlax-get-acad-object) "Shell.Application"))
  (vlax-invoke-method sh 'open (findfile file))
  (vlax-release-object sh)
)


[ziko30]

Ronjonp et al ,

Looks like I am  headed for the deeper waters…at least for my level.  Some questions:

1-   By  ANYTHING do you mean  any program I have on my PC I can open with the code given?
2-   Where can I find listed with explanations all VLISP functions. When I look in the help files I don’t see all of them. Maybe I am not  looking in the right place or maybe there is no such thing.

3-  Could you explain the use of the variable "file" a little bit starting from where you declare it as global.

4- My novitiate reading of  help on the "findfile"  function leads me to believe I will  search for a given file in a directory and when  I find the file open it. But in this case I know exactly the location and name of the file: using this method, could I not just point to it and open it.  Iam thinking of  time to open...
5- Is  "vla-getinterfaceboject" the same as "vlax-get-or-create-object" and do I replace "shell" with Excel for example

[Jeff_M]

1. Yes, any file which has an extension which has been set to open with a specific program (doc = Word, xsl - Excel, csv = Excel, etc)

2. The ActiveX portions of the Vlisp code are derived from the ActiveX methods & properties. There are no official documents that show exactly how this is done. A good start, however, is the Visual Lisp Developer's Bible by David M Stein. I think a copy has ben posted here at one time.

3. the variable "file" is an argument to the function, which makes it a local variable to that function.

4. Using findfile, even if you know the name & location, will ensure you have the file...it really doesn't take much time at all to execute.

5. No and No. The GetInterfaceObject is an ActiveX method for Autocad, wheareas the get-or-creat-object will get an, or create a new, instance of an object (program).  Do not replace Shell with anything, see response to question #1.

HTH

[ziko30]

Jeff... yes it helps and clears some misconceptions on my part. But I still don't know how to use the code. For Example, my drawing name is shapes.dwg and the csv output is shapes.csv in a given folder. How would I use Ronjon's code  in this:

Code: [Select]

(defun c:alist (/ nm lnm llnm nam name opf ln lname color ltype lna lnamea colora ltypea)
(setq nm (getvar "dwgname"))
(setq lnm (strlen nm))
(setq llnm (- lnm 3))
(setq nam (substr nm 1 llnm))
(setq name (strcat nam "csv"))
 
(setq opf (open (strcat "C:\\MyFile\\Myfolder\\" nam "csv") "w"))
 
(setq ln (tblnext "layer" T))
(setq lname (cdr (assoc 2 ln)))
(setq color (cdr (assoc 62 ln)))
(setq ltype (cdr (assoc 6 ln)))
 
  (write-line (strcat "Drawing: " nm ) opf)
(write-line "Layer Name,Color,Linetype " opf)
(write-line (strcat lname "," (rtos (abs color) 2 0) "," ltype ) opf)
  (while
      (setq lna (tblnext "layer"))
      (setq lnamea (cdr (assoc 2 lna)))
      (setq colora (cdr (assoc 62 lna)))
      (setq ltypea (cdr (assoc 6 lna)))

      (write-line (strcat lnamea "," (rtos (abs colora) 2 0) "," ltypea) opf)
  )
(close opf)
 
(startapp "C:\\Program Files\\Microsoft Office\\Office11\\Excel.exe" ;;Gile

  (strcat "\"" (getvar 'dwgprefix) (vl-filename-base (getvar 'dwgname)) ".csv\"");; Gile
 
(princ)
)
I understand the use of Gile's code and works... but I would like to learn the other as well.  BTW point noted on the Visual Lisp Developer's Bible

Thx.

[Jeff_M]

I understand the use of Gile's code and works... but I would like to learn the other as well.  BTW point noted on the Visual Lisp Developer's Bible

Thx.

You're welcome. Here's your code modified for use with ronjonp's:

Code: [Select]

(defun c:alist (/ nm lnm llnm nam name opf ln lname color ltype lna
lnamea colora ltypea)
  (defun openfile (file / sh)
    (setq sh (vla-getinterfaceobject
       (vlax-get-acad-object)
       "Shell.Application"
     )
    )
    (vlax-invoke-method sh 'open (findfile file))
    (vlax-release-object sh)
  )
  (setq nm (getvar "dwgname"))
  (setq lnm (strlen nm))
  (setq llnm (- lnm 3))
  (setq nam (substr nm 1 llnm))
  (setq name (strcat nam "csv"))

  (setq opf (open (strcat "C:\\MyFile\\Myfolder\\" nam "csv") "w"))

  (setq ln (tblnext "layer" T))
  (setq lname (cdr (assoc 2 ln)))
  (setq color (cdr (assoc 62 ln)))
  (setq ltype (cdr (assoc 6 ln)))

  (write-line (strcat "Drawing: " nm) opf)
  (write-line "Layer Name,Color,Linetype " opf)
  (write-line
    (strcat lname "," (rtos (abs color) 2 0) "," ltype)
    opf
  )
  (while
    (setq lna (tblnext "layer"))
     (setq lnamea (cdr (assoc 2 lna)))
     (setq colora (cdr (assoc 62 lna)))
     (setq ltypea (cdr (assoc 6 lna)))

     (write-line
       (strcat lnamea "," (rtos (abs colora) 2 0) "," ltypea)
       opf
     )
  )
  (close opf)

  (openfile (strcat "C:\\MyFile\\Myfolder\\" nam "csv"))

  (princ)
)
Note that I included the (openfile) defun inside your code. This does not need to be done this way, so long as the defun gets loaded prior to running your code. Such as placing it in your acaddoc.lsp would ensure you could use this same code within other lisps. This is also a good reason to pass the filename rather than hard code it....code reuseability.

[ziko30]

Jeff....

Wonderful... worked like a charm! Have a great weekend.