Adding Worksheets to an Excel Workbook

[e.h.doxtator]

All

I'm a Python newbie, and I'm just getting to the wonders of COM
programming. I am trying to programmatically do the following:

1. Activate Excel
2. Add a Workbook
3. Add a Worksheet
4. Populate the new Worksheet
5. Repeat steps 3,4 while there is data.

How do you add a Worksheet to a Workbook?

Thanks!

[John Machin]

e.**********@accenture.com wrote:

All

I'm a Python newbie, and I'm just getting to the wonders of COM
programming. I am trying to programmatically do the following:

1. Activate Excel
2. Add a Workbook
3. Add a Worksheet
4. Populate the new Worksheet
5. Repeat steps 3,4 while there is data.

How do you add a Worksheet to a Workbook?

To find out how to do things, you can:

(1) use the VBA help in Excel.

You would find (eventually):
"""
Add method as it applies to the Sheets and Worksheets objects.

Creates a new worksheet, chart, or macro sheet. The new worksheet
becomes the active sheet.

expression.Add(Before, After, Count, Type)
expression Required. An expression that returns one of the above
objects.

Before Optional Variant. An object that specifies the sheet before
which the new sheet is added.

After Optional Variant. An object that specifies the sheet after
which the new sheet is added.

Count Optional Variant. The number of sheets to be added. The default
value is one.

Type Optional Variant. Specifies the sheet type. Can be one of the
following XlSheetType constants: xlWorksheet, xlChart,
xlExcel4MacroSheet, or xlExcel4IntlMacroSheet. If you are inserting a
sheet based on an existing template, specify the path to the template.
The default value is xlWorksheet.

Remarks
If Before and After are both omitted, the new sheet is inserted before
the active sheet.
"""
so,
your_handle.Worksheets.Add()
looks like what you need.

(2) Again in Excel, use the "record a macro" facility: turn on
recording, do your thing, stop recording, inspect the generated macro.

In this case, this gave
Sheets.Add
which you translate to
your_handle.Sheets.Add()

What's the difference between Sheets and Worksheets? I dunno. Try both.
Look in the Excel VBA help.

HTH,
John

[Mark Elston]

I expect this doesn't help him much. I get the impression he is looking
more for a recipe.

Just doing a Google search of python + excel I got the following which
make some good starting points:

http://www.markcarter.me.uk/computing/python/excel.html
http://mail.python.org/pipermail/pyt...er/183367.html
http://mathieu.fenniak.net/plotting-...ugh-pythoncom/

There are lots of others.

Mark

* John Machin wrote (on 10/10/2006 2:59 PM):

e.**********@accenture.com wrote:

>All

I'm a Python newbie, and I'm just getting to the wonders of COM
programming. I am trying to programmatically do the following:

1. Activate Excel
2. Add a Workbook
3. Add a Worksheet
4. Populate the new Worksheet
5. Repeat steps 3,4 while there is data.

How do you add a Worksheet to a Workbook?

To find out how to do things, you can:

(1) use the VBA help in Excel.

You would find (eventually):
"""
Add method as it applies to the Sheets and Worksheets objects.

Creates a new worksheet, chart, or macro sheet. The new worksheet
becomes the active sheet.

expression.Add(Before, After, Count, Type)
expression Required. An expression that returns one of the above
objects.

Before Optional Variant. An object that specifies the sheet before
which the new sheet is added.

After Optional Variant. An object that specifies the sheet after
which the new sheet is added.

Count Optional Variant. The number of sheets to be added. The default
value is one.

Type Optional Variant. Specifies the sheet type. Can be one of the
following XlSheetType constants: xlWorksheet, xlChart,
xlExcel4MacroSheet, or xlExcel4IntlMacroSheet. If you are inserting a
sheet based on an existing template, specify the path to the template.
The default value is xlWorksheet.

Remarks
If Before and After are both omitted, the new sheet is inserted before
the active sheet.
"""
so,
your_handle.Worksheets.Add()
looks like what you need.

(2) Again in Excel, use the "record a macro" facility: turn on
recording, do your thing, stop recording, inspect the generated macro.

In this case, this gave
Sheets.Add
which you translate to
your_handle.Sheets.Add()

What's the difference between Sheets and Worksheets? I dunno. Try both.
Look in the Excel VBA help.

HTH,
John

[greg.rb]

# here is a simple script:

from win32com.client import Dispatch

xlApp = Dispatch("Excel.Application")
xlApp.Visible=1 #show me excel
xlApp.Workbooks.Add() #add a workbook

for r in range(1,5): #put data into spreadsheet row/column
xlApp.Cells(r,r).Value=r

for r in range(1,5): #read data from sheet
print xlApp.Cells(r,r).Value

xlApp.Worksheets.Add()#add another sheet in same workbook.

for r in range(1,11): #put data into spreadsheet
xlApp.Cells(1,r).Value=r #first row this tome

#xlApp.ActiveWorkbook.Close()
#xlApp.Quit

Good luck.

Here is a free Excel Object Model Overview:
http://msdn.microsoft.com/library/de...eloverview.asp

[e.h.doxtator]

These are all excellent suggestions. Thanks everyone for your help!