RE: Frustration with Luacom and Excel

Hi Thomas

Thanks for your message, our messages crossed in the post !

For the Excel hyperlink problem, our solutions agreed. Yay :)

Thanks for the graphing snippets, I am sure they will be very useful when I get around to tackling that one

Geoff

From: spammealot1@live.co.uk
To: lua-l@lists.lua.org
Date: Wed, 29 Apr 2015 13:59:59 +0100
Subject: RE: Frustration with Luacom and Excel

Hi

Thanks for the suggestion as you predicted it didn't work. I was dumb enough to sit here for several hours just trying all the permutations of tables with named parameters and strings.

Oleview wouldn't help me, as I know what the parameters are for the Hyperlinks:Add() function are, I just don't know how Luacom is expecting them to be passed.

Eventually I stumbled on the correct way of passing the arguments. It doesn't expect namedParams, or a table at all, it just has a predefined order of arguments

It is

excel.Selection.Hyperlinks:Add(cellLocation, urlRootStr, urlFragmentStr, ToolTipStr, HyperLinkTextStr)

where urlRootStr, urlFragmentStr are appended together with a # for the final url string

Full working example

require "luacom"

excel = luacom.CreateObject("Excel.Application")
local book = excel.Workbooks:Add()
local sheet = book.Worksheets(1)

excel.Visible = true

sheet.Cells(1, 1):Select()
excel.Selection.Hyperlinks:Add(excel.Selection, "http://www.bbc.co.uk/", "election-2015-32506490", "ToolTip", "BBC")

This should have been a 30 second exercise for me to look up the docs on the web, not the several hours it actually took.

IMHO this lack of good library documentation is a huge problem for Lua, as this is not an isolated case.

There are a few notable exceptions like Steve Donovan, Leaf Corcoran, Stefano Peluchetti etc, that produce excellent documentation, but generally the quality of documentation is poor.

Perhaps I should create a Hall of Fame and Shame for authors that produce good and bad documentation ? :)

Geoff

Date: Wed, 29 Apr 2015 08:53:58 -0300
From: iburgueno@gmail.com
To: lua-l@lists.lua.org
Subject: Re: Frustration with Luacom and Excel

Have you tried with:

excel.Selection.Hyperlinks:Add({Anchor = excel.Selection, Address="http://www.bbc.co.uk/", TextToDisplay="BBC"} )

Anyway, if that fails (which probably will), you can take a look at the definition of the Add method (ActiveSheed.Hyperlinks.Add) using OleView, as suggested earlier.

That's because you are using named parameters, and I don't recall how LuaCom deals with those.

Named parameters are like a "shortcut" were you don't need to provide all arguments to a function if some of them are optional.

For instance, this function (not related to Excel)

HRESULT StartRecording(

[in] BSTR Path,

[in, optional, defaultvalue(0)] short Width,

[in, optional, defaultvalue(0)] short Height,

[in, optional, defaultvalue(0)] short FrameRate);

Could be invoked as StartRecording( Path="something", FrameRate=30 )

But, with LuaCom, you might need to invoke it passing nils for the arguments not provided.

StartRecording( "something", nil, nil, FrameRate=30 )

Hope that helps.