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

Another PythonWin Excel question

P: n/a
I followed the example in
http://stompstompstomp.com/weblog/technical/2004-05-20 and learned that to
add a new worksheet to an Excel workbook, you would use the
workbook.Worksheets.Add() method. That works. However, the new worksheet
got added *in front* of the last worksheet I was at. How can I get it to
add *after*?

Thanks,

--
Me
Jul 18 '05 #1
Share this Question
Share on Google+
12 Replies


P: n/a
I am not sure about this but I believe you can give a parameter
after="sheet1". to Add(), like so, Add(after="sheet1").

Unfortunately I do not have Excel installed on this machine to confirm
this.

A tip: if you have VBA (which you should if you have Excel) installed,
lookup the Add method for the Worksheets collection. VBA will show the
code completion, with all the arguments for the method call. Try the
same for any of the methods.

Thanks,
--Kartic

Jul 18 '05 #2

P: n/a

"Kartic" <ka******************@gmail.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...
I am not sure about this but I believe you can give a parameter
after="sheet1". to Add(), like so, Add(after="sheet1").

I get a "got an expected keyword argument 'after'" from Add().
Unfortunately I do not have Excel installed on this machine to confirm
this.

A tip: if you have VBA (which you should if you have Excel) installed,
lookup the Add method for the Worksheets collection. VBA will show the
code completion, with all the arguments for the method call. Try the
same for any of the methods.

Yes, I read about that but unfortunately I have no experience with VBA *at
all*. :=(
Thanks,
--Kartic

Jul 18 '05 #3

P: n/a
Sorry, I was thinking of the move() method. Move() takes the after=
argument.

See this link for usage. (The left nav bar has all other method of the
worksheets collection, so bookmark this page :-)

http://msdn.microsoft.com/library/de...oworkbooks.asp
Thanks

Jul 18 '05 #4

P: n/a
Ah, this work:

self.xlbook.Worksheets.Add(None,sht)

got it from:

http://mail.python.org/pipermail/pyt...er/183367.html

Thanks again.

--
Me
"It's me" <it***@yahoo.com> wrote in message
news:ou****************@newssvr21.news.prodigy.com ...

"Kartic" <ka******************@gmail.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...
I am not sure about this but I believe you can give a parameter
after="sheet1". to Add(), like so, Add(after="sheet1").


I get a "got an expected keyword argument 'after'" from Add().
Unfortunately I do not have Excel installed on this machine to confirm
this.

A tip: if you have VBA (which you should if you have Excel) installed,
lookup the Add method for the Worksheets collection. VBA will show the
code completion, with all the arguments for the method call. Try the
same for any of the methods.


Yes, I read about that but unfortunately I have no experience with VBA *at
all*. :=(
Thanks,
--Kartic


Jul 18 '05 #5

P: n/a
It's me wrote:
I followed the example in
http://stompstompstomp.com/weblog/technical/2004-05-20 and learned that to
add a new worksheet to an Excel workbook, you would use the
workbook.Worksheets.Add() method. That works. However, the new worksheet
got added *in front* of the last worksheet I was at. How can I get it to
add *after*?

Thanks,

--
Me

Hello,

I did it yesterday like this way and it works well (part of my code):

wb.Worksheets.Add(Count=nrMonths,After=wb.Workshee ts(1))

As I read in MSDN you could not write After="sheet1" instead you must
use the Object of sheet1 like in my example and it works well in my
case. The Count=... statement will create n Sheets after the first worksheet
By
Marten
Jul 18 '05 #6

P: n/a

"Marten Bauer" <Ma**********@gmx.net> wrote in message
news:34*************@individual.net...

I did it yesterday like this way and it works well (part of my code):

wb.Worksheets.Add(Count=nrMonths,After=wb.Workshee ts(1))
As I read in MSDN you could not write After="sheet1" instead you must
use the Object of sheet1 like in my example and it works well in my
case. The Count=... statement will create n Sheets after the first worksheet
Yes, I learn that as well. The parameter to After is a Worksheet object.
It appears if you don't specify any parameters, it would add it Before the
current sheet.

Thanks,


By
Marten

Jul 18 '05 #7

P: n/a
"It's me" <it***@yahoo.com> writes:
Yes, I read about that but unfortunately I have no experience with VBA *at
all*. :=(


You don't really have to know VBA, but if you're going to try to
interact with COM objects from Python, you'll find it much smoother if
you at least use any available reference information for the COM
object model and interfaces you are using.

In the Excel case, that means understanding - or at least knowing how
to look in a reference - its object model, since that will tell you
exactly what parameters an Add method on a worksheet object will take
and how they work.

For excel, online documentation can be found in a VBAXL9.CHM help file
(the "9" may differ based on Excel release), but it might not always
be installed depending on what options were selected on your system. In
my English, Office 2000 installation, for example, the files are located in:
c:\Program Files\Microsoft Office\Office\1033

You can load that file directly, or Excel itself will reference it
from within the script editor help (Tools->Macro->Visual Basic Editor,
then F1 for help). If you methods or classes and have the help
installed it'll bring in the reference.

You can also find it on MSDN on the web, although it can be tricky to
navigate down to the right section - the top of the Office 2000 object
documentation should be available at:

http://msdn.microsoft.com/library/en...modelguide.asp

This is mostly reference information, but there are some higher level
discussions of overall objects (e.g., worksheets, workbooks, cells,
etc...) too.

-- David
Jul 18 '05 #8

P: n/a
Thanks,

"David Bolen" <db**@fitlinxx.com> wrote in message
news:u1***********@fitlinxx.com...
"It's me" <it***@yahoo.com> writes:
Yes, I read about that but unfortunately I have no experience with VBA *at all*. :=(
You don't really have to know VBA, but if you're going to try to
interact with COM objects from Python, you'll find it much smoother if
you at least use any available reference information for the COM
object model and interfaces you are using.

In the Excel case, that means understanding - or at least knowing how
to look in a reference - its object model, since that will tell you
exactly what parameters an Add method on a worksheet object will take
and how they work.

For excel, online documentation can be found in a VBAXL9.CHM help file
(the "9" may differ based on Excel release), but it might not always
be installed depending on what options were selected on your system. In
my English, Office 2000 installation, for example, the files are located

in: c:\Program Files\Microsoft Office\Office\1033

You can load that file directly, or Excel itself will reference it
from within the script editor help (Tools->Macro->Visual Basic Editor,
then F1 for help). If you methods or classes and have the help
installed it'll bring in the reference.

You can also find it on MSDN on the web, although it can be tricky to
navigate down to the right section - the top of the Office 2000 object
documentation should be available at:

http://msdn.microsoft.com/library/en...modelguide.asp
This is mostly reference information, but there are some higher level
discussions of overall objects (e.g., worksheets, workbooks, cells,
etc...) too.

-- David

Jul 18 '05 #9

P: n/a
It's me wrote:
I followed the example in
http://stompstompstomp.com/weblog/technical/2004-05-20 and learned that to
add a new worksheet to an Excel workbook, you would use the
workbook.Worksheets.Add() method. That works. However, the new worksheet
got added *in front* of the last worksheet I was at. How can I get it to
add *after*?

Thanks,

--
Me


Does this help?

http://msdn.microsoft.com/library/de...nworkbooks.asp

--
Mike
Jul 18 '05 #10

P: n/a
Yes, Mike,

Others pointed that out as well.

The difficulty is that they are all in VBAs. Most of them can be
translated to Python fairly easily, and some I can get from looking at the
recorded macro - but some requires quite a bit of head scratching.

For instance, I wanted to figure out how create a new window. So, I went
through the record macro process and looked at the VBA code, it says:

ActiveWindow.NewWindow

Okay. Now what???

And for switching window, it says:

Windows("Book1:1").Activate

Okay. ???

So, I look through the online information on msdn and viola! No mentioning
of that anwhere....

Would be nice if there's a Python specific of it....but just dreaming...

Back to reading MSDN.....

Thanks,
"Mike Thompson" <none.by.e-mail> wrote in message
news:41**********************@news.optusnet.com.au ...
It's me wrote:
I followed the example in
http://stompstompstomp.com/weblog/technical/2004-05-20 and learned that to add a new worksheet to an Excel workbook, you would use the
workbook.Worksheets.Add() method. That works. However, the new worksheet got added *in front* of the last worksheet I was at. How can I get it to add *after*?

Thanks,

--
Me
Does this help?

http://msdn.microsoft.com/library/de...nworkbooks.asp
--
Mike

Jul 18 '05 #11

P: n/a
It's me wrote:
Yes, Mike,

Others pointed that out as well.
For good reason.

The difficulty is that they are all in VBAs. Most of them can be
translated to Python fairly easily, and some I can get from looking at the
recorded macro - but some requires quite a bit of head scratching.

For instance, I wanted to figure out how create a new window. So, I went
through the record macro process and looked at the VBA code, it says:

ActiveWindow.NewWindow
app.ActiveWindow.NewWindow()

Okay. Now what???

And for switching window, it says:

Windows("Book1:1").Activate


app.Windows.Item("Book1:1").Activate()

---------------------------------------------------------------------

from win32com.client import Dispatch, constants

app = Dispatch("Excel.Application")
app.Visible = True

workbook = app.Workbooks.Add()

defaultWorksheet = workbook.Worksheets(1)

app.ActiveWindow.NewWindow()
app.ActiveWindow.NewWindow()

# grab the capation (like 'Book1:1') from one of the windows
thridWindowsCaption = app.Windows[2].Caption

print thridWindowsCaption
app.Windows.Item(thridWindowsCaption).Activate()

------------------------------------------------------------------------

Sometimes its useful to look in the file generated by makepy. It details
all the classes and their methods AND there are annotations in the form
of comments. Having said that, if you've never looked in a makepy
generated module before, you're in for a shock - it takes a while
before you figure out what you are looking at.

When you get stuck, trial & error and a good debuger are your friend.

--
Mike
Jul 18 '05 #12

P: n/a
Okay, thanks. That helps a lot.

"Mike Thompson" <none.by.e-mail> wrote in message
news:41**********************@news.optusnet.com.au ...
It's me wrote:
Yes, Mike,

Others pointed that out as well.


For good reason.

The difficulty is that they are all in VBAs. Most of them can be
translated to Python fairly easily, and some I can get from looking at the recorded macro - but some requires quite a bit of head scratching.

For instance, I wanted to figure out how create a new window. So, I went through the record macro process and looked at the VBA code, it says:

ActiveWindow.NewWindow


app.ActiveWindow.NewWindow()

Okay. Now what???

And for switching window, it says:

Windows("Book1:1").Activate


app.Windows.Item("Book1:1").Activate()

---------------------------------------------------------------------

from win32com.client import Dispatch, constants

app = Dispatch("Excel.Application")
app.Visible = True

workbook = app.Workbooks.Add()

defaultWorksheet = workbook.Worksheets(1)

app.ActiveWindow.NewWindow()
app.ActiveWindow.NewWindow()

# grab the capation (like 'Book1:1') from one of the windows
thridWindowsCaption = app.Windows[2].Caption

print thridWindowsCaption
app.Windows.Item(thridWindowsCaption).Activate()

------------------------------------------------------------------------

Sometimes its useful to look in the file generated by makepy. It details
all the classes and their methods AND there are annotations in the form
of comments. Having said that, if you've never looked in a makepy
generated module before, you're in for a shock - it takes a while
before you figure out what you are looking at.

When you get stuck, trial & error and a good debuger are your friend.

--
Mike

Jul 18 '05 #13

This discussion thread is closed

Replies have been disabled for this discussion.