Adding/removing data from an existing excel document.

[t1ck3ts]

Hi Guys!

Ok, im new to the whole AutoIt scene but what i can tell you is, im loving it! I've been up for hours on end just tinkering and getting myself

familiar with this amazing software.

So far i have made a little app that does my weekly & monthly reports for me! hahaha if not why not! is what i say!

I have done this by just reading up on the doumentation and using that amazing little window tool, so far i have done EVERYTHING i needed

just from that, thats how crazy awesome your documentation is!

Now, i just need the app to use an already existing excel doument, with code inside it that adds rows and colums up in totals and such.

When i try and use the

_ExcelBookOpen

option, it pretty much removes all my code, data and just throws the new info in.

I need it to just add the new data into the blocks, removing the old data inside that block and saving the document. The excel document

already has my calculations and macro code inside it to do the rest (which makes a nice graph on another tab inside the document)

Is there something i am doing wrong here? Is there another option i should be using? Any help would be greatly apreciated :3

Edited December 19, 2012 by t1ck3ts

[water]

Can you post the exact statement you use? _ExcelBookOpen without parameters doesn't work.

[t1ck3ts]

Can you post the exact statement you use? _ExcelBookOpen without parameters doesn't work.

Sorry, i was just refering to that code's function Currently this is what i am using:

#include <Excel.au3>

Local $oExcel = _ExcelBookNew(0)

_ExcelSheetActivate($oExcel, 3)
; code to get info from software goes here
_ExcelWriteCell($oExcel, ControlTreeView("Open File", "", "SysTreeView321", "GetItemCount"), 1, 1)

_ExcelBookSaveAs($oExcel, "C:Recording Status M E " & @MON & "-" & @YEAR, "xls", 0, 0)
_ExcelBookClose($oExcel)

Before i was using this:

#include <Excel.au3>

Local $sFilePath1 = "C:Template.xls"
Local $oExcel = _ExcelBookOpen($sFilePath1)

_ExcelSheetActivate($oExcel, 3)
; code to get info from software goes here
_ExcelWriteCell($oExcel, ControlTreeView("Open File", "", "SysTreeView321", "GetItemCount"), 1, 1)

_ExcelBookSaveAs($oExcel, "C:Recording Status M E " & @MON & "-" & @YEAR, "xls", 0, 0)
_ExcelBookClose($oExcel)

Edited December 19, 2012 by t1ck3ts

[water]

This works fine for me:

#include 

Local $oExcel = _ExcelBookNew(0)

_ExcelSheetActivate($oExcel, 3)
; code to get info from software goes here
;_ExcelWriteCell($oExcel, ControlTreeView("Open File", "", "SysTreeView321", "GetItemCount"), 1, 1)
_ExcelWriteCell($oExcel, "**", 1, 1)
_ExcelBookSaveAs($oExcel, "C:Recording Status M E " & @MON & "-" & @YEAR, "xls", 0, 0)
_ExcelBookClose($oExcel)

What you need in your script is some error checking. Did each function run successfully? Check the return value or @error and @extended after calling each function.

[t1ck3ts]

This works fine for me:

#include

Local $oExcel = _ExcelBookNew(0)

_ExcelSheetActivate($oExcel, 3)
; code to get info from software goes here
;_ExcelWriteCell($oExcel, ControlTreeView("Open File", "", "SysTreeView321", "GetItemCount"), 1, 1)
_ExcelWriteCell($oExcel, "**", 1, 1)
_ExcelBookSaveAs($oExcel, "C:Recording Status M E " & @MON & "-" & @YEAR, "xls", 0, 0)
_ExcelBookClose($oExcel)

What you need in your script is some error checking. Did each function run successfully? Check the return value or @error and @extended after calling each function.

Yeah, the code i am currently using now, works.

The only problem is that its making a new excel document with no graph tabs or calculations already inside it. See below, this is my existing excel document with manually inputed

numbers done by myself.

I would like my little app to remove (overwrite) the numbers inside those cells with the new ones its found (the numbers change each month) but i would like

it to do it without breaking the calculations i have already put inside the previous document.

The calculation bit is below each row, calculating all numbers and giving a total at the bottom.

So its going to have to use the

_ExcelBookOpen

. But when i use it, it still removes all things already inside the excel document.

Edited December 19, 2012 by t1ck3ts

[t1ck3ts]

Oh gosh!

Im a complete and utter derp! Thanks for the help Water! You were right in saying i should check error's!

GEEEEEEEZ! I feel like a complete and utter twit! >.<

Really sorry for wasting you time

PS: Been looking at your Active Directory! I am sooo going to use that for many of the things i plan on doing ^____________^

Edited December 19, 2012 by t1ck3ts

[water]

Another question. The file you want to open is named "C:Template.xls".

Do you want to open this file or create a new one with this file as a template?

[t1ck3ts]

Another question. The file you want to open is named "C:Template.xls".

Do you want to open this file or create a new one with this file as a template?

I see where the problem comes in.

It seems that it cant handle the new .xlsx extension :/

I have to save it in the old '97-2003 extension.

[water]

That's true. The current Excel UDF doesn't support the new file formats.

That's why I'm doing a complete of the Excel UDF at the moment. The version I posted is an early alpha. But if you like you can use the new UDF and I will be happy to help with any problems.

This would help me as well to enhance the UDF.