How can I iterate over worksheets in win32com?

Question

I generate an xlsx file with lots of sheets and I want to take me at specific position when I open it manually with Excel. This function does the job but for one sheet only. How can I apply it to all of the sheets in workbook?

import win32com.client

def select_cell():
    xl = win32com.client.gencache.EnsureDispatch('Excel.Application')

    wb = xl.Workbooks.Open(r'H:\Files\1.xlsx')
    ws = xl.ActiveSheet
    ws.Range('B100').Select()
    wb.Close(True)
    xl.Quit()

select_cell()

I want to make something like this:

import win32com.client

def select_cell():
    xl = win32com.client.gencache.EnsureDispatch('Excel.Application')
    wb = xl.Workbooks.Open(r'H:\Files\1.xlsx')
    for ws in wb.Worksheets():
        ws.Range('B100').Select()

    wb.Close(True)
    xl.Quit()

select_cell()

for ws in wb.Worksheets(): File "C:\Users\1\AppData\Local\Temp\gen_py\2.7\00020813-0000-0000-C000-000000000046x0x1x7\Sheets.py", line 113, in __call__ ret = self._oleobj_.InvokeTypes(0, LCID, 2, (9, 0), ((12, 1),),Index pywintypes.com_error: (-2147352567, 'Exception occurred.', (0, None, None, None, 0, -2147352571), 1) — electroiv, Dec 31 '16 at 11:56
Did you try to replace `for ws in wb.Worksheets():` by `for sh in wb.Sheets:` then `ws = wb.Worksheets(sh.Name)` ? — J. Piquard, Dec 31 '16 at 12:33
It did work, thanks. I was able to iterate and put a value in all of them at the same cell. Unfortunately, ws.Range('B100').Select() gave an error: method Select from class Range was finished incorrectly. So I still wasn't able to set open point in every sheet like I did in the first example and like Excel does when you scroll manually and then close the document. — electroiv, Dec 31 '16 at 12:43
Did you try to modify the `ActiveSheet` in order to explore all `for sh in wb.Sheets:` ? ==> proposal `xl.Worksheets(sh.Name).Activate()`, then `ws = xl.ActiveSheet`... — J. Piquard, Dec 31 '16 at 14:11
I found that you need both to input a value and select the same cell in order to get cell remembered. I did as you suggested and it remembered position in every sheet. Thanks for the help! — electroiv, Dec 31 '16 at 14:29

score 3 · Accepted Answer · answered Dec 31 '16 at 16:14

In order to be taken to specific cell in newly generated document it is necessary to have both of these expressions executed:

ws.Range('k100').Value = 1
ws.Range('k100').Select()

To do it in every sheet of the workbook:

def select_cell():
    xl = win32com.client.gencache.EnsureDispatch('Excel.Application')
    wb = xl.Workbooks.Open(r'H:\Files1.xlsx')

    for sh in wb.Sheets:

        xl.Worksheets(sh.Name).Activate()
        ws = xl.ActiveSheet
        ws.Range('k100').Value = 1
        ws.Range('k100').Select()


    wb.Close(True)
    xl.Quit()

The code above will take you to K100 on every worksheet in the book.

score 0 · Answer 2 · answered Jan 29 '17 at 21:46

Your script did nothing at all when I tested it.

The script below worked fine, based on my test.

import xlsxwriter


# Create an new Excel file and add a worksheet.
workbook = xlsxwriter.Workbook('C:\\Users\\Excel\\Desktop\\book1.xlsx')
worksheet = workbook.add_worksheet()

# Widen the first column to make the text clearer.
worksheet.set_column('A:A', 20)

# Add a bold format to use to highlight cells.
bold = workbook.add_format({'bold': True})

# Write some simple text.
worksheet.write('A1', 'Hello')

# Text with formatting.
worksheet.write('A2', 'World', bold)

# Write some numbers, with row/column notation.
worksheet.write(2, 0, 123)
worksheet.write(3, 0, 123.456)


workbook.close()

How can I iterate over worksheets in win32com?

2 Answers2

Linked