[Home] [Downloads] [Search] [Help/forum]

Register forum user name Search FAQ

Gammon Forum

[Folder]  Entire forum
-> [Folder]  MUSHclient
. -> [Folder]  Jscript
. . -> [Subject]  Writing to Excel
Writing to Excel

It is now over 60 days since the last post. This thread is closed.     [Refresh] Refresh page

Posted by Sirius096   (12 posts)  [Biography] bio
Date Sun 20 May 2007 10:16 AM (UTC)
Message
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.
[Go to top] top
Posted by Shaun Biggs   USA  (644 posts)  [Biography] bio
Date Reply #1 on Sun 20 May 2007 03:34 PM (UTC)
Message
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 is much easier to fight for one's ideals than to live up to them.
[Go to top] top
Posted by Meerclar   USA  (733 posts)  [Biography] bio
Date Reply #2 on Sun 20 May 2007 07:09 PM (UTC)
Message
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.
Meerclar - Lord of Cats
Coder, Builder, and Tormenter of Mortals
Stormbringer: Rebirth
storm-bringer.org:4500
www.storm-bringer.org
[Go to top] top
Posted by Sirius096   (12 posts)  [Biography] bio
Date Reply #3 on Mon 21 May 2007 03:02 AM (UTC)

Amended on Mon 21 May 2007 03:03 AM (UTC) by Sirius096

Message
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.
[Go to top] top
Posted by Sirius096   (12 posts)  [Biography] bio
Date Reply #4 on Mon 21 May 2007 11:15 AM (UTC)
Message
I also can't figure out how to make it stop opening a new window each time. Rather annoying.
[Go to top] top
Posted by Shaun Biggs   USA  (644 posts)  [Biography] bio
Date Reply #5 on Mon 21 May 2007 01:35 PM (UTC)
Message
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?

ExcelApp = new ActiveXObject("Excel.Application")
ExcelSheet = new ActiveXObject("Excel.Sheet")
ExcelSheet.Application.Visible = true;
i = 1

function test() {
  ExcelSheet.ActiveSheet.Cells( i, 1 ).Value = "foo"
  i = i + 1
}
It is much easier to fight for one's ideals than to live up to them.
[Go to top] top
Posted by Sirius096   (12 posts)  [Biography] bio
Date Reply #6 on Tue 22 May 2007 02:21 AM (UTC)
Message
Have to be able to define what ExcelSheet is, which I cant seem to do without creating a new workbook/sheet every time.
[Go to top] top
Posted by Shadowfyr   USA  (1,786 posts)  [Biography] bio
Date Reply #7 on Wed 23 May 2007 03:03 AM (UTC)
Message
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:

ExcelApp = new ActiveXObject("Excel.Application")
i = 1

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

Or even:

i = 1

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


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. ;)
[Go to top] top
Posted by Fletchling   Australia  (54 posts)  [Biography] bio
Date Reply #8 on Thu 24 May 2007 09:40 AM (UTC)
Message
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.
[Go to top] top

The dates and times for posts above are shown in Universal Co-ordinated Time (UTC).

To show them in your local time you can join the forum, and then set the 'time correction' field in your profile to the number of hours difference between your location and UTC time.

32,081 views.

It is now over 60 days since the last post. This thread is closed.     [Refresh] Refresh page

Go to topic:           Search the forum

[Go to top] top

Quick links: MUSHclient. MUSHclient help. Forum shortcuts. Posting templates. Lua modules. Lua documentation.

Information and images on this site are licensed under the Creative Commons Attribution 3.0 Australia License unless stated otherwise.

[Home]

Written by Nick Gammon - 5K   profile for Nick Gammon on Stack Exchange, a network of free, community-driven Q&A sites   Marriage equality

Comments to: Gammon Software support
[RH click to get RSS URL] Forum RSS feed ( https://gammon.com.au/rss/forum.xml )

[Best viewed with any browser - 2K]    [Hosted at HostDash]