RE: Frustration with Luacom and Excel

Hi Thomas

Was that a typo in your reply ? you said "version of Excel, which, in my case, is 2003"

I said I am also using 2003 in my earlier email.

I hadn't realised MS had changed the API so fundamentally across Excel versions. That was a bright move on their part Sighhhhhhhh.

Geoff

> From: Thomas.Buergel@varian.com
> To: lua-l@lists.lua.org
> Date: Mon, 4 May 2015 13:59:35 +0000
> Subject: RE: Frustration with Luacom and Excel
>
> Hi Geoff,
>
> My setup is mostly the same (LuaForWindows 5.1.46, which has LuaCOM 1.4 on Windows 7). The major difference between our setups is the version of Excel, which, in my case, is 2003. That would explain the different behaviors between our setups, as we're remote-controlling features/functionality in a COM server, which has a different version.
>
> Documentation on Excel 2003 does indeed seem very hard to come by. I found a copy of a scanned book (using Google search terms "excel 2002 vba programmer's reference pdf"), which had some VBA examples for charts - similar to the one you linked. Apparently Excel 2003 and 2002 are very similar.
>
> Looking at that, it seems like there's one indirection less in the Excel 2003 code. So if I'd had to wager a guess, I would translate it to LuaCOM as:
>
> local chart2003 = graphsheet:ChartObjects():Add(xOffset, yOffset, width, height) -- note, no trailing ".Chart"
> local chart2010 = chart2003.Chart
> -- set the title
> chart2003.HasTitle = true
> chart2003.ChartTitle.Text= graphsheet.Name .. " for " .. datasheet.Name
>
> Others seem to have noticed differences between Excel 2003 and later versions, for example:
> http://stackoverflow.com/questions/2828722/exception-in-creating-excel-chart-by-c-sharp
>
> Also, it seems in the samples that I have seen that the chart is always "Activate()"ed. I don't know if this is just necessary in Excel 2003; a similar problem (albeit described for VBA) is here:
>
> http://www.mrexcel.com/forum/excel-questions/81168-chart-question-excel-2003-a.html
>
> Maybe that's the key?
>
> Sorry, I can't try that, I don't have Excel 2003 handy. In general I look at the VBA samples, squint my eyes and try to translate it to Lua, which mostly works almost right away...
>
> As for the enums:
>
> > 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.
>
> Yes, they are created from the type library inside the GetExcel function:
>
> > 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
> > --]]
>
> At least with Excel 2003's automation interface, I get all the xlXXX and rgbXXX enums defined. Not sure where the msoXXX ones are hiding, but they aren't exposed this way.
>
> Cheers,
> Tom
>
> ---
>
> From: lua-l-bounces@lists.lua.org [mailto:lua-l-bounces@lists.lua.org] On Behalf Of Geoff Smith
> Sent: Monday, May 04, 2015 1:06 PM
> To: Lua mailing list
> Subject: 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
> >
> >
>