Another PythonWin Excel question

[It's me]

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,

 

[Kartic]

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

 

[It's me]

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*. :=(

 

[Kartic]

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/d...tml/wrtskhowtoaddnewworksheetstoworkbooks.asp
Thanks

 

[It's me]

Ah, this work:

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

got it from:

http://mail.python.org/pipermail/python-list/2003-September/183367.html

Thanks again.

 

[Marten Bauer]

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,

Hello,

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

wb.Worksheets.Add(Count=nrMonths,After=wb.Worksheets(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

 

[It's me]

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

wb.Worksheets.Add(Count=nrMonths,After=wb.Worksheets(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,

 

[David Bolen]

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-us/odeomg/html/deovrobjectmodelguide.asp

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

-- David

 

[It's me]

Thanks,

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-us/odeomg/html/deovrobjectmodelguide.asp

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

-- David

 

[Mike Thompson]

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,

Does this help?

http://msdn.microsoft.com/library/d...l/wrtskhowtomoveworksheetswithinworkbooks.asp

 

[It's me]

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]

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.

 

[It's me]

Okay, thanks. That helps a lot.

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.