RE: Frustration with Luacom and Excel

Hello Thomas

Firstly I wanted to say thanks very much for trying to help me out with this problem. Even if I cant solve my specific problem, this chart example will help other people in the future, its the only sample code on the web !

I still get the same error(s)

lua: COM exception:(.\src\library\tLuaCOM.cpp,394):Unable to set the HasTitle property of the Chart class

OK so this must be some sort of version discrepancy between my setup and yours. Lets list the versions I am using here see if anything obvious jumps out.

I am using Windows 7, Lua 5.1, Excel 2003

I just downloaded and installed Lua For windows the very latest version so that should mean I am using Luacom V1.4.

Tried again, still same error unknown HasTitle property. I renamed luacom.dll to junk name, and ran again, as expected it complained it couldn't find luacom. So proving I haven't got a pathing problem and using some old out of date luacom I didn't know I had.

OK so that points maybe at my ancient version of Excel2003 being the culprit. Maybe that property wasn't defined for Excel2003 ? Did a bit of digging around and found this link

https://msdn.microsoft.com/en-us/library/office/aa173287(v=office.11).aspx

That seems to indicate in Excel 2003 there is definitely a chart "HasTitle" property.

At which point, I am stumped again. I cant see what the difference is between our setups that causes it to work for you but not for me ? Could you list your version numbers please ?

Incidentally, did you define anything to get the enums to work, for example xlenums.XlChartType.xlLine. Is it extracting that value from Excel via Luacom ? The enums are all unknown for my setup here.

Thanks again, any clues on what might be causing this annoying problem ?

Geoff

> From: Thomas.Buergel@varian.com
> To: lua-l@lists.lua.org
> Date: Mon, 4 May 2015 10:00:28 +0000
> Subject: RE: Frustration with Luacom and Excel
>
> Hi Geoff,
>
> You said:
>
> > I took Thomas's code snippet and started to try and adapt it to be a standalone small chart
> > demo in Lua. I cant see what I am doing wrong so if anyone could assist I would be most
> > grateful. The Internet is completely devoid of any example Lua scripts for making charts.
>
> Your code as posted worked for me. It didn't quite do what I think you would like to do but at least it doesn't error out.
>
> One thing that I noticed when remotely driving Excel is that it sometimes gets "stuck". The COM automation happens in a secondary instance of Excel.exe, and depending on how you use/abuse it, it sometimes remains confused across sessions. So, when I have strange COM exceptions that shouldn't be, I always first check if there's an instance of Excel.exe still running and terminate it before trying again.
>
>
> Since you started with my somewhat convoluted example (it was like that because of the somewhat unusual data content and arrangement I had when I wrote this code for a real application), I thought I'd give it a try to see if I can reduce it to a simple case from which you can extend.
>
> The following code...
> - generates three data series (3 columns, 100 rows each)
> - gives the data series a name
> - on a secondary sheet, adds a simple line chart
> - associates the data with the chart
>
> Note that I added the Excel enums again instead of redefining the used values again, mainly to avoid mistyping a definition and then chasing a non-bug because of that :)
>
>
> -------- begin simplechart.lua
> local luacom = require "luacom"
>
> local 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
>
> local excel, xlenums = assert(GetExcel())
>
>
> -- add a simple line chart from the data in "datasheet",
> -- place the chart in "graphsheet"
> local function AddGraph(graphsheet, datasheet)
>
> local width, height = 640, 320
> local xOffset, yOffset = 32, 32
> -- add the chart at the specified location
> local chart = graphsheet:ChartObjects():Add(xOffset, yOffset, width, height).Chart
> -- set the title
> chart.HasTitle = true
> chart.ChartTitle.Text = graphsheet.Name .. " for " .. datasheet.Name
> -- set the chart type
> chart.ChartType = xlenums.XlChartType.xlLine
>
> -- figure out how large our data set is
> local numRows = datasheet.Columns(1):End(xlenums.XlDirection.xlDown).Row
> local numColumns = datasheet.Rows(1):End(xlenums.XlDirection.xlToRight).Column
>
> -- this range describes the data set to add to the chart
> local sourceData = datasheet:Range(datasheet.Cells(1, 1), datasheet.Cells(numRows, numColumns))
> -- associate the source data with the chart
> chart:SetSourceData(sourceData)
>
> return chart
> end
>
> -- add a new workbook
> local book = excel.Workbooks:Add()
> -- use sheet 1 for the data
> local sheet1 = book.Worksheets(1)
> sheet1.Name = "Data"
> -- and sheet 2 for the graph
> local sheet2 = book.Worksheets(2)
> sheet2.Name = "Chart"
>
> excel.Visible = true
> excel.DisplayAlerts = false
>
> math.randomseed(os.time())
>
> -- create three columns, 100 entries each, of data
> for col=1, 3 do
> -- row 1: title
> sheet1.Cells(1, col).Value2 = ("Data series %d"):format(col)
> -- row 2-100: data
> for row=2, 100 do
> sheet1.Cells(row, col).Value2 = math.floor(math.random() * 100)
> end
> end
>
> -- add a graph on sheet 2, taking its data from sheet 1
> AddGraph(sheet2, sheet1)
>
> book:SaveAs("c:\\temp\\mygraph.xlsx")
> book:Close()
> -------- end simplechart.lua
>
>
> Hope this helps,
> Tom
>
>