RE: Frustration with Luacom and Excel

Its always guaranteed to work, post a question on the Internet and then you will stumble on the answer within 5 minutes.

I just got it to work with the following code

x = ws.Shapes.AddLine( 1, 200, 100, 50, 10 )
x:Select()
excel.Selection.ShapeRange.Line.EndArrowheadStyle = 2 -- msoArrowheadTriangle

This still leaves a bunch of unanswered questions though

1) Why are the AddLine params scrambled compared to MSDN docs ?

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 ?

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 ?

4) Does anyone have any general snippets of Lua/Excel code that they would care to donate by posting here?

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