How to add data into exisitng Excel file at next open row?

[Mike Driver]

How can you determine the next open row in an existing Excel file such
that you can start adding data to the cells in that row? As in below,
I want a variable in place of the 6 (row 6 in the four ws1.Cells(x,1)
lines), but have no other way of knowing what row I am on besides
looking to the first free cell in column A. How to do? Examples I
see make it seem really complicated - this can't be that hard.

Thanks for any help.

worksheet = "C:\\Excel_Reports\\ea" + ea + "report"# + ".xls" 
xlApp = win32com.client.Dispatch("Excel.Application") 
xlApp.Visible = 1 
xlApp.Workbooks.Open(worksheet) ## for existing file 
##xlApp.SheetsInNewWorkbook = 1 
##wb = xlApp.Workbooks() 
ws1 = xlApp.Worksheets(1) 


ws1.Cells(6,1).Value = "selection" 
ws1.Cells(6,2).Value = count 
ws1.Cells(6,3).Value = epcFloat 
ws1.Cells(6,8).Value = currentGMT 


wb.SaveAs(worksheet) 
wb.Close(False) ## False/1

[Stewart Ross]

Hi. If your spreadsheet does not have fixed data of some kind situated below the data you are adding, and you are not using specialised formatting of the worksheet, you can use the LastCell property of the SpecialCells method to find the last entered cell in the worksheet. If you add one to the row number for that cell you have got the rownumber of the next blank row in your worksheet:

NextRow = ws1.Range("A1").SpecialCells(xlLastCell).Row + 1

You can then use this in the .Cells method in place of the constant you show:

ws1.Cells(NextRow, 8) = currentGMT

The .Value property of a range is its default, so there is no difference between the effect of the statement above and

ws1.Cells(NextRow, 8).Value = currentGMT

-Stewart

PS current Excel help on the enumerated constanst for the SpecialCells method refer to xlTypeLastCell in place of xlLastCell. The constant has the value 11. In my code (derived from earlier versions of Excel and now used with Office 2007) I still use xlLastCell rather than xlTypeLastCell with no problem.

[Mike Driver]

Thanks so much Stewart! That worked. How simple. I did have to use the 11 instead of either xlTypeLastCell or xlLastCell. I find that case always for me (like to saveAs to a cvs or whatever, I have to use the number code for cvs). You are successful with the text. Not sure why that would be that I have to use a number. Just curious, any ideas?

[Stewart Ross]

Hi Mike. Reason that you can't access the constants is because you are using late binding (as shown in line 2 of your first post), dispensing with the need to declare an explicit reference to the Excel object library. With early binding you could declare variable xlApp to be of type Excel.Applicati on. You'd have to have a reference to the Excel library in place first or the type declaration would be deemed invalid by the compiler.

A downside to late binding is that without the reference to the object library you can't access any of the properties stored within it, including the pre-declared enumerated type constants such as xlLastCell. You also will not be able to use intellisense to guide you about what object methods and properties are available.

-Stewart