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