Excel DataLink Table Lisp on Named Range

[CLvu]

 Hello,

Let me first start off by apologizing if this topic has already been covered elsewhere, if you would be so kind as to point me into that direction I'd be grateful.

 

 I have found this topic  Excel TAble via Lisp and Excel formatting  quite recently, with a lisp by Juan Villarreal. It does its job well but for an entire Excel sheet, whereas I need it to do the same thing for multiple Named Ranges of a spreadsheet. I believe it's enough for the lisp to only work on one named range, after that I'm thinking I could loop it over and over until it goes through all the Named Ranges(I don't know how efficient that would be, but that's just off the top of my head).

 I tried attaching an Excel file in an attempt to give an example, in it there are  4 named ranges (can be up to hundreds, the number of columns are always the same, the number of rows can differ slightly, the ranges are already created/named). 

 

 

(setq sheetname (vlax-get-property sheet1 "Name"))


(cons 301 (strcat "Data Link\n" DataLinkName "\n" FilePath "\nLink details: Entire sheet: " SheetName))
(cons 302 (strcat FilePath "!" SheetName))


(cons 1 (strcat FilePath "!" SheetName))

 I should mention that I already tried (and failed) modifying the sheetname variable to the range itself (ex.: "A1:C15") and the named ranged (random ex.: "NamedRange1") hoping that would do the trick but it didn't work.

 I tried looking into the (assoc 301) of the created datalink table hoping it would give me some insight into what to change but it just gives me back (301 . CELL_VALUE) which doesn't help much.

 

 I should also mention that this lisp is very complicated for me to begin to understand, but it is very powerful in that it can keep the excel formatting(very important). If you have a better approach to this I would be willing to try it, anything really. This has been bugging me for a while now.

Example.xlsx

[BIGAL]

The sheet name is totally different to range.

 

Something like this, 

(setq myRange (vlax-get-property  (vlax-get-property myxl "ActiveSheet") "Range" cellname))

 

have a look at getexecl.lspgetexecel.lsp

 

 

[CLvu]

Yes, my mistake about the sheetname.

What I have is a list of strings consisting of the named ranges within my excel, an example: ( "NamedRange1" "NamedRange2" "NamedRange3" "NamedRange4" ). I've found this code here somewhere, to help me build the list:

(setq ncount (vlax-get xlnames 'Count))
(while (< i (1+ ncount))
    (setq lst (cons
		(vlax-get-property
		  (vlax-invoke xlnames "Item" i) 'Name) lst))
    (setq i (1+ i))
  );end while

 

This tbl.lsp Tbl.lsp datalinks to the whole excel sheet. I would like it to work for each named range in my list, thus it making 4 tables (as per my list example) instead of 1 big table.

(cons 301 (strcat "Data Link\n" DataLinkName "\n" FilePath "\nLink details: Entire sheet: " SheetName))

The lisp picks the Link entire sheet option. And i do not know what to change in it to make it pick Link to a named range.

 

Or I would also be open to any ideas about a different approach to this. I do not know how to use vla-addtable  (I don't know if it can keep the excel formatting), but if it can work I'll try it

Preemptively I thank you for your time.

[BIGAL]

Sort of got further but beyond my pay level some one else will jump in the last number below was the values in the excel with a named range "Test"

 

 

(setq myRange (vlax-get-property  (vlax-get-property myxl "ActiveSheet") "Range" "Test"))

(setq range (vlax-safearray->list (vlax-variant-value (vlax-get-property myrange 'Value))))

 

((#<variant 5 3>) (#<variant 5 3>) (#<variant 5 4>) (#<variant 5 5>) (#<variant 5 6>) (#<variant 5 8>))

: (nth 0 range)
(#<variant 5 3>)