lua-users home
lua-l archive

[Date Prev][Date Next][Thread Prev][Thread Next] [Date Index] [Thread Index]


Does oleview provide some insights?

Op 27 apr. 2015 14:37 schreef Geoff Smith <spammealot1@live.co.uk>:
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