Excel 2007 with acad 2010

[clovis]

Hello,
I'm updating our routines and one of them is about Excel.
I have the following list

(("E19" . "335") ("F19" . "334") ("G19" . "336") ("H19" . "") ("T19" . "??") 
("E18" . "338") ("F18" . "337-2") ("G18" . "339") ("H18" . "") ("T18" . "??") 
("AC18" . "??") ("AB18" . "??") ("E17" . "D4") ("F17" . "555") ("G17" . "05") 
("H17" . "") ("T17" . "??") ("E16" . "d3") ("F16" . "340") ("G16" . "01") 
("H16" . "") ("T16" . "??") ("AC16" . "??") ("AB16" . "??") ("E15" . "6") 
("F15" . "666") ("G15" . "02") ("H15" . "") ("T15" . "??") ("I5" . "207135999") 
("I5" . "TEST LINK P&ID ITEMLIST"))

the first member is the cell number whereas the second one is the content of the cell.
Would you be kind to give me a hint to get it working.
Is it still necessary to look for the excel.olb?
THanks for any advice

[fixo]

Wait until tomorrow if you can,
I will try
Just you have to define sheet name and file name
before, coz this data is not enough
Or do you want to write it into the newly created xls file?
Oops, nap time is here...

[fixo]

Can't sleep...

(defun C:wdx(/ cells data filename sheetname xlapp xlbook xlbooks xlcells xlsheet xlsheets)
  (setq filename "C:\\Test\\blah.xls");<--change
  (setq sheetname "Sheet1");<--change
(setq data
'(("E19" . "335") ("F19" . "334") ("G19" . "336") ("H19" . "") ("T19" . "??") 
("E18" . "338") ("F18" . "337-2") ("G18" . "339") ("H18" . "") ("T18" . "??") 
("AC18" . "??") ("AB18" . "??") ("E17" . "D4") ("F17" . "555") ("G17" . "05") 
("H17" . "") ("T17" . "??") ("E16" . "d3") ("F16" . "340") ("G16" . "01") 
("H16" . "") ("T16" . "??") ("AC16" . "??") ("AB16" . "??") ("E15" . "6") 
("F15" . "666") ("G15" . "02") ("H15" . "") ("T15" . "??") ("I5" . "207135999") 
("I5" . "TEST LINK P&ID ITEMLIST")))

  (setq	xlapp	   (vlax-get-or-create-object "Excel.Application")
	xlbooks  (vlax-get-property xlapp 'Workbooks)
;;;	xlbook	   (vlax-invoke-method xlbooks 'Add);<-- for new file
	xlbook (vl-catch-all-apply
	      'vla-open
	      (list xlbooks filename)
	    )
 
	xlsheets (vlax-get-property xlbook 'Sheets)
	xlsheet	   (vlax-get-property xlsheets 'Item sheetname)
	xlcells	   (vlax-get-property xlsheet 'Cells)
	)
  
  
(vla-put-visible xlapp :vlax-true)
(vl-catch-all-apply 'vlax-put-property (list xlapp 'ScreenUpdating :vlax-false))
(foreach item data
  (setq xlrange (vl-catch-all-apply
    'vlax-get-property  (list xlcells 'Range (car item))))
(vl-catch-all-apply
    'vlax-put-property
    (list xlrange 'Value2 
	 (vlax-make-variant
	   (cdr item) 8)))
  )


(vl-catch-all-apply 'vlax-put-property (list xlapp 'screenupdating :vlax-true))
(vlax-invoke-method
    xlbook
    'SaveAs
    filename 
    -4143
    nil
    nil
    :vlax-false
    :vlax-false
    1
    2
  )
(vlax-invoke-method
    xlbook 'Close)
(vlax-invoke-method
    xlapp 'Quit)
  (mapcar '(lambda (x)
	     (vl-catch-all-apply
	       '(lambda	()
		  (vlax-release-object x)
		)
	     )
	   )
	  (list xlcells xlsheet xlsheets xlbook xlbooks xlapp)
  )
  (setq  xlapp nil)
  (gc)(gc)(gc)
  (princ)
  )
(prompt "\n\t\t***\tStart command with WDX\t***\n")
(princ)

(or (vl-load-com)
    (princ)     )

Tested on Excel 2007 (student release), AutoCAD 2010, Win7

~'J'~

[clovis]

Thanks a lot fixo,
I hoop that it wasn't my question that kept you awake

shouldn't the objects be released

(vlax-release-object xlsheets)
...

just saw it..
How can I navigate between all sheets?
Thanks again

[fixo]

I just thought it was urgently for you
Cheers

[clovis]

You're right, I have until the end of the week to make things work. Next week I'm working on a project and thus won't have any time to work on automate.
Just need to find out how to switch between sheets
Thanks

[clovis]

After a better reading of your file I figure it out
Thanks

[fixo]

You're welcome,
sorry for the belating, I have
started to learn English on TV channel now
It will take a lot of time...
If it will be an extra urgent case you might be want to
send me PM
Guess, maybe I wiill be busy next few weeks

[clovis]

Hi
this method doesn't work for me

(vlax-invoke-method
    xlbook
    'SaveAs
    filename 
    -4143
    nil
    nil
    :vlax-false
    :vlax-false
    1
    2
  )

using those ones makes it work

	(vlax-invoke-method Xlbook 'save)
	(vlax-invoke-method Xlbook 'close :vlax-True xlFile)

could you explain the -4143?
Anyways, I got it now so thanks for sharing!

[fixo]

could you explain the -4143?
Anyways, I got it now so thanks for sharing!

Hint:
Open VBA Editor in Excel, then look at SaveAs method in the Excel Help
Then copy the desired argument starting with "xl", open Immediate window inside
the editor, then paste in this window your argument preceded with ? symbol:
?xlblahbla
then press Enter
You will see result as number below, that's all
another possible option to pass file name of Excel file to SaveAs expression is
to write them without extension, e.g. "C:\\MyFolder\\MyExcelFille"
Not sure about if it will be work on your release though