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

Adding Worksheets to an Excel Workbook

P: n/a
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!

Oct 10 '06 #1
Share this Question
Share on Google+
4 Replies


P: n/a
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

Oct 10 '06 #2

P: n/a
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
Oct 10 '06 #3

P: n/a
# 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

Oct 10 '06 #4

P: n/a
These are all excellent suggestions. Thanks everyone for your help!

Oct 11 '06 #5

This discussion thread is closed

Replies have been disabled for this discussion.