Topic: eof in excel sheet

[Amsterdammed]

Hello there,

is there a vlisp way to find out he eof in an excel sheet, something like

Code: [Select]

(vlax-get-property xlsheet 'EOF)

?


Thanks

bernd

[Lee Mac]

I think you need to use the CurrentRegion property of the Range object.

[Amsterdammed]

how do i get that range object? if i look at the properties of the sheet itself i don't see any? I'm not very good in that vlax and so ........

[irneb]

Or you can simply take the Cells property of each WorkSheet in the Sheets collection of the Application to get the currently active Workbook's Sheets' Ranges. Or if the XLS(x) file is not the active one, first get the Workbook object from the Application's Workbooks collection.

[Lee Mac]

how do i get that range object? if i look at the properties of the sheet itself i don't see any? I'm not very good in that vlax and so ........

Here is a quickly written example:

Code - Auto/Visual Lisp: [Select]

1. ;; Get Used Excel Range Example  -  Lee Mac 2012
2. ;; Example to demonstrate how to obtain the used range of cells
3. ;; in the active worksheet of a selected Excel file (xls / xlsx)
4. ;;
5. ;; If successful, returns a list of (<rows> <columns>) identifying
6. ;; the range of cells holding a value from cell A1.
7.  
8. (defun c:getusedrangeexample ( / err file xlapp xlcol xlreg xlrng xlrow xlsht xlwbk xlwbs )
9.     (if (setq file (getfiled "Select Excel File" "" "xls;xlsx" 16))
10.         (if (setq xlapp (vlax-get-or-create-object "excel.application"))
11.             (progn
12.                 (setq err
13.                     (vl-catch-all-apply
14.                         (function
15.                             (lambda ( )
16.                                 (setq xlwbs (vlax-get-property  xlapp 'workbooks)
17.                                       xlwbk (vlax-invoke-method xlwbs 'open file)
18.                                       xlsht (vlax-get-property  xlapp 'activesheet)
19.                                       xlrng (vlax-get-property  xlsht 'range "A1")
20.                                       xlreg (vlax-get-property  xlrng 'currentregion)
21.                                       xlrow (vlax-get-property  xlreg 'rows)
22.                                       xlcol (vlax-get-property  xlreg 'columns)
23.                                 )
24.                                 (list
25.                                     (vlax-get-property xlrow 'count)
26.                                     (vlax-get-property xlcol 'count)
27.                                 )
28.                             )
29.                         )
30.                     )
31.                 )
32.                 (if xlwbk (vlax-invoke-method xlwbk 'close :vlax-false))
33.                 (vlax-invoke-method xlapp 'quit)
34.                
35.                 (foreach obj (list xlcol xlrow xlreg xlrng xlsht xlwbk xlwbs xlapp)
36.                     (if (eq 'vla-object (type obj))
37.                         (vlax-release-object obj)
38.                     )
39.                 )
40.                 (gc)
41.                 (if (vl-catch-all-error-p err)
42.                     (prompt (strcat "\nError: " (vl-catch-all-error-message err)))
43.                     err
44.                 )
45.             )
46.             (prompt "\nUnable to interface with Excel Application.")
47.         )
48.     )
49. )
50. (vl-load-com) (princ)

[Amsterdammed]

Great!
but what if i don't know which column is the one with the biggest range in a sheet?
I mean, now we look down the A1 all the way, but what if column A is not filled all the way down?

[Amsterdammed]

or do i see this wrong and the range is over all columns?

[Lee Mac]

or do i see this wrong and the range is over all columns?

The range is over all rows and columns, the result is measured from cell 'A1'

[irneb]

The range is always going to give you the overall range. It's the same as if you moved the cursor to A1 and pressed Ctrl+Shift+End inside Excel.

[Amsterdammed]

Strange. doesn't work like that here (please see attached xlsm). Funny enough, when i set the range to BY1 (that is the usual suspect in my file for the longest column, but not good enough to assume it is always the correct one)

the code returns the correct number of rows and colls, if i use the A1 as range start, it returns (1 3)

[irneb]

The method Lee explains is selecting the group around the A1 cell. E.g. both these give the same result:

Code: [Select]

(defun c:getusedrangeexample ( / err file xlapp xlcol xlreg xlrng xlrow xlsht xlwbk xlwbs )
   (if (setq file (getfiled "Select Excel File" "" "xls;xlsx" 16))
       (if (setq xlapp (vlax-get-or-create-object "excel.application"))
           (progn
               (setq err
                   (vl-catch-all-apply
                       (function
                           (lambda ( )
                               (setq xlwbs (vlax-get-property  xlapp 'workbooks)
                                     xlwbk (vlax-invoke-method xlwbs 'open file)
                                     xlsht (vlax-get-property  xlapp 'activesheet)
                                     xlrng (vlax-get-property  xlsht 'cells)
                                     xlrow (vlax-get-property  xlrng 'rows)
                                     xlcol (vlax-get-property  xlrng 'columns)
                               )
                               (list
                                   (vlax-get-property xlrow 'count)
                                   (vlax-get-property xlcol 'count)
                               )
                           )
                       )
                   )
               )
               (if xlwbk (vlax-invoke-method xlwbk 'close :vlax-false))
               (vlax-invoke-method xlapp 'quit)
 
               (foreach obj (list xlcol xlrow xlreg xlrng xlsht xlwbk xlwbs xlapp)
                   (if (eq 'vla-object (type obj))
                       (vlax-release-object obj)
                   )
               )
               (gc)
               (if (vl-catch-all-error-p err)
                   (prompt (strcat "\nError: " (vl-catch-all-error-message err)))
                   err
               )
           )
           (prompt "\nUnable to interface with Excel Application.")
       )
   )
)

Code: [Select]

(defun c:getusedrangeexample ( / err file xlapp xlcol xlreg xlrng xlrow xlsht xlwbk xlwbs)
   (if (setq file (getfiled "Select Excel File" "" "xls;xlsx" 16))
       (if (setq xlapp (vlax-get-or-create-object "excel.application"))
           (progn
               (setq err
                   (vl-catch-all-apply
                       (function
                           (lambda ( )
                               (setq xlwbs (vlax-get-property  xlapp 'workbooks)
                                     xlwbk (vlax-invoke-method xlwbs 'open file)
                                     xlsht (vlax-get-property  xlapp 'activesheet)
                                     xlrng (vlax-get-property  xlsht 'UsedRange)
                                     xlrow (vlax-get-property  xlsht 'Rows)
                                     xlcol (vlax-get-property xlrng 'Columns)
                               )
                               (list
                                   (vlax-get-property xlrow 'count)
                                   (vlax-get-property xlcol 'count)
                               )
                           )
                       )
                   )
               )
               (if xlwbk (vlax-invoke-method xlwbk 'close :vlax-false))
               (vlax-invoke-method xlapp 'quit)
 
               (foreach obj (list xlcol xlrow xlreg xlrng xlsht xlwbk xlwbs xlapp)
                   (if (eq 'vla-object (type obj))
                       (vlax-release-object obj)
                   )
               )
               (gc)
               (if (vl-catch-all-error-p err)
                   (prompt (strcat "\nError: " (vl-catch-all-error-message err)))
                   err
               )
           )
           (prompt "\nUnable to interface with Excel Application.")
       )
   )
)Though in your sample XLSX I get (1048576 16348) ... which I think is larger than it should be

[Amsterdammed]

Yes, the rows were 1337 and cols 187.