[Wtr-general] Working with Excel - how to access the last cell with data in the spreadsheet?

[Charley Baker]

Chris is right, that's a great site and they do have a link to the vba help
files which should help with the underlying object model. I've used the
usedrange property of the worksheet ole object for rows and columns:
rowcount = @worksheet.usedrange.rows.count
columncount = @worksheet.usedrange.columns.count

Otherwise you can set a marker, look for blank cells, etc. Hope this helps,
I'm not using excel now, too many disadvantages.

Cheers,

Charley

On 5/12/06, Chris McMahon <christopher.mcmahon at gmail.com> wrote:
>
> You should look this over:
> http://www.rubygarden.org/ruby?ScriptingExcel.  It's the best
> reference I know for this sort of thing.
>
> That said, this might be what you want:
>
> require 'win32ole'
> #find the right-most column on the worksheet in question
>         @column_number = @sheet.range("b2").end(-4161).Column
>         #puts @column_number
>
> -Chris
>
>
> On 5/12/06, Praveen Kumar Kammitta <pkammit at gmail.com> wrote:
> > Hi everyone, I have a question regarding accessing the last cell with
> data
> > in Excel.
> > One of my Watir scripts captures the text of a link in a page and writes
> it
> > to a spreadsheet.
> > Here is the code:
> >
> > timeSpreadsheet1 = File.new(("DataSheet") + ".xls", "w") # # File will
> be
> > opened in overwrite mode.
> > timeSpreadsheet1.puts $ie.link(:index, 24).innerText
> >
> > Another Watir script which I run subsequently accesses the data written
> to
> > the spreadsheet.
> > Here is the code I use in this script:
> >
> > #Open the Spreadsheet and grab the Opportunity Number.
> >         excel = WIN32OLE::new('excel.Application')
> >         workbook =
> > excel.Workbooks.Open('C:\RegressionSuite\DataSheet.xls')
> >         worksheet = workbook.Worksheets(1) #get hold of the first
> worksheet
> >         worksheet.Select
> >         $data = worksheet.Range('a1')['text'] # The data is in cell a1.
> >         excel['Visible'] = false # hide the spreadsheet from view
> >         workbook.close
> >         excel.Quit
> >
> > # Insert the data read from the spreadsheet into the text field
> > $ie.text_field(:name, 'TEST').set $data
> >
> > My script works fine and I have been able to execute my tests without
> > issues.
> > But what I would like to do is to write the data in append mode instead
> of
> > overwrite mode(in my first script).
> > Then, in my second script I would like to instruct Watir to read data
> from
> > the last cell in 'a' instead of from a1, which I am currently doing.
> > For example, if I ran the first script 4 times, a4 would be the last
> cell
> > with data in the spreadsheet. How do I instruct Watir to grab data
> > from the last cell(which in this case is a4) ?
> >
> >
> >
> >
> >
> > _______________________________________________
> > Wtr-general mailing list
> > Wtr-general at rubyforge.org
> > http://rubyforge.org/mailman/listinfo/wtr-general
> >
> >
>
> _______________________________________________
> Wtr-general mailing list
> Wtr-general at rubyforge.org
> http://rubyforge.org/mailman/listinfo/wtr-general
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://rubyforge.org/pipermail/wtr-general/attachments/20060512/032051ea/attachment.html