Link with excel

[javisuesk]

Hello!

I am creating a new project and I have two questions:

1) How can I get the value of a determined cell from excel to AMS? for instance from the cell: 1 row, 1 column.

2) How can I export a Grid from AMS to an excel archive?



Thanks!!

[BioHazard]

... How can I get the value of a determined cell from excel to AMS? for instance from the cell: 1 row, 1 column.

With the LuaCOM plugin. Lua can't interact with Excel by itself, but with help from the LuaCOM plugin, you can pass data back & forth between AMS and Excel easily enough.

So, first download the attached LuaCOM.zip. Unzip and install the plugin to AMS Plugins/Actions folder. Then have a look at my attached .apz demo. Shows how to use LuaCOM to:

1. open an instance of Excel
2. write data from AMS to specified cells, rows, or columns in Excel
3. read data from specified cells, rows, or columns in Excel back into AMS
4. plus an example of some fancy stuff (creating a 'matrix-like rain' in Excel from AMS)

How can I export a Grid from AMS to an excel archive?

Not sure about that one. The Grid object always gives me a headache. But if you're already familiar with Grid object functions, you shouldn't have too many problems adapting from my code. See how you go.

[BioHazard]

Edit,
Oh, and welcome to the forum.
There - who ever said I wasn't a nice guy!

[javisuesk]

Thanks for the quick and detailed answer!!
It will help me for sure.

[sameer valva]

Hi Bio, I Tried to target a existing excel file and replaced the first line

excel = luacom.CreateObject("Excel.Application")

with

excel = luacom.GetObject("Excel.Application")

but didn't work ( as always im the problem )

Can luaCOM access an Excel file without open it ? I noticed that it can hide the file when opened but if it can access the file without opening it will be better.

Thanks

[colc]

Hi and welcome to the forum @javisuesk,
I have a SQLite3 Database which I import into AMS via a grid object
It has 28 Columns (A1 to AC1) , adjust code to your needs

local oExcel = luacom.CreateObject("Excel.Application");
if oExcel then
    oExcel.Visible = 1;
    oExcel.WorkBooks:Add();
    oExcel.ActiveWorkbook.Sheets(1):Select();
    
    oExcel.ActiveSheet.Columns(1).ColumnWidth = 25;
    oExcel.ActiveSheet.Columns(2).ColumnWidth = 20;
    
    --oExcel.ActiveSheet.Cells(1,1).FormulaR1C1 = "Sample";    
    --oExcel.ActiveSheet.Cells(1, 2).FormulaR1C1 = "by";
    --oExcel.ActiveSheet.Cells(1, 3).FormulaR1C1 = "ZG";

    oExcel.ActiveSheet:Range("A1:AC1").Interior.ColorIndex = 6;
    oExcel.ActiveSheet:Range("A1:AC1").Font.Italic = "True";
    oExcel.ActiveSheet:Range("A1:AC1").Font.Bold = "True";
    oExcel.ActiveSheet:Range("A1:AC1").Font.Size = 12
    
    --oExcel.ActiveSheet.Cells(2, 1).FormulaR1C1 = "Ru-Board.Com";

    for i = 0, Grid.GetRowCount("Grid1") - 1 do
        for k = 0, Grid.GetColumnCount("Grid1") do
            oExcel.ActiveSheet.Cells(i+1 , k+1).FormulaR1C1 = Grid.GetCellText("Grid1", i, k);
        end
    end
end

Cheers

[BioHazard]

@javisuesk,
@sameer,

I've just noticed a bug with COM errors getting returned when the Excel file is closed prematurely. Still ironing out some kinks, guys. Also, I need to modify the Excel Dependency notification as Microsoft Excel (or equivalent XLS writer) is required - not the Excel Viewer (obviously). Will repost once I have it figured out.

[BioHazard]

Hi Bio, I Tried to target a existing excel file and replaced the first line

excel = luacom.CreateObject("Excel.Application")

with

excel = luacom.GetObject("Excel.Application")

but didn't work ( as always im the problem )

Are you opening a workbook in the existing Excel file? Or adding a new workbook to it? If it's the latter, the replacement code should be working fine. If however you're 'opening' an existing workbook, you'll have to tell LuaCOM that you're 'opening' rather than 'adding'. And you'll have to instruct it where to look. Eg.

excel = luacom.GetObject("Excel.Application"); 
wb = excel.Workbooks:[COLOR=#FF0000]Open[/COLOR]([COLOR=#FF0000]_DesktopFolder.."\\MyFile.xlsx"[/COLOR]);

Can luaCOM access an Excel file without open it ? I noticed that it can hide the file when opened but if it can access the file without opening it will be better.

Not in the way you're suggesting (at least i don't think so?). The Excel Object Model has to be accessed somehow. And that means spawning a process. So, although that means it'll still be visible in System Processes, by setting the object's Visibility property to'false', it amounts to a 'closed file' as far as the end user's concerned. Furthermore, once the file is closed, the table of values being returned from the Excel Object Model must be reset to 'nil', followed by a collectgarbage() command to release and terminate the process properly. So a physically closed file means no values to return. If it could read an Excel file the same way Lua reads a textfile, we could make use of the IO library. Unfortunately, that's not the case.

PS.
@javisuesk,
Should have that revised .apz demo up in a few days or so. It's throwing COM errors because of insufficient cross-checking for instances of when user manually closes the Excel file and then tries to rerun a read/write command. It also needs more accurate scripting of the collectgarbage() commands. Busy with workstuff at the moment but will get to it, eventually.

[sameer valva]

Big Hug man .

Hiding the file will do the job now, ill do some tests today and give feedback.

Thanks

[BioHazard]

Right, have finally had some time to have another ***** at eliminating those COM exceptions and the news is not good.

As pointed out, the example provided above will work AOK, provided the user does not prematurely close the Excel file and re-attempt one of the commands while the Excel file is still closed. Because that's when it throws the COM errors.

The problem exists that even though an Excel file may be manually closed by the user, it still runs in System Processes. So error-checking for nil value returns won't work because LuaCOM holds values in memory until they are released, either thru a SystemTerminateProcess() command or via re-scripting the variables to be set as nil followed by a collectgarbage() command.

I've also tried enumerating system-processes & window-titles to catch instances of when the target Excel file has been prematurely closed. But given that Excel can still be running in SystemProcesses without a window-title to even capture, the whole process becomes extraordinarily complicated - beyond the point a commonsense for such a basic task.

I'm not saying that it can't be done. And imagine there's probably a pretty simple solution actually available, somewhere. But I've combed through all available documentation for LuaCOM and cannot find anything which demonstrates how to code for these kinds of COM exceptions, let alone the correct syntax for it.

Sorry guys - but this one's beyond my paygrade. Next.

[BioHazard]

Update,

Had a nagging feeling something fairly simple was eluding me with this - so went back in for a final look. Stepping away for a couple of days provided some much needed clarity to fix those COM errors (among others). And stumbled across an old tip left by Reteset when working with LuaCOM: Apparently, it has a built-in feature to silently repress those annoying COM errors (and API errors too) viz,

luacom.config.abort_on_error = false;
luacom.config.abort_on_API_error = false;

For more info on this, look up Exception Handling on pg.22-23 of the LuaCOM manual:

http://files.luaforge.net/releases/luacom/luacom/1.4/luacom-1.4-doc.pdf

Anyway, try this updated demo - it's much, much better now. Can't guarantee that it's absolutely bug-free (has proved to be quite the challenge) but it's sure getting close.

Nb.
That built-in Excel Dependency check (used in the first demo) can't distinguish between Excel and Excel Viewer. So, dropped a custom function into Globals which'll now make that distinction.

[BioHazard]

OOPS - wrong update! Sorry.
Grab this one, instead:

[BioHazard]

GRRR! Wrong version again.
How do ya tell if BioHazard is up past his bedtime?

LOL, 3rd time's a charm. THIS is the one I meant to upload:
Zzzz - now, I'm going to sleep!

[sameer valva]

Great Job Bio, Ive been off these two days, and now returned. I will try your project and return to you.

[BioHazard]

@sameer

Just a word of caution with this latest demo, mate. I think it's going to need one last attempt at refining. If you play around with it long enough, you'll find it still returning an error or two. Thing just about has me whipped - but I'll have another stab at it to see if i can't clean those up too. Also, I forgot to code in the commands to properly release any Objects still running in memory. I think it needs code to reset all values to nil (and a collectgarbage() command) in the On Shutdown event.

Let me know if you find it returning errors anywhere.
Merry Xmas, man.