Macro to open an excel spread sheet

[Jasenc]

Hi could someone help me

Im looking for a simple autocad macro to open a excel spreadsheet.

 

Ive tried this code but I cant seem to get it to work

 

^c^c_(startapp \"Explorer\" \"j:\\\\General Engineering\\\\AutoCAD\\\\Lisp
Routine 2012\\\\SteelUK001202.xls\")"

 

Any suggetstions

Edited February 13, 2013 by SLW210
Code Tags.

[Lee Mac]

Try:

^C^C(startapp "explorer" "J:/General Engineering/AutoCAD/Lisp Routine 2012/SteelUK001202.xls")

[Jasenc]

Lee

thank you for your help,

Its almost ther eI guess.

For some reason it doesnt find teh file though.

 

I get an explorer error aying " the path 'J:\Book1.xls does not exist or is not a directory."

 

any susgetsions ?

[Lee Mac]

What does this return at your AutoCAD command-line?

(findfile "J:/General Engineering/AutoCAD/Lisp Routine 2012/SteelUK001202.xls")

[Jasenc]

Thank you for response.

 

When I enter it to the command line

all I get in return is 'NIL'

[dbroada]

That shows you that AutoCAD cannot find the file. Are you certain you have the file name correct?

 

edit---------------

 

the file name would have been displayed had it been found

[Jasenc]

Ok I tried it again and now I get this

 

Command: (findfile "J:/General Engineering/AutoCAD/Autocad/Lisp Routine

2012/SteelUK001202.xls")

"J:\\General Engineering\\AutoCAD\\Autocad\\Lisp Routine

2012\\SteelUK001202.xls"

[dbroada]

does Lee's post work now with the extra /Autocad/ added?

[Lee Mac]

Ok I tried it again and now I get this

 

Command: (findfile "J:/General Engineering/AutoCAD[highlight]/Autocad/[/highlight]Lisp Routine

2012/SteelUK001202.xls")

"J:\\General Engineering\\AutoCAD\\Autocad\\Lisp Routine

2012\\SteelUK001202.xls"

 

In that case, try:

^C^C(startapp "explorer" "J:/General Engineering/AutoCAD[highlight]/Autocad/[/highlight]Lisp Routine 2012/SteelUK001202.xls")

[Jasenc]

Guys thank you for your help.

Unfortunatly it still doesnt work even with the extra 'Autocad' added.

I get an explorer error stating path or directory doenst exist .

 

this is what is displayed on my command line now

 

Command: (startapp "explorer" "J:/General Engineering/AutoCAD/Autocad/Lisp

Routine 2012/SteelUK001202.xls") 33

[Lee Mac]

Unfortunatly it still doesnt work even with the extra 'Autocad' added.

I get an explorer error stating path or directory doenst exist .

 

That's odd, perhaps try this instead:

^C^C(startapp "explorer" (findfile "J:/General Engineering/AutoCAD/Autocad/Lisp Routine 2012/SteelUK001202.xls"))

[Jasenc]

Lee ,

 

That works like a treat ,

Thanks a million for your help!!!!

[Lee Mac]

You're welcome!

It evidently appears that the Explorer application can only accept filepaths with backslash delimiters.

[Jasenc]

Lee , me just being fussy . The .xls file I'm trying to open , opens fine with the code you helped me out with the only thing is it doesn't open with excel. It opens through explorer is there way to open it directly with excel ?

[Bill Tillman]

Here is a snippet of code from Terry Miller's GETEXCEL code which you can find the complete copy here. It works quite well and has lots of other uses. Terry was kind enough to share this with the world and for that we send him a big note of thanks.

 

NOTE: If you don't automatically load it, this will require the following line to be added to the code. Put it just before the (defun statement in this code and it will do it's thing for you.

 

(vl-load-com)

 

 

;-------------------------------------------------------------------------------
; OpenExcel - Opens an Excel spreadsheet
; Arguments: 3
;   ExcelFile$ = Excel filename or nil for new spreadsheet
;   SheetName$ = Sheet name or nil for not specified
;   Visible = t for visible or nil for hidden
; Syntax examples:
; (OpenExcel "C:\\Temp\\Temp.xls" "Sheet2" t) = Opens C:\Temp\Temp.xls on Sheet2 as visible session
; (OpenExcel "C:\\Temp\\Temp.xls" nil nil) = Opens C:\Temp\Temp.xls on current sheet as hidden session
; (OpenExcel nil "Parts List" nil) =  Opens a new spreadsheet and creates a Part List sheet as hidden session
;-------------------------------------------------------------------------------
(defun OpenExcel (ExcelFile$ SheetName$ Visible / Sheet$ Sheets@ Worksheet)
 (if (= (type ExcelFile$) 'STR)
   (if (findfile ExcelFile$)
     (setq *ExcelFile$ ExcelFile$)
     (progn
       (alert (strcat "Excel file " ExcelFile$ " not found."))
       (exit)
     );progn
   );if
   (setq *ExcelFile$ "")
 );if
 (gc)
 (if (setq *ExcelApp% (vlax-get-object "Excel.Application"))
   (progn
     (alert "Close all Excel spreadsheets to continue!")
     (vlax-release-object *ExcelApp%)(gc)
   );progn
 );if
 (setq *ExcelApp% (vlax-get-or-create-object "Excel.Application"))
 (if ExcelFile$
   (if (findfile ExcelFile$)
     (vlax-invoke-method (vlax-get-property *ExcelApp% 'WorkBooks) 'Open ExcelFile$)
     (vlax-invoke-method (vlax-get-property *ExcelApp% 'WorkBooks) 'Add)
   );if
   (vlax-invoke-method (vlax-get-property *ExcelApp% 'WorkBooks) 'Add)
 );if
 (if Visible
   (vla-put-visible *ExcelApp% :vlax-true)
 );if
 (if (= (type SheetName$) 'STR)
   (progn
     (vlax-for Sheet$ (vlax-get-property *ExcelApp% "Sheets")
       (setq Sheets@ (append Sheets@ (list (vlax-get-property Sheet$ "Name"))))
     );vlax-for
     (if (member SheetName$ Sheets@)
       (vlax-for Worksheet (vlax-get-property *ExcelApp% "Sheets")
         (if (= (vlax-get-property Worksheet "Name") SheetName$)
           (vlax-invoke-method Worksheet "Activate")
         );if
       );vlax-for
       (vlax-put-property (vlax-invoke-method (vlax-get-property *ExcelApp% "Sheets") "Add") "Name" SheetName$)
     );if
   );progn
 );if
 (princ)
);defun OpenExcel

[Jasenc]

Thank you for your reply ,

So where it states ExcelFile$ I can replace this with J:\\General Engineering\\AutoCAD\\Autocadlisp\\Lisp Routine 2013\\titleblock.xls right ?

and what if I dont want it to open specific sheet in excel what can I chop out of this routine ?

[Bill Tillman]

Yes, that is correct, but this routine was meant to be called like he shows he the Syntax Examples in the header of the code. As for the second question, just use the 2nd example in his Syntax Examples. This should open the file to the default sheet which would be the last sheet activated when it was last saved.

 

There are lots of other methods with this that can create a new file on the fly, etc... but I typically use .NET for those tasks. VLISP works fine with it, but it's not the speediest method and it's also limited to users who have AutoCAD installed. And at $5000 per seat (we just paid that for 2013 with annual subscription and sales tax) it's an expensive option. You can download the new Visual Studio 2012 Express for FREE....I love that price... and do lots more as far as interfacing Excel and AutoCAD together.

[Lee Mac]

You could alternatively use an Excel command-line switch.

 

Here is a quick example demonstrating how to determine the shell command to use:

(defun _excelopen ( fn / id )
   (if
       (and
           (setq fn (findfile fn))
           (setq id (vl-registry-read (strcat "HKEY_CLASSES_ROOT\\" (vl-filename-extension fn))))
           (setq id (vl-registry-read (strcat "HKEY_CLASSES_ROOT\\" id "\\shell\\Open\\command")))
       )
       (if (vl-string-search "%1" id)
           (startapp (vl-string-subst fn "%1" id))
           (startapp (strcat id " \"" fn "\""))
       )
   )
)

(_excelopen "J:/General Engineering/AutoCAD/Autocad/Lisp Routine 2012/SteelUK001202.xls")

Though, if you know the path to the Excel executable, you can use this path directly in the startapp expression.

[Jasenc]

Thanks you guys , really appreciate all the help