By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
439,932 Members | 1,944 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 439,932 IT Pros & Developers. It's quick & easy.

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

P: 5
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.

Expand|Select|Wrap|Line Numbers
  1. worksheet = "C:\\Excel_Reports\\ea" + ea + "report"# + ".xls" 
  2. xlApp = win32com.client.Dispatch("Excel.Application") 
  3. xlApp.Visible = 1 
  4. xlApp.Workbooks.Open(worksheet) ## for existing file 
  5. ##xlApp.SheetsInNewWorkbook = 1 
  6. ##wb = xlApp.Workbooks() 
  7. ws1 = xlApp.Worksheets(1) 
  10. ws1.Cells(6,1).Value = "selection" 
  11. ws1.Cells(6,2).Value = count 
  12. ws1.Cells(6,3).Value = epcFloat 
  13. ws1.Cells(6,8).Value = currentGMT 
  16. wb.SaveAs(worksheet) 
  17. wb.Close(False) ## False/1 
Dec 5 '10 #1
Share this Question
Share on Google+
3 Replies

Expert Mod 2.5K+
P: 2,545
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:

Expand|Select|Wrap|Line Numbers
  1. NextRow = ws1.Range("A1").SpecialCells(xlLastCell).Row + 1
You can then use this in the .Cells method in place of the constant you show:

Expand|Select|Wrap|Line Numbers
  1. 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

Expand|Select|Wrap|Line Numbers
  1. ws1.Cells(NextRow, 8).Value = currentGMT

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.
Dec 5 '10 #2

P: 5
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?
Dec 5 '10 #3

Expert Mod 2.5K+
P: 2,545
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.Application. 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.

Dec 5 '10 #4

Post your reply

Sign in to post your reply or Sign up for a free account.