Sign in to follow this  
Followers 0
t1ck3ts

Adding/removing data from an existing excel document.

9 posts in this topic

#1 ·  Posted (edited)

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 by t1ck3ts

Share this post


Link to post
Share on other sites



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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

#3 ·  Posted (edited)

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 by t1ck3ts

Share this post


Link to post
Share on other sites

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.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

#5 ·  Posted (edited)

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.

Posted Image

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 by t1ck3ts

Share this post


Link to post
Share on other sites

#6 ·  Posted (edited)

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 by t1ck3ts

Share this post


Link to post
Share on other sites

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?


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0