open(.xls file, 'w') so that hyperlinks appear

[Jeff Shannon]

Lemniscate wrote:

> Okay, my other post should be up soon, but I thought I would throw
> this out.  First off, I should say I am not a VB person, so I have to
> work at converting VB code into win32client compatible commands.
> However, the Help File in the MS Excel Help has me stumpted.  Here is
> the page I am using as a basis and afterwards is just a cut and paste
> of my attempts (at an interactive prompt) to insert a hyper link.
> Hyperlinks Collection Object
>
> ........
> Use the Add method to create a hyperlink and add it to the Hyperlinks
> collection. The following example creates a new hyperlink for cell E5.
>
> With Worksheets(1)
>     .Hyperlinks.Add .Range("E5"), "http://www.gohere.com"
> End With

The trick with VB, is that many times function calls don't require parenthesis to group the parameters.  This
would translate as something like...

# xl.sheet is a Worksheet object...
xl.sheet.Hyperlinks.Add( xl.sheet.Range('E5'), "http://www.gohere.com")


>
> Okay, here is my interactive attempts.  Hopefully, seeing these will
> help you see what I am doing wrong (I am SURE it is something simple,
> but it's ticking me off).  Also, as a side note, in PythonWin, I
> cannot run a Commakepy on Excel 9 (I'll add that attempt to the bottom
> of my code so you can all see what I mean).  Thanks a bunch.

You may not have installed the type libraries when you installed Excel.  Try installing more Excel
components--anything mentioning VB or macro facilities (or online help, for that matter...)

>
> >>> from win32com.client import Dispatch
> >>> xlApp = Dispatch("Excel.Application")
> >>> xlApp.Workbooks.Add()
> <COMObject Add>
> >>> OK = xlApp.Workbooks(1).Sheets(1)
> >>> OK.Range("B2:C5").Value = "Range Insert"
> >>> xlApp.Visible = 1
> >>> xlApp.Hyperlinks.Add.Cells("B5"), 'http://www.gohere.com"
> Traceback (  File "<interactive input>", line 1
>     xlApp.Hyperlinks.Add.Cells("B5"), 'http://www.gohere.com"
>                                                                   ^
> SyntaxError: invalid token

Judging from the help page you quoted above, Hyperlinks is a property of a sheet, not the app, therefore you
should (I think) be doing...

OK.Hyperlinks.Add(OK.Cells("B5"), "http://www.gohere.com")

And as I noted previously, Python requires parentheses that VB doesn't.

Once you get the type library installed (and it *should* be on your Office/Excel cd--I had no problem running
makepy on Excel...) then you can use the COM object browser that's built into PythonWin to examine what functions
are available, and what parameters they expect--some of the information is kind of cryptic, though, and it helps
if you have some idea what you're looking for to begin with.  ;)

I also *strongly* recommend, if you haven't already, reading Python Programming on Win32 (by Mark Hammond & Andy
Robinson).  It has fairly extensive examples of driving Excel from within Python.

Hope this helps.  If you have any more questions, feel free to ask.  :)

Jeff Shannon
Technician/Programmer
Credit International