Python - Working with Excel - Enter data everytime 9 cells at a time

Question

I am re writing my question with code, First of all I am new to programming. Started to think about programming recently. :( at very later stage of life :)

My code is as below:

   import win32com.client as win32
   from win32com.client import Dispatch

   excel = win32.gencache.EnsureDispatch('Excel.Application')
   wb = excel.Workbooks.Open(r'F:\python\book1.xlsx')
   excel.Visible = False
   ws = wb.Worksheets("Sheet1")

   # to get the last row 
   used = ws.UsedRange
   nrows = used.Row + used.Rows.Count


   ws.Cells(nrows,2).Value = "21"
   ws.Cells(nrows,2).Offset(2,1).Value = "22"
   ws.Cells(nrows,2).Offset(3,1).Value = "23"
   #like this nine values

   wb.Save()
   excel.Application.Quit()

What I am trying to do is write values in the excel sheet.

sample sheet

Old Question Below Ignore it. I am using Python 2.7 and win32com to access excel file

I am stuck with a problem where I need to enter data in to 9 cells each time on column B

I want to select the last cell in B column and enter the new set of 9 cell values.

I tried to use ws.usedRange but this is not helping as it chooses the last cell wherever the data is present in the whole sheet. You can see in the attached sheet testdata which is spread in columns D,E,F etc so used range chooses the last cell based on that. is there a way to solve my problem? I am ok to use any other module as well if it helps.

First, you haven't attached any sheet. Second, instead of describing the code, show us the actual code. — abarnert, Nov 29 '13 at 06:35
What about something like `Worksheets(1).Range("B1:B65535").Find("", lookin:=xlValues)`? See [`Range.Find`](http://msdn.microsoft.com/en-us/library/office/ff839746.aspx). — wilx, Nov 29 '13 at 06:46
Translate [this](http://stackoverflow.com/questions/6301665/row-number-of-last-cell-with-data) from VBA to Python. I prefer the second answer. — Steven Rumbalski, Nov 29 '13 at 06:51
I tried to attach the sheet/image but it said I cannot attach image as I do not have required rating to attach the sheet. This is my First post. Apologies for the inconvenience caused. — just10minutes, Nov 29 '13 at 06:52
Thanks for the suggestion I will try and let you know if I am successful. — just10minutes, Nov 29 '13 at 06:53
I haven't got a perfect solution but I will be following the below approach. I will run a macro on excel to store the last row number on cell A1 and read it for further use. iLastRow = calcws.Cells(rows.count, "b").End(xlUp).row — just10minutes, Nov 29 '13 at 14:11
@just10minutes: For future reference, if you have something that you can't post here (either because you don't have the rep, or it just isn't appropriate—e.g., it's 5 pages long and most people won't want to read it), just post it somewhere else, and put a link in your question. — abarnert, Dec 02 '13 at 18:43

score 2 · Accepted Answer · answered Nov 29 '13 at 06:46

2

A UsedRange:

… includes any cell that has ever been used. For example, if cell A1 contains a value, and then you delete the value, then cell A1 is considered used. In this case, the UsedRange property will return a range that includes cell A1.

Do you want to work on every cell that has ever been used? If not, why would you use UsedRange? If so, what are you trying to use it for? To find the last row in the UsedRange? You can do that easily. The Range Objects docs show you what you can do with them.

Then, once you know what you want to specify, the same documentation shows how to ask for it. You want B10:B18? Just ws.Range('B10:B18').

Once you have that Range object, you can assign a value or formula to the whole range, iterate over its cells, etc. Again, the same docs show how to do it.

answered Nov 29 '13 at 06:46

abarnert

354,177
51
601
671

Thanks for the suggestion. Will let you know what happens. I think I should give the whole code and ask the question. May be Ill do it in a while. – just10minutes Nov 29 '13 at 06:54
Sorry nothing I could figure out till now. Please let me know If I am missing something. I tried to implement all the solutions but still unsuccessful – just10minutes Nov 29 '13 at 08:23
@just10minutes: What does "I tried to implement all the solutions but still unsuccessful" mean? Are you talking about the code you edited into the question? If so, what does it do wrong? Does it select the wrong rows? Raise an exception? – abarnert Dec 02 '13 at 18:45

Python - Working with Excel - Enter data everytime 9 cells at a time

1 Answers1