help on ExcelApplication

Getting Started
#1

Hi all
How i can get the total on rows on the excel file

#2

Are you talking about EXCEL or XOJO?

In Excel put a forumla in the cell


=SUM(A1...A99)

but if that is you question… the EXCEL docs are a great place to start

#3

I read that as ‘what is the row number of the last line’?, assuming on was supposed to be ‘of’

If that is the request I think something on these lines…

xlSheet=xlApp.ActiveWorkbook.Worksheets(Sheetno) LastRow=xlSheet.UsedRange.Rows.Count

#4

On Xojo

thanks

#5

untested… (as Im working on a mac)

[code]dim excel as new ExcelApplication
dim book as ExcelWorkbook
dim sheet as ExcelWorkSheet
dim f as folderitem
excel.visible=true

f=SpecialFolder.Desktop.child(“the file.xls”)
book=excel.Workbooks.open(f.ShellPath)
Sheet=book.Worksheets(0)
LastRow=Sheet.UsedRange.Rows.Count[/code]

Thats the row count.

If you want the sum of a column, then you could change the formula of a cell at the bottom to be as Dave says… SUM(A1…A99)

then read the value of that cell.

Eugene Daykin has a very very useful book on driving Excel in Xojo:

https://forum.xojo.com/25640-i-wish-i-knew-how-to-program-excel-with-xojo-in-windows/0

#6

Hi Alexis,

Jeffs example should work. Here is an example that I put together and tested on Xojo 2015 r3.1 with Microsoft Office 2016:

[code] Dim excel as new ExcelApplication
excel.Workbooks.Add
excel.Visible = True

//Fill some rows with data
Dim i as Integer
For i = 1 to 100
excel.Range(“A” + CStr(i)).value =“5”
Next i

//Show the number of rows
Dim answer as String
answer = excel.ActiveSheet.UsedRange.Rows.Count
MsgBox answer //should be 100
[/code]

#7