Writing to Excel

Anyway I can open an existing excel sheet and add what I want to the next line?

So far I have only been able to make a new sheet and print to specific cells.

I'm not quite sure how to have anything printed to an Excel sheet, but if you can print to specific cells, you might just want to have a variable keep track of the last cell you printed to. Then just increment the row, and you're all set to go to the next line.

It can be done with SQL statements but yer gonna have to show us what you have so we have something to work from. It's not gonna be pretty or simple but it is possible.

Ok, so to write to a specific cell of a NEW excel sheet I've done this:

function test() {
ExcelApp = new ActiveXObject("Excel.Application")
ExcelSheet = new ActiveXObject("Excel.Sheet")
ExcelSheet.Application.Visible = true;
ExcelSheet.ActiveSheet.Cells(1,1).Value = "blah";
}


Now, I can use variables to track the last cell I input data to... would be easier if it just adds to the next row though, heh.

So my main problem is opening a specific excel file and writing to the specific row.

I also can't figure out how to make it stop opening a new window each time. Rather annoying.

Isn't that because you have it set to a new ActiveObject each time? Can you initialize that in a separate function, then just keep referencing ExcelSheet with that test function?

Have to be able to define what ExcelSheet is, which I cant seem to do without creating a new workbook/sheet every time.

That, to be frank, doesn't make any sense. Both ExcelApp and ExcelSheet are "object handles". Unless you specifically close the script engine entirely *or* you intentionally set them to something else, they should continue to point at the same things.

Now, what Shaun is suggesting is that your code is actually doing something like:



This *won't* work. The reason is what is called "scope". Basically, in most languages a variable is only "global", which is to say accessible to all parts of your script/program if defined "before" any functions. Shaun's example does this. It creates the variable *first*, then defines a function called test(). This means that things happen this way:

main -> create ExcelApp
main -> create ExcelSheet
main -> define function test()

mushclient -> call test()
test -> Set cell information.
test -> exit

Now, if you are doing *either* of the two ways above, then what is happening is roughly this:

main -> define function test()

mushclient -> call test()
test -> create ExcelApp
test -> create ExcelSheet
test -> Set cell information.
test -> **delete** ExcelSheet
test -> **delete** ExcelApp
test -> exit

The worksheet remains "open" because Excel is also a stand alone application, so it will remain visible "unless" you specifically hide or close it. Basically, objects have "reference counts". When you open something like Excel, it does:

Excel -> Add to count, from jscript.
Excel -> Add to count, from *self*.

Excel and the worksheet you opened with it will "only" close if *both* references are released. Merely deleting ExcelSheet or even ExcelApp *only* does:

Excel -> Delete from count, jscript.

Since *self* still exists, it looks like your script is still connected to Excel, but you have in fact "lost" the connection. That is why you are having to open the new worksheet every time.

Mind you, it would be a lot easier to see what you are actually doing wrong if you posted the code itself, instead of just describing what is going wrong. ;)

While we are on this topic, I'd very much appreciate any guidance on how to read and write to nominated excel worksheet;workbook,rows/columns/ranges. Using Lua.

Thanks.