Re: luacom and excel

[David Manura <dm.gmane@...>]

Wim Couwenberg <w.couwenberg <at> chello.nl> writes:
> I remember having this same problem when I switched to a newer
> version of Excel.  Try to use the "Value2" property instead...

Thanks!  That does the trick (Excel 2002).  Here's my code for anyone interested:

  -- create scatterplot in Excel
  require('luacom')
  local excel = luacom.CreateObject("Excel.Application")
  excel.Visible = true
  local wb = excel.Workbooks:Add()
  local ws = wb.Worksheets(1)
  for row=1,100 do
     ws.Cells(row,1).Value2 = math.random()
     ws.Cells(row,2).Value2 = math.random()
     -- note: using ".Value2" since ".Value" fails
     -- with type mismatch error. why?
  end
  local chart = excel.Charts:Add()
  chart.ChartType = -4169  -- scatter XY
  chart.HasLegend = 0
  local range = ws.UsedRange
  chart:SetSourceData(range, 2)

Some data type issue:
http://msdn2.microsoft.com/en-us/library/microsoft.office.interop.excel.range.value2.aspx
-- "The only difference between [the Value2] property and the Value property is
that the Value2 property doesn’t use the Currency and Date data types. You can
return values formatted with these data types as floating-point numbers by using
the Double data type."

I guess I'll work on patching luacom to get it to compile in lua 5.1 now.

--davidm