in reply to Using Win32::OLE and Excel - Tips and Tricks

What's the advantage to the method you propose for finding the last column and row as opposed to something more like 
# Assuming all variables declared in a "my" statement above.
# Also assuming $worksheet is pointing to a WorkSheet object.
$last_cell = $worksheet->Range("A1")->EntireColumn->
    SpecialCells(xlCellTypeLastCell)->{Address};
($last_col, $last_row) = $last_cell =~ /\$(\w+)\$(\d+)/;

[download]
?

(Note that the last cell returned by this method does not depend on the Range("A1") specified -- it still returns the last cell properly. For example, N23 (as text $N$23) if N23 were the last cell in the sheet.)

Replies are listed 'Best First'.
Re: Last cell?
by cacharbe (Curate) on Jan 28, 2003 at 19:38 UTC

    Upon further investigation, I have found that this is not reliable if the columnar data is not of equal row size. It returns the last row of the right most column, which is incorrect in some cases.

    The methods I used in the examples return the last row (over all) and the last column (over all) vs finding the location of the last cell in the last column

    So, if the data on the worksheet is symmetric, your function works faster, however yours would not be reliable to, say, put edges around all the cells in a data set that has columns of different row sizes

    C-.

    ---
    Flex the Geek

[reply]
      If the method you'd come up originally doesn't work fine with non "square" sheets then what would you recommend.
      Furthermore, I can't seem to be able to make your original last row/column script work. The dos prompt tells me the following :
      Bareword "xlPrevious" not allowed while "strict subs" in use at D:\sou
+rce\readExcel.pm line 30.
Bareword "xlByRows" not allowed while "strict subs" in use at D:\sourc
+e\readExcel.pm line 30.
Bareword "xlPrevious" not allowed while "strict subs" in use at D:\sou
+rce\readExcel.pm line 33.
Bareword "xlByColumns" not allowed while "strict subs" in use at D:\so
+urce\readExcel.pm line 33.

      [download]
      What should I do ?

      edit (broquaint): changed <pre> to <code> tags

[reply]
[d/l]
      Sorry about the previous post - I found out what wasn't working about the xlPrevious and I included the 
      use Win32::OLE::Const 'Microsoft Excel';
      line. Thanks again and great tutorial. How about PowerPoint someday ?
[reply]

        That's actaully in the works. It's a little more hairy than Excel (which is actually the easiest of the Office suites, aside from maybe Outlook). I will probably post a MSWord and Powerpoint both over the next few months.

        I haven't had to do create a powerpoint specific project yet, so most of what I have is just snippets, but I've cranked out a couple of word related projects, and I'm trying to distill them down into a workable tutorial.

        Stay tuned.

        C-.

        ---
        Flex the Geek

[reply]

      Interesting. I don't seem to have run into this, but I wasn't explicitly looking for it, either. I'll have to do some more research on the matter.

[reply]
Re: Last cell?
by cacharbe (Curate) on Dec 09, 2002 at 17:56 UTC
    TMTOWTDI

    Actually, yours is probably faster.

    C-.

    ---
    Flex the Geek

[reply]
      What is the best way to determine all of the valid fields and subfields for Perl/OLE/Excel information. It is only by studying examples did I learn that Cell->Interior->ColorIndex gets the cell background color, and everything I have learned is by Web examples. Is there a definitive document for all of the possible fields?
[reply]

        The reference document is the object reference and definition. You can "Browse" it via the tool written by Jan DuBois that I mentioned in the preface of this tutorial.

        <PERLROOT>\html\site\lib\Win32\OLE\Browswer\Browser.html

        Point it at the Microsoft Excel XX.XX objects and browse until your heart's content.

        C-.

        ---
        Flex the Geek

[reply]

In Section Tutorials