RE: Frustration with Luacom and Excel

Hi

As predicted I got stuck even before I tackled adding graphs.

I am really stuck on this one. How the heck do you add a hyperlink via Luacom ?

The VBA macro code to do it is

Range("L8").Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="http://www.bbc.co.uk/", TextToDisplay:="BBC"

I have been guessing how to translate this to a luacom call

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

sheet.Cells(1, 1):Select()

-- just complete guess here
excel.Selection.Hyperlinks:Add(excel.Selection, {Address="http://www.bbc.co.uk/", TextToDisplay="BBC"} ) -- put link

Of course total guesswork fails miserably !

Hopefully one of you Lua experts can solve this tricky one ? Any help would be greatly appreciated again, thanks

Geoff

From: spammealot1@live.co.uk
To: lua-l@lists.lua.org
Date: Tue, 28 Apr 2015 21:06:38 +0100
Subject: RE: Frustration with Luacom and Excel

Hi

Thanks for the replies, and the code snippets.

On the subject of the mysterious first parameter of the AddLine () function, I had the same hunch that instead of calling with the . method I should be calling with the : style. However at the time when I tried it I thought it produced a COM error.
In reality though some other issue caused the COM error, and I hadn't realised that at the time.

Now I have fixed the other issues, I can confirm that AddLine should be called using the : object method style.

My next task is to generate a couple of graphs via Luacom, that's going to be a challenge, cant say I am greatly looking forward to it. I will likely be back here soon, with more Luacom Excel questions :)

Geoff

> From: Thomas.Buergel@varian.com
> To: lua-l@lists.lua.org
> Date: Mon, 27 Apr 2015 16:20:26 +0000
> Subject: RE: Frustration with Luacom and Excel
>
> > This still leaves a bunch of unanswered questions though
> >
> > 1) Why are the AddLine params scrambled compared to MSDN docs ?
>
> Don't know, haven't seen that.
>
> But yes, it is a bit frustrating. One thing I noticed is that in some cases one has to use the self-call (with object:method) while other times it's the regular table access (object.field). It mostly, but not always, corresponds to "method" vs. "property" in the COM world... if it doesn't behave properly, I try the other way around. Your fake first parameter would seem to indicate that it should be a colon-call, not a dot-call.
>
> > 2) Luacom seems to have reference to the Excel Enums but I couldn't get
> > it to work and had to use 2 rather than msoArrowheadTriangle.
> > What I am doing wrong on the enums ?
>
> I got /some/ enums with the following piece of code (uncomment the commented out block to see what I mean). msoXXX definitions are not in there, I suspect they may be defined in a different object than Excel.Application but never bothered to try to find them.
>
> function GetExcel()
> local excel = luacom.GetObject("Excel.Application")
> if not excel then
> excel = luacom.CreateObject("Excel.Application")
> end
>
> if not excel then
> return false, "Can't instantiate Excel"
> end
>
> local typeinfo = luacom.GetTypeInfo(excel)
> local typelib = typeinfo:GetTypeLib()
> local enums = typelib:ExportEnumerations()
> --[[ debugging - dump enumerations
> for k,v in pairs(enums) do
> print(k,v)
> if type(v)=="table" then
> for l,w in pairs(v) do
> print(" ", l, w)
> end
> end
> end
> --]]
> return excel, enums
> end
>
> > 3) Writing Lua code like this, largely by trial and error of syntax sucks !
> > Are there any tips for doing this more scientifically with less trial and error
> > guessing ?
>
> If you look into the LuaCOM source, there are some guesses as to how to map the COM world into/out of the Lua world. Sometimes it makes assumptions that don't quite work.
>
> My experience has been that starting out with Excel/LuaCOM has a steep learning curve, but once you get into it it's not as bad.
>
> Practice makes perfect...?
>
>
> > 4) Does anyone have any general snippets of Lua/Excel code
> > that they would care to donate by posting here?
>
> I have some but it's too much/intertwined with application code to post. Maybe one of these days I'll get a moment to extract the relevant bits; if that happens, I'll post it.
>
> Cheers,
> Tom
>
>
> Thanks
>
> Geoff
>
>
>
>
> ________________________________________
> From: spammealot1@live.co.uk
> To: lua-l@lists.lua.org
> Date: Mon, 27 Apr 2015 12:58:36 +0100
> Subject: Frustration with Luacom and Excel
> Hi
>
> I have been trying to use Luacom to create a moderately complex Excel spreadsheet, its proving to be a very frustrating and difficult task largely due to the lack of documentation and simple examples to follow. I have scoured the Internet and there only seems to be about 3 very simple snippets of lua code showing how to use it with Excel. I found that pretty surprising as I am guessing the main usage of Luacom is to be able to hook up Lua and Excel.
>
> I made some progress largely by guesswork of the Luacom Excel API syntax. I am currently stuck on how to draw arrows on the Excel spreadsheet. I can draw lines with the following code but cant figure out how to turn the lines into arrows
>
> require "luacom"
>
> excel = luacom.CreateObject("Excel.Application")
> local wb = excel.Workbooks:Add()
> local ws = wb.Worksheets(1)
> excel.Visible = true
> excel.DisplayAlerts = false
>
> -- MSDN Docs says (BeginX, BeginY, EndX, EndY)
> -- For Luacom though its (unknown, xEnd, yEnd, xStart, yStart)
> x = ws.Shapes.AddLine( 1, 200, 100, 50, 10 )
> x:Select()
>
> --  ws.Shapes.ShapeRange.Line.EndArrowheadStyle = 2 -- msoArrowheadTriangle
> x = ws.Shapes.AddLine( 1, 100, 100, 50, 10 )
> x:Select()
>
> I cant figure out the syntax for setting the arrowheadstyle, it must be something vaguely like
>
> ws.Shapes.ShapeRange.Line.EndArrowheadStyle = 2 -- msoArrowheadTriangle
>
> For reference the vba macro code to do the same thing is
>     ActiveSheet.Shapes.AddLine(271.2, 120.6, 423, 214.2).Select
>     Selection.ShapeRange.Line.EndArrowheadStyle = msoArrowheadTriangle
>
>
> A secondary question. Can anyone explain why the params are different for the AddLine function compared to what the MSDN COM documentation ? From trial and error I figured out there is a mysterious unknown first param that doesn't seem to do anything and then the 4 remaining co-ords are juggled compared to MSDN docs.
>
> How the heck am I supposed to know that for Lua usage you have to use 5 params and juggle em around ? It took me ages of trial and error to stumble on that solution !!
>
> Any help most appreciated, thanks.
>
> P.S
> I think it would be useful to create a new message thread here where folks could post some simple working snippets of Lua code showing some different Excel features. Are there enough people here that have used Luacom/Excel and would be willing to post a few snippets of code ? It would greatly add to the Internet Knowledge base on this topic even if it was only a handful of scripts
>
> Geoff
>
>
>
>
>
>
>