Interfacing Excel with VLISP 4

[tvdesign]

Hi there,

I was just working on Bill Kramers "Interfacing with Excel Spreadsheets" found in Autodesk Point A newletter.

He has 4 functions that I can't seem to find

xlp-Put-Value
xlp-get-item
xlp-get-value
xlp-get-range

Does anybody know where to find these functions? I've looked everywhere. It's a great article but without these functions, I'm lost.

Any help would be greatly appreciated!!

Thanks,
Kirk

 

[borgunit]

I think that the article you are referring to is the 3rd part of an article. Parts one and two describe the references to these calls. There are links in the article to the other parts.

 

[mikehetherton]

G'day Tvdesign,

I will cut you a deal. If you help me I will help you
I to have been reading all three of Bill's tutorials, But when I go to load this from the visual lisp window it doesn't do anything. What do I have to do to get it to open up Excel??? I enclosed the file below.

Tvdesign you have to import the type library. Which is the lines of code that I have put a (*) next to. You can then come back into the visual lisp editor opp click anywhere on the lisp file and select Apropos Window type in XLP- then select match by prefix. Hit OK. then they should all come up in the little window.




;; Point A
;; A scroll from the Wizard's Lab.
;;
;; Bill Kramer 2002
;;
;; Interface with Excel Worksheet
;;
;; Global Variables
;; xL - Object reference to EXCEL instance
;; myXLWbs - Object reference to EXCEL work books
;; myXLWb1 - Object reference to Work book #1
;; myXLShs - Object reference to Sheets in Work book
;; myXLSh1 - Object reference to Sheet 1
;; myXLNms - Object reference to list of named cells
;;
(vl-load-com)
;
;;-----------------------------------------------
;; Open link to EXCEL spreadsheet, use existing
;; instance of EXCEL if one exists otherwise
;; load EXCEL and then open the file supplied
;; as the variable xLFile.
;;
(defun Link_2_Excel (xLFile)
(setq xL ;Attempt to open existing or create new Excel instance
(vlax-get-or-create-object "Excel.Application&quot)
(if xL ;;success?
(progn
(if (and xLFile (findfile xLFile)) ;;Was a file name supplied?
(progn
;;
;; We have the application object, now we need to tunnel
;; down into a work book.
;;
* (if (null xl-open) ;;Check to see if type library defined
* ;;It is not, import the type library for EXCEL8 * (vlax-import-type-library
* :tlb-filename "C:/Program Files/Microsoft Office/Office/Excel8.olb"
* :methods-prefix "xL-"
* roperties-prefix "xLp-"
* :constants-prefix "xLc-&quot)
;;
;; Now dig into the EXCEL object
(setq myXLWbs (vlax-get xL "Workbooks&quot ;workbooks
myXLWb1 (xl-open myXLWbs xLFile) ;open a workbook CHANGE XLFILE TO FLANGE
myXLShs (vlax-get myXLWb1 "Sheets&quot ;worksheets
myXLSh1 (xl-get-item myXLShs "Sheet1&quot ;worksheet number 1 CHANGE TO FLAT
myXLNms (vlax-get myXLWb1 "Names&quot ;get reserve names
)
)
)
)
)
xL ;;return the object reference
)
;;----------------------------------------
;; Given a name that represents a variable in the EXCEL spreadsheet,
;; send the value in VAL to EXCEL.
;;
;; Note that the linkage to EXCEL MUST be determined before calling
;; this function!
;;
(defun Update_Variable (Varname ;;Variable name
Val ;;Value to write to spreadsheet
/
TMP ;;Named object reference
Rng) ;;Range in spreadsheet (location)
(setq TMP
(vl-catch-all-apply ;;Catch errors if they happen
'vla-item ;;Get the name from the names collection
(list
myXLNMS ;;collection of names from Link_2_Excel
VarName)))
(if (and ;;No errors and item found?
(not (vl-catch-all-error-p TMP))
(setq Loc1 (vlax-get TMP "Value&quot))
(progn ;;yes, get the range of the named item and update the value
(setq Rng (xl-get-range Sh1 Loc1))
(xl-put-value Rng Val)
)
)
)
;;-----------------------------------------------
;; Close the EXCEL spread sheet after saving it.
;;
(defun Close_XL (SaveFileName)
(xl-saveas myXLSh1 SaveFileName)
(xl-close myXLWb1)
)

 

[tvdesign]

Hello Borgunit,
G'day Mike,

OK, I had a look at lesson 3 and I'm still pretty confused. First I'll try to answer your question. Are you "running" the program with a line similar to this from the command line in acad?
(link_2_excel "c:\\lisp\\book1.xls&quot
That should run the program and open up the file in excel.

That's what I'm doing and although Excel does not show up on the taskbar, it must be opening my file because I cannot save a file "over" the top of my book1.xls file in my lisp folder until I do this line: (xl-close myXLWb1)

Anybody know if I should see excel on my taskbar at this point?

I also had to edit the following line:
:tlb-filename
"C:/Program Files/Microsoft Office/Office/Excel8.olb"

And I replaced it with this line:
:tlb-filename
"C:/Program Files/Microsoft Office/Office10/XL5EN32.olb"
Because I'm running Office XP I guess the path and the .olb file are different

Now I have a question for you.
I used the Apropos window and found nothing for xlp- using prefix or any other method. When I look for just xl- I get over 400 results and XL-GET-ITEM is one that shows in the window. But it's nil.......

Are you "stepping" through your code in vlisp?
When I do and I hit this line:
myXLSh1 (xl-get-item myXLShs "Sheet1&quot ;worksheet number 1
I get: ; error: no function definition: XL-GET-ITEM
Do you get the same results?

What do you think?
Thanks for your input!
Kirk

 

[mikehetherton]

Top Stuff Kirk,

(link_2_excel "c:\\lisp\\book1.xls&quot Thats a dream.

Add this line to see Excel on your taskbar. It flashes up then parks itself again.
(vlax-put-property xl "Visible" T)
put this line below this line
(vlax-get-or-create-object "Excel.Application&quot


If you are getting no XLP- in the Apropos window then the type library isn't loading. Copy and paste the following to VLISP Editor then run. It doesn't do anything to the drawing environment. Then once again opposite click etc. etc.

(if (null xl-open) ;;Check to see if type library defined
;;It is not, import the type library for EXCEL8
(vlax-import-type-library
:tlb-filename "C:/Program Files/Microsoft Office/Office/Excel8.olb" ;change this line for XP
:methods-prefix "xL-"
roperties-prefix "xLp-"
:constants-prefix "xLc-&quot)

:Are you "stepping" through your code in vlisp?
:When I do and I hit this line:
:myXLSh1 (xl-get-item myXLShs "Sheet1&quot ;worksheet number 1
:I get: ; error: no function definition: XL-GET-ITEM
o you get the same results?

Kirk I am having major problems around this area also. I am getting a problem with an ActiveX Server and something about an RPC Server also. Will keep you posted.

Cheers
Mike

 

[tvdesign]

Hello again,

OK, now I'm starting to get somewhere with this!
I really like the (vlax-put-property xl "Visible" T)
that you can use to make Excel visible on the taskbar.

First of all I discovered why I was having problems with:

xlp-Put-Value
xlp-get-item
xlp-get-value
xlp-get-range

This may have been common knowlege to VB programmers, but as a LISP guy I had some trouble here.

Office XP (Excel 2002) uses the executable file itself,
as the object library instead of a separate TLB or OLB file as was the case in older versions.

In my code which is pasted in below I added a function:
"DSX-TypeLib-Excel" which does the following to make the program more friendly with different versions of Excel.
I got it off a website that has lots of info that may be helpful.

http://www.dsxcad.com/articles/article003.htm

Check it out!!!

1. looks in several "typical" installation folders and attempts to find the excel installation path to the .olb (object library)

2. set variable excel_ver so I can use the proper Excel functions throughout the program. (eg. xl-get-item is a version 8 function whereas xlp-get-item is version 9 and above) I believe this is correct, although it has not been tested.

-Mike maybe this is a clue to your trouble with ActiveX Server and RPC Server. Not sure, just a thought.

For now here is my code. I do beleive that from here on in I will be able to get this link with Excel figured out. Any info I discover will be posted here. I've added a few "if's" to help trap some errors. Give 'er a go mate and you'll see what I did!

Thanks very much to Mike And Borg for all your help!
This is cool stuff!
Good luck and let me know how it goes.

Cheers! Thanks! Mucho Bueno! (sans spanish spell check)
Kirk

;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
;; Point A
;; A scroll from the Wizard's Lab.
;;
;; Bill Kramer 2002
;;
;; Interface with Excel Worksheet
;;
;; Global Variables
;; xL - Object reference to EXCEL instance
;; myXLWbs - Object reference to EXCEL work books
;; myXLWb1 - Object reference to Work book #1
;; myXLShs - Object reference to Sheets in Work book
;; myXLSh1 - Object reference to Sheet 1
;; myXLNms - Object reference to list of named cells
;;
(prompt "\nLoading VLAX functions... Please Wait: &quot
(vl-load-com)
;
;;-----------------------------------------------
;; Open link to EXCEL spreadsheet, use existing
;; instance of EXCEL if one exists otherwise
;; load EXCEL and then open the file supplied
;; as the variable xLFile.
;;
;;
;;Add this line to see Excel on your taskbar. It flashes up then parks itself again.
;;(vlax-put-property xl "Visible" T)
;;put this line below this line
;;(vlax-get-or-create-object "Excel.Application&quot


(defun Link_2_Excel (xLFile)
;Attempt to open existing or create new Excel instance
(setq xL (vlax-get-or-create-object "Excel.Application&quot)
(vlax-put-property xl "Visible" T);make Excel visible on the taskbar
(if xL ;;success?
(progn
(if (and xLFile (findfile xLFile)) ;;Was a file name supplied?
(progn
;;
;; We have the application object, now we need to tunnel
;; down into a work book.
;;
;;Search for excel installation and return path to .olb or .exe file
(setq xL_lib (DSX-TypeLib-Excel))
(if xL_lib
(progn
;found Excel object library
(if (null xl-open) ;;Check to see if type library defined
;;It is not, import the type library for EXCEL
(progn
(vlax-import-type-library
:tlb-filename xL_lib
:methods-prefix "xL-"
roperties-prefix "xLp-"
:constants-prefix "xLc-&quot
);end progn
);end if
;;
;; Now dig into the EXCEL object
(setq myXLWbs (vlax-get xL "Workbooks&quot) ;workbooks
(setq myXLWb1 (xl-open myXLWbs xLFile)) ;open a workbook
(setq myXLShs (vlax-get myXLWb1 "Sheets&quot) ;worksheets
(if (> 9 excel_ver)
;Excel Version 8
(setq myXLSh1 (xl-get-item myXLShs "Sheet1&quot) ;worksheet number 1
;Excel Version 9 or higher
(setq myXLSh1 (xlp-get-item myXLShs "Sheet1&quot) ;worksheet number 1
);end if
(setq myXLNms (vlax-get myXLWb1 "Names&quot) ;get reserve names
);end progn
(progn
;could not find Excel object library
(alert "Unable to load Excel Object Library! Please check fuction \042 DSX-TypeLib-Excel \042 for path issues!&quot
);end progn
);end if
);end progn
(progn
(alert "Unable to establish link with Excel!&quot
);end progn
);end if
);end progn
);end if
xL ;;return the object reference
);end defun


;;----------------------------------------
;; Given a name that represents a variable in the EXCEL spreadsheet,
;; send the value in VAL to EXCEL.
;;
;; Note that the linkage to EXCEL MUST be determined before calling
;; this function!
;;
(defun Update_Variable (Varname ;;Variable name
Val ;;Value to write to spreadsheet
/
TMP ;;Named object reference
Rng) ;;Range in spreadsheet (location)
(setq TMP
(vl-catch-all-apply ;;Catch errors if they happen
'vla-item ;;Get the name from the names collection
(list
myXLNMS ;;collection of names from Link_2_Excel
VarName)))
(if (and ;;No errors and item found?
(not (vl-catch-all-error-p TMP))
(setq Loc1 (vlax-get TMP "Value&quot))
(progn ;;yes, get the range of the named item and update the value
(if (> 9 excel_ver)
(progn
;Excel Version 8
(setq Rng (xl-get-range Sh1 Loc1))
(xl-put-value Rng Val)
);end progn
(progn
;Excel Version 9 or higher
(setq Rng (xlp-get-range Sh1 Loc1))
(xlp-put-value Rng Val)
);end progn
);end if
);end progn
);end if
);end defun


;;-----------------------------------------------
;; Close the EXCEL spread sheet after saving it.
;;
(defun Close_XL (SaveFileName)
(xl-saveas myXLSh1 SaveFileName)
(xl-close myXLWb1)
);end defun

;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
;;
;; DSX-TypeLib-Excel
;;
;; 1. looks in several "typical" installation folders and attempts
;; to find the excel installation path to the .olb or .exe (object library)
;;
;; 2. set variable excel_ver so I can use the proper Excel function throughout the program
;; (eg. xl-get-item is a version 8 function whereas xlp-get-item is varsion 9 and above)
;;
;; Kirk Paterson 02-04-03 edited from original routine found at
;;

http://www.dsxcad.com/articles/article003.htm

;; check the website above for lots more info about linking Excel and VLISP
;;
;; Note that Excel 2002 uses the executable file itself,
;; instead of a separate TLB or OLB file as was the case in older versions.
;;
;; Also note that this has ONLY been tested with Office XP which uses Excel.exe to store the library
;;
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
(defun DSX-TypeLib-Excel ( / sysdrv tlb)
(setq sysdrv (getenv "systemdrive&quot)
(cond
((setq tlb (findfile (strcat sysdrv "\\Program Files\\Microsoft Office\\Office\\Excel8.olb&quot))
(setq excel_ver 8)
tlb
)
((setq tlb (findfile (strcat sysdrv "\\Program Files\\Microsoft Office\\Office\\Excel9.olb&quot))
(setq excel_ver 9)
tlb
)
((setq tlb (findfile (strcat sysdrv "\\Program Files\\Microsoft Office\\Office\\Excel10.olb&quot))
(setq excel_ver 10)
tlb
)
((setq tlb (findfile (strcat sysdrv "\\Program Files\\Microsoft Office\\Office\\Excel.exe&quot))
(setq excel_ver 10.5)
tlb
)
((setq tlb (findfile (strcat sysdrv "\\Program Files\\Microsoft Office\\Office10\\Excel.exe&quot))
(setq excel_ver 10.5)
tlb
)
);end cond
);end defun

 

[mikehetherton]

G'day Kirk,

Have you had any luck reading the numbers from a cell in Excel yet?

Cheers
Mike

 

[tvdesign]

Hey Mike,

I haven't had time to work on that part yet. Hopefully soon I'll give it a try. How about you... Did you get yours to run?

Thanks,
Kirk

 

[JeffSanders]

Very helpful information listed above.

Has anyone found a way to save the Excel file to the same name without having the dialog box appear to ask you if you want to overwrite the existing file?

 

[mikehetherton]

G'day Jeff,

Have you had any luck reading the numbers in a cell back into Autocad?

Cheers
Mike

 

[JeffSanders]

Mike,
Yes.
I have the code putting the data from a list into Excel.
I also have the code to get the data from Excel and put it into a list.

Let me know if I can help you on any of these items.

The two problems I am having are:

1. A dialog box appears whenever I attempt to save the Excel file. "File already exist. Are you sure you want to overwrite?" I would really like to get rid of that box!

2. I have a user that receives an error trying to get the data from excel using my code. His Excel object libraries are the same as mine. Same name. Same directory. Same version. He recieves the error:

----------------Start of error messages----------------
Last number generated was - 9
Last number generated was - 10
Last number generated was - 11
You are using Excel Version :9
; error: Automation Error. Description was not provided.

----------------End of error messages----------------

The code I use is basically the same as Kramer's with a few modifications including the ones stated above. The code generating the numbers looks like this:

----------------Start of code------------------------

(princ "\nLast number generated was - 7&quot

(setq myWbooks (vlax-get xL "Workbooks&quot)

(princ "\nLast number generated was - 8&quot

(if (> (vla-get-count myWbooks) 0)
(setq myWbook
(xlp-get-item myWbooks
(strcat
(vl-filename-base fileName)
(vl-filename-extension fileName)
)
)
)
)

(princ "\nLast number generated was - 9&quot

(if (null myWbook)
(setq myWbook (xl-open myWbooks fileName))
)

(princ "\nLast number generated was - 10&quot

(setq myShts (vlax-get myWbook "Sheets&quot)

(princ "\nLast number generated was - 11&quot

(princ "\n You are using Excel Version :&quot
(princ excel_ver)

(if (> 9 excel_ver)
;Excel Version 8
(setq mySht (xl-get-item myShts "Sheet1&quot)
;Excel Version 9 or higher
(setq mySht (xlp-get-item myShts "Sheet1&quot)
)

(princ "\nLast number generated was - 15&quot

(setq mySht (xlp-get-item myShts 1))

(princ "\nLast number generated was - 16&quot

----------------End of code------------------------


The last number generated was 11. I'll check to make sure that myShts was not set to nil but looks like the error is in this line:

(setq mySht (xlp-get-item myShts "Sheet1&quot)

Any ideas?

Thanks,
Jeff Sanders

 

[JeffSanders]

I solved the problem.

Thanks,
Jeff Sanders

 

[tvdesign]

Hi Jeff,

Don't know if you have figured out how to get around the overwrite existing file problem. One thing you may want to try is to save the file as a new file name. I have not tried this but think it's worth a shot.

I was working on this code the other night and hit a stumbling block. Like the error your user described...

; error: Automation Error. Description was not provided.

I tried some code I found on another site and get the same errors. Looks like from your post that you've figured out how to get this to work properly. I'd be interested in looking at your code and giving it a try with office xp to see if I can get past this error. If you'd be kind enough to post your code, that would be great.

Thanks,
Kirk

 

[zliazmei]

Hi there I made something like tis a few weeks ago plese use the following code and give me your opinion!

It will input the block called point in your dwg in the points with coordinates x,y with atributes Asx nad ASy taken from xls file.

1. create block Point.dwg usinf wblock function
this block must have atributes Asx and Asy definined in this order.and paste it in your acad/support folder
2. create xls file with 4 columns x,y Asx ASy.
3. Use this to create a dcl file called Zbl2DXLS.DCL in you acad suport folder.

Zbl2DXLS: dialog {
label = "INPUT BLOCKS FROM FILE";
width=5;
: boxed_row {
label=" Chose Input file !";
: edit_box { // defines the INPUT FILE edit box
label = "";
key = "path1";
edit_width = 40;
fixed_width = true;
alignment =left;
}
: button {// defines the INPUT FILE BUTON
alignment=right;
fixed_height=true;
key="BROW";
label= "BROWSE";
fixed_width=true;
}
}
: boxed_radio_row { // defines the radio button areas
label = "What is the Measure Unit of Coordinates in Your File?";
fixed_width = true;
alignment = centered;
children_alignment = centered;
: radio_button { // defines the METERS radio button
label = "METERS";
key = "ME";
fixed_width =true;
}

: radio_button { // defines the CENTIMETERS radio button
label = "CENTIMETERS";
key = "CE";
fixed_width = true;
}
: radio_button { // defines the MILIMRETRS radio button
label = "MILIMETERS";
key = "MI";
fixed_width =true;
}


}
: column{
:boxed_row {
alignment = centered;
children_alignment = left;
fixed_width=true;
label = "Scale Factors to Apply in your DWG";
:column{
: text {
alignment=centered;
is_bold= true;
height=0.8;
label= "Set DWG Scale : 1/?";
}

: edit_box { // defines the dRAWING sCALE edit box
key = "DWSC";
edit_width = 4;
fixed_width = true;
alignment=centered;
}
}
: spacer { width =0.1; }
:column{
: text {
alignment=centered;
is_bold= true;
height=0.8;
label = "Set Block Scale:";
}
: edit_box { // defines the Block Scale edit box
key = "BSC";
edit_width =4;
fixed_width =true;
alignment=centered;
}
}
}
:boxed_row {
alignment = centered;

fixed_width =true;
label = "Position of First Block in your DWG.";
:row{
: edit_box { // defines the ROW edit box
label = "ROW";
key = "ROW1";
edit_width = 4;
alignment = left;
fixed_width =true;
}
: spacer { width =0.1; }
: edit_box { // defines the COLUMN Scale edit box
label = "COLUMN";
key = "COLUMN1";
edit_width =4;
alignment = right;
fixed_width =true;
}
}
}
}

: boxed_row {
label=" Select ZERO Point !";
: edit_box { // defines the ZERO POINTXcoord edit box
label = "X:";
key = "ZEROX";
edit_width = 10;
fixed_width = true;
alignment =left;
}
: edit_box { // defines the ZERO POINTYcoord edit box
label = "Y:";
key = "ZEROY";
edit_width = 10;
fixed_width = true;
alignment =left;
}
: edit_box { // defines the ZERO POINTZcoord edit box
label = "Z:";
key = "ZEROZ";
edit_width = 10;
fixed_width = true;
alignment =left;
}
: button {// defines the ZERO POINT PICK BUTON
alignment=right;
fixed_height=true;
key="PICK";
label= "PICK";
fixed_width=true;
mnemonic = "P";

}
}
: row { // defines the OK/Cancel button row
width=15;
: spacer { width =0.02;}
: button { // defines the OK button
label = "OK";
is_default = true;
key = "accept";
width = 2.5;
}
: spacer { width =0.01; }
: button { // defines the Cancel button
label = "Cancel";
is_cancel = true;
key = "cancel";
width = 2.5;

}
: spacer { width =0.02;}
}
}

4. Use the folowing to create the lisp routine ZMEIblock2dfromXLS.lsp

(princ "\nZMEiblock2DfromXLS - LOADED&quot
(princ "\nStart with 'ZBL2DXLS'&quot
(vl-load-com)
(defun vlxx-TypeLib-Excel9 ()
(findfile
(strcat
(getenv "SYSTEMDRIVE&quot ; Windows NT and 2000, example: "C:"
"\\Program Files\\Microsoft Office\\Office\\Excel9.olb" ; OFFICE 2000 OLB file
) ;_ end of strcat
) ;_ end of findfile
) ;_ end of defun



(defun vlxx-Load-TypeLib-Excel9 (/ tlbfile out)
(cond
((null msxl-xl24HourClock)
(if (setq tlbfile (vlxx-TypeLib-Excel9))
(progn
(princ "\nInitializing Microsoft Excel 2000...&quot
(vlax-Import-Type-Library
:tlb-filename tlbfile :methods-prefix
"msxl-" roperties-prefix "msxl-"
:constants-prefix "msxl-"
) ;_ end of vlax-Import-Type-Library
;_ end of vlax-Import-Type-Library
;_ end of vlax-Import-Type-Library
;_ end of vlax-Import-Type-Library
(if msxl-xl24HourClock
(setq out T)
) ;_ end of if
) ;_ end of progn
) ;_ end of if
)
(T (setq out T))
) ;_ end of cond
out
) ;_ end of defun

;;; Note: <xfile> is the fully-qualified filename to be opened,
;;; <dmode> is either &quot;SHOW&quot; or &quot;HIDE&quot; depending upon whether
;;; you want the Excel session to be accessible to the user directly.
(defun vlxx-Open-Excel9-Exist (xfile dmode / appsession)
(princ &quot;\nOpening Excel Spreadsheet file...&quot
(cond
((setq appsession (vlax-Get-or-Create-Object &quot;Excel.Application.9&quot)

(vlax-invoke-Method
(vlax-Get-Property appsession 'WorkBooks)
'Open
xfile
) ;_ end of vlax-invoke-Method
(if (= (strcase dmode) &quot;SHOW&quot
(vla-Put-Visible appsession 1)
(vla-Put-Visible appsession 0)
) ;_ end of if
)
) ;_ end of cond
appsession
) ;_ end of defun


;;; Get cell object relative to range using <relrow> and <relcol>

(defun vlxx-Excel-Get-Cell (rng relrow relcol)
(vlax-Variant-Value
(msxl-Get-Item
(msxl-Get-Cells rng)
(vlax-Make-Variant relrow)
(vlax-Make-Variant relcol)
) ;_ end of msxl-Get-Item
) ;_ end of vlax-Variant-Value
) ;_ end of defun

;;; Return value of contents in cell (row, col)

(defun vlxx-Excel-Get-CellValue (row col)
(vlax-Variant-Value
(msxl-Get-Value
(vlxx-Excel-Get-Cell
(msxl-Get-ActiveSheet xlapp)
row
col
) ;_ end of vlxx-Excel-Get-Cell
) ;_ end of msxl-Get-Value
) ;_ end of vlax-Variant-Value
) ;_ end of defun


;;; Return a list of cell values for a given row

(defun vlxx-Excel-Get-RowValues
(row startcol numcells / next out)
(setq next startcol)
(repeat numcells
(setq out (if out
(append out (list (vlxx-Excel-Get-CellValue row next)))
(list (vlxx-Excel-Get-CellValue row next))
) ;_ end of if
next (1+ next)
) ; setq
) ; repeat
out
) ;_ end of defun

;;; Return a Number of Non NILL values for a given COLUMN
;;;starting from a given ROW

(defun vlxx-Excel-Get-ColumnValues
(col startrow / next out)
(setq next startrow)
(setq out (vlxx-Excel-Get-CellValue next col))
(princ out)
(while (/= out nil)
(setq out (vlxx-Excel-Get-CellValue next col)
next (1+ next)
block_conter (- (- next 1) startrow)
) ;_ end of setq
) ;_ end of while
(princ &quot;\nBlock Definitions Found - &quot
block_conter
) ;_ end of defun

;;; Return a list of lists where each sublist is one row of information
;;; in a top-to-bottom order in the spreadsheet range
;;;
;;; (vlxx-Excel-GetRangeValues-ByRows sRow sCol nRows nCols)

(defun vlxx-Excel-GetRangeValues-ByRows
(startrow startcol numrows numcols / nextrow rowlst outlst)
(setq nextrow startrow)
(repeat numrows
(setq rowlst (vlxx-Excel-Get-RowValues nextrow startcol numcols)
outlst (if outlst
(append outlst (list rowlst))
(list rowlst)
) ;_ end of if
nextrow (1+ nextrow)
) ;_ end of setq
) ;_ end of repeat
outlst
) ;_ end of defun
(defun C:ZBL2DXLS ()
(setq dcl_id (load_dialog &quot;Zbl2DXLS.dcl&quot) ;Load the dialog box.

(setq what_next 5)
(setq cnt 1)
(setq
dialogLoaded T
dialogShow T

dwg_path (getvar &quot;DWGPREFIX&quot

INUNITS &quot;METER&quot;
METERYES &quot;1&quot;
CENTIMETERYES &quot;0&quot;
MILIMETERYES &quot;0&quot;

DWSC1 50
BSC1 20

beginROW 1
beginColumn 1

Xoo &quot;0.0000&quot;
Yoo &quot;0.0000&quot;
Zoo &quot;0.0000&quot;
ZEROPOINT (list Xo Yo Zo)

) ;_ end of setq
(while (>= what_next 5) ;Begin display loop.
(if (null (new_dialog &quot;Zbl2DXLS&quot; dcl_id)) ;Initialize dialog
(exit) ;box, exit if nil
) ;_ end of if

(set_tile &quot;path1&quot; dwg_path)

(set_tile &quot;ME&quot; METERYES)
(set_tile &quot;CE&quot; CENTIMETERYES)
(set_tile &quot;MI&quot; MILIMETERYES)

(set_tile &quot;DWSC&quot; (rtos DWSC1))
(set_tile &quot;BSC&quot; (rtos BSC1))

(set_tile &quot;ROW1&quot; (rtos beginRow))
(set_tile &quot;COLUMN1&quot; (rtos beginColumn))

(set_tile &quot;ZEROX&quot; Xoo)
(set_tile &quot;ZEROY&quot; Yoo)
(set_tile &quot;ZEROZ&quot; Zoo)

(action_tile
&quot;ME&quot;
&quot;(setq INUNITS \&quot;METER\&quot&quot;
) ;_ end of action tile
(action_tile
&quot;CE&quot;
&quot;(setq INUNITS \&quot;CENTIMETER\&quot&quot;
) ;_ end of action tile
(action_tile
&quot;MI&quot;
&quot;(setq INUNITS \&quot;MILIMETER\&quot&quot;
) ;_ end of action tile

(action_tile
&quot;BROW&quot;
(strcat
&quot;(progn (set_tile \&quot;path1\&quot; (getfiled \&quot;Get file with data\&quot; (getvar \&quot;DWGPREFIX\&quot \&quot;xls&quot;
&quot;\&quot; 4)))&quot;
) ;_ end of strcat
) ;_ end of action_tile

(action_tile
&quot;cancel&quot;
(strcat &quot;(progn (setq UserClick nil) (done_dialog 4))&quot
) ;_end of action tile

(action_tile
&quot;accept&quot;
(strcat
&quot;(progn (setq DWGSCALE (get_tile \&quot;DWSC\&quot)&quot;
&quot;(setq filename (get_tile \&quot;path1\&quot)&quot;
&quot;(setq block_scale (get_tile \&quot;BSC\&quot)&quot;
&quot;(setq ROW (get_tile \&quot;ROW1\&quot)&quot;
&quot;(setq COLUMN (get_tile \&quot;COLUMN1\&quot)&quot;
&quot;(setq Xo (get_tile \&quot;ZEROX\&quot)&quot;
&quot;(setq Yo (get_tile \&quot;ZEROY\&quot)&quot;
&quot;(setq Zo (get_tile \&quot;ZEROZ\&quot)&quot;
&quot;(setq ZEROPOINT (list Xo Yo Zo))&quot;
&quot;(setq UserClick T)&quot;
&quot;(done_dialog 3))&quot;
) ;_ end of strcat
) ;_ end of action_tile
(action_tile
&quot;PICK&quot;
(strcat
&quot;(progn (setq dwg_path (get_tile \&quot;path1\&quot)&quot;
&quot;(setq METERYES (get_tile \&quot;ME\&quot)&quot;
&quot;(setq CENTIMETERYES (get_tile \&quot;CE\&quot)&quot;
&quot;(setq MILIMETERYES (get_tile \&quot;MI\&quot)&quot;
&quot;(setq DWSC1 (atoi (get_tile \&quot;DWSC\&quot))&quot;
&quot;(setq BSC1 (atoi (get_tile \&quot;BSC\&quot))&quot;
&quot;(setq beginRow (atoi (get_tile \&quot;ROW1\&quot))&quot;
&quot;(setq beginColumn (atoi (get_tile \&quot;COLUMN1\&quot))&quot;
&quot;(done_dialog 6))&quot;
) ;_ end of strcat
) ;_ end of action_tile

(setq what_next (start_dialog)) ;Display dialog box
(cond
((= what_next 6)
(progn
(setq ZERO (getpoint &quot;\n Pick a ZERO point: &quot)
(setq Xoo (rtos (car ZERO) 2 4))
(setq Yoo (rtos (cadr ZERO) 2 4))
(setq Zoo (rtos (caddr ZERO) 2 4))
) ;_ end of progn
)
((= what_next 3)
(done_dialog)
)
((= what_next 4)
(done_dialog)
)
) ;_ end of cond
) ;_ end of while

(unload_dialog dcl_id)

(if
UserClick ; User clicked Ok
(progn
(setq cmdecho1 (getvar &quot;cmdecho&quot)
(setvar &quot;cmdecho&quot; 0)
(setq osmode1 (getvar &quot;osmode&quot)
(setvar &quot;osmode&quot; 0)
(setq ATTREQ1 (getvar &quot;ATTREQ&quot)
(setvar &quot;ATTREQ&quot; 1)
(princ (strcat &quot;\nInput File Set To:&quot; filename))
(princ &quot;\nZERO point set to:&quot
(princ ZEROPOINT)
(princ (strcat &quot;\nInput Units set to: &quot; INUNITS))
(if (= INUNITS &quot;METER&quot
(setq SCALE 1000)
(if (= INUNITS &quot;CENTIMETER&quot
(setq SCALE 10)
(setq SCALE 1)
) ;_ end of if
) ;_ end of if
(Princ (strcat &quot;\nDrawing Scale factor set to: 1/&quot;
DWGSCALE
) ;_ end of strcat
) ;_ end of Princ
(Princ (strcat &quot;\nBlock Scale factor set to: 1/&quot;
block_scale
) ;_ end of strcat
) ;_ end of Princ
(cond
((vlxx-Load-TypeLib-Excel9)
(cond
((setq xlfile filename)
(setq G$XFILE xlfile)
(cond
((setq xlapp (vlxx-Open-Excel9-Exist xlfile &quot;HIDE&quot)
(setq ash (msxl-Get-ActiveSheet xlapp))
(setq range (msxl-Get-ActiveCell xlapp))

;;; Starts non NIL Rows counter &quot;VLXX-EXCEL-GET-COLUMNVALUES&quot;
;;; ACCORDING TO VALUES FOR START ROW AND
;;; START COLUMN TAKEN FROM THE DCL WINDOW
;;; Assigns the Block_Counter value to &quot;Patka&quot; variable

(setq COLUMN2 (atoi COLUMN)
ROW2 (atoi ROW)
) ;_ end of setq
(setq patka (vlxx-Excel-Get-ColumnValues COLUMN2 ROW2))
(princ patka)

;;; Starts &quot;vlxx-Excel-GetRangeValues-ByRows&quot;
;;; ACCORDING TO VALUES OF ROW2 COLUNM2 for number of Rows determined by row Counter
;;; &quot;VLXX-EXCEL-GET-COLUMNVALUES&quot;
;;; Returns Xllist variable - Block definitions from spredsheet

(setq Columns 5)
(setq Rows patka)
(setq
xlist (vlxx-Excel-GetRangeValues-ByRows
ROW2
COLUMN2
Rows
Columns
) ;_ end of vlxx-Excel-GetRangeValues-ByRows
) ;_ end of setq

;;; Print out each sublist of &quot;XLIST&quot; to see what you got...
(princ &quot;\nIserting Blocks: \n&quot
(foreach mbr xlist
(progn (princ mbr)
(setq Pt_name (if (numberp (nth 0 mbr))
(rtos (nth 0 mbr))
(nth 0 mbr)
) ;_ end of if
Xcoord
(nth 1 mbr)
Ycoord
(nth 2 mbr)
Asx
(if (numberp (nth 3 mbr))
(rtos (nth 3 mbr))
(nth 3 mbr)
) ;_ end of if
Asy
(if (numberp (nth 4 mbr))
(rtos (nth 4 mbr))
(nth 4 mbr)
) ;_ end of if
Pt_coord
(list
(+ (atof Xo)
(/ (* SCALE Xcoord)
(atof DWGSCALE)
) ;_ end of /
) ;_ end of +
(+ (atof Yo)
(/ (* SCALE Ycoord)
(atof DWGSCALE)
) ;_ end of /
) ;_ end of +
) ;_ end of list
) ;_ end of setq
(command &quot;_-insert&quot;
;inserting block
&quot;point&quot; ; block name
Pt_coord
; block input coordinates
block_scale ; block scale factor
&quot;&quot; ; block rotation angle
&quot;&quot;
Pt_name ; Atribute Point name
Asx ; atribute Asx
Asy ; atribute Asy

) ;_ end of command
) ;_ end of progn
(terpri)
) ;_ end of foreach
(setq xlist nil)
(vlxx-Excel-Quit xlapp)
)
(T (princ &quot;\nFailed to start application session.&quot)
) ;_ end of cond
)
) ;_ end of cond
)
(T (alert &quot;Failed to initialize type library for Excel 2000...&quot)
) ;_ end of cond
(setvar &quot;cmdecho&quot; cmdecho1)
(setvar &quot;osmode&quot; osmode1)
(setvar &quot;ATTREQ&quot; ATTREQ1)
(princ)
) ;_ end of progn
) ;_ end of if USERCLICK
) ;_ end of defun



;;; Quit Excel and Close

(defun vlxx-Excel-Quit (appsession)
(vlax-Invoke-Method appsession 'QUIT)
(if (vlax-Property-Available-p appsession 'Visible)
(princ &quot;\nExcel Application still active!&quot
(princ &quot;\nExcel Application has been closed.&quot
) ;_ end of if
) ;_ end of defun

;;; Force any open session of Excel to close

(defun vlxx-Excel-Kill ()
(while (vlax-Get-Object &quot;Excel.Application.9&quot
(vlxx-Excel-Quit (vlax-Get-Object &quot;Excel.Application.9&quot)
) ;_ end of while
) ;_ end of defun

5. NOE the excel9.olb(office2000) in definition VLXX-TypeLib-Excel9 must be changed to Excel8.olb for office 97 or to XL5EN32.olb for office XP. There is a simple way to make it to work automaticy.

HOPE THIS WILL HELP.

ZMEI

 

[tvdesign]

Thanks ZMEI,

I don't have time to test your code to see if it works for me at the moment. I use office xp and figured out that you need to use excel.exe instead of xl5en32.olb to load the library. I'm not sure why they include xl5en32.olb with xp, maybe it's some sort of backwards compatablity thing, but I think that was excel 95's .olb file

I'll give your code a whirl tomorrow and let you know how it goes.

Thanks,
Kirk