Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl-Sensitive Sunglasses
 
PerlMonks  

Last cell?

by Nkuvu (Priest)
on Dec 06, 2002 at 21:58 UTC ( #218164=note: print w/ replies, xml ) Need Help??


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+)/;
?

(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.)


Comment on Last cell?
Download Code
Re: Last cell?
by cacharbe (Curate) on Dec 09, 2002 at 17:56 UTC
    TMTOWTDI

    Actually, yours is probably faster.

    C-.

    ---
    Flex the Geek

      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?

        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

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

      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.

      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.
      What should I do ?

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

      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 ?

        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

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://218164]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others perusing the Monastery: (7)
As of 2014-08-29 10:24 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    The best computer themed movie is:











    Results (278 votes), past polls