New to COM (Excel). Need a little help.

[Marc]

Hi all,

I am trying to write an application where I need the ability to open
an Excel spreadsheet and do basic read/write, insert rows, and
hide/unhide rows. Using win32com I have been able to get the basics
down as well as some examples displaying how to simply read and write.

But the next step appears exponential. I haven never done anything in
VB, so any and all concepts and commands are completely foreign. I
have been digging through the VB help and also bought a book
specifically for Python and COM. But I don't really have time to learn
VB before I can finish my script.

Would there happen to be any pre-existing examples of the stuff I need
to do out there? Basically I need to do the things I listed above -
insert rows and columns and hide/unhide rows. I think with a few
examples of sheet manipulation I could figure out the rest.

Thanks ahead of time,
Marc

[Bob Gailer]

Re: New to COM (Excel). Need a little help.

At 10:18 AM 10/3/2003, Marc wrote:
[color=blue]
>Hi all,
>
>I am trying to write an application where I need the ability to open
>an Excel spreadsheet and do basic read/write, insert rows, and
>hide/unhide rows. Using win32com I have been able to get the basics
>down as well as some examples displaying how to simply read and write.
>
>But the next step appears exponential. I haven never done anything in
>VB, so any and all concepts and commands are completely foreign. I
>have been digging through the VB help and also bought a book
>specifically for Python and COM. But I don't really have time to learn
>VB before I can finish my script.
>
>Would there happen to be any pre-existing examples of the stuff I need
>to do out there? Basically I need to do the things I listed above -
>insert rows and columns and hide/unhide rows. I think with a few
>examples of sheet manipulation I could figure out the rest.[/color]

The Range object handles all of the above. I recommend examining the
various properties and methods of Range. One easy way to do this is
1 open the Excel VBA Window
2 press F2 to get the Object Browser
3 scroll the classes pane to Range then look at members insert, delete and
value.
4 Press F1 on any of these to see the help file and helpful examples.

Also look at Worksheet Classes Rows and Columns properties for Range
objects that span entire rows / columns

Assuming you have created a worksheet object (let's call it ws)

Reading & Writing (a review):[color=blue][color=green][color=darkred]
>>> rng = ws.Range("a1:b2 ")
>>> rng.Value[/color][/color][/color]
((None, None), (None, None))[color=blue][color=green][color=darkred]
>>> rng.Value=((1, 2), (3, 4))
>>> rng.Value[/color][/color][/color]
((1.0, 2.0), (3.0, 4.0))

Hiding:[color=blue][color=green][color=darkred]
>>> row = ws.Rows("1")
>>> row.Hidden[/color][/color][/color]
0[color=blue][color=green][color=darkred]
>>> row.Hidden=1[/color][/color][/color]

Insert/Delete[color=blue][color=green][color=darkred]
>>> row.Insert()
>>> col = ws.Columns("A")
>>> col.Delete()[/color][/color][/color]

Enough?

Bob Gailer
bgailer@alum.rp i.edu
303 442 2625


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.521 / Virus Database: 319 - Release Date: 9/23/2003

[Peter Abel]

Re: New to COM (Excel). Need a little help.

mnations@airmai l.net (Marc) wrote in message news:<4378fa6f. 0310030818.6508 6abe@posting.go ogle.com>...[color=blue]
> Hi all,
>
> I am trying to write an application where I need the ability to open
> an Excel spreadsheet and do basic read/write, insert rows, and
> hide/unhide rows. Using win32com I have been able to get the basics
> down as well as some examples displaying how to simply read and write.
>
> But the next step appears exponential. I haven never done anything in
> VB, so any and all concepts and commands are completely foreign. I
> have been digging through the VB help and also bought a book
> specifically for Python and COM. But I don't really have time to learn
> VB before I can finish my script.
>
> Would there happen to be any pre-existing examples of the stuff I need
> to do out there? Basically I need to do the things I listed above -
> insert rows and columns and hide/unhide rows. I think with a few
> examples of sheet manipulation I could figure out the rest.
>
> Thanks ahead of time,
> Marc[/color]

One trick is using Excel's Macro-Recorder to record all
you've done in Excel. After this you get the resulting macro
by Alt-F11 in the VBA-Editor.
You can copy the recorded methode-calls to Python by changing some
few things to Python-syntax.
Let's say you have reached the following point:
[color=blue][color=green][color=darkred]
>>> import win32com.client
>>> excel = win32com.client .Dispatch("Exce l.Application")
>>> excel.Visible=1
>>> workbook=excel. Workbooks.Add()[/color][/color][/color]

Now start Excel's Macro-Recorder and do want you want to do.
E.g. select row 4-9 and hide them.
Stop recording and see the VBA-result:
Rows("4:9").Sel ect
Selection.Entir eRow.Hidden = True
This will result in the following Python code:[color=blue][color=green][color=darkred]
>>> excel.Rows("4:9 ").Select()
>>> excel.Selection .EntireRow.Hidd en = True[/color][/color][/color]
Et voila, line 4 to 9 are hidden
(provided that you got them unhided before in Excel).
Some days or weeks later when you have learned a little bit
about ??Microsoft's VBA-OOP-concept?? :-) you will know that you
can code this shorter:[color=blue][color=green][color=darkred]
>>> workbook.Active Sheet.Rows("4:9 ").Hidden = True[/color][/color][/color]
or quicker:[color=blue][color=green][color=darkred]
>>> excel.Rows("4:9 ").Hidden = True[/color][/color][/color]

I hope you will likes this **LEARNING by RECORDING** :-)

Regards
Peter

[Marc]

Re: New to COM (Excel). Need a little help.

One other quick question. How do I close down the Excel Application?
After performing the commands:

x1App = win32com.client .dynamic.Dispat ch('Excel.Appli cation')
xlApp.Workbooks .Open(filename)

That fires up Excel which stays hidden unless I make it visible. I can
close the book and delete the COM object by doing the following:

xlBook.Close(Sa veChanges=0)
del self.xlApp

But the Excel process is still running in the background. How do I
stop the Excel process? Leaving it running seems to screw up the
application the next time I run it.

Thanks again,
Marc

[Bob Gailer]

Re: New to COM (Excel). Need a little help.

At 06:06 PM 10/3/2003, Marc wrote:
[color=blue]
>One other quick question. How do I close down the Excel Application?
>After performing the commands:
>
> x1App = win32com.client .dynamic.Dispat ch('Excel.Appli cation')
> xlApp.Workbooks .Open(filename)
>
>That fires up Excel which stays hidden unless I make it visible. I can
>close the book and delete the COM object by doing the following:
>
> xlBook.Close(Sa veChanges=0)
> del self.xlApp
>
>But the Excel process is still running in the background. How do I
>stop the Excel process? Leaving it running seems to screw up the
>application the next time I run it.[/color]

Right. It does. Thank you Microsoft.
Try:
x!App.Quit()

Bob Gailer
bgailer@alum.rp i.edu
303 442 2625


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.521 / Virus Database: 319 - Release Date: 9/23/2003