t1ck3ts Posted December 19, 2012 Share Posted December 19, 2012 (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 December 19, 2012 by t1ck3ts Link to comment Share on other sites More sharing options...
water Posted December 19, 2012 Share Posted December 19, 2012 Can you post the exact statement you use? _ExcelBookOpen without parameters doesn't work. My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
t1ck3ts Posted December 19, 2012 Author Share Posted December 19, 2012 (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 December 19, 2012 by t1ck3ts Link to comment Share on other sites More sharing options...
water Posted December 19, 2012 Share Posted December 19, 2012 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 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
t1ck3ts Posted December 19, 2012 Author Share Posted December 19, 2012 (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. 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 Link to comment Share on other sites More sharing options...
t1ck3ts Posted December 19, 2012 Author Share Posted December 19, 2012 (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 December 19, 2012 by t1ck3ts Link to comment Share on other sites More sharing options...
water Posted December 19, 2012 Share Posted December 19, 2012 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 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
t1ck3ts Posted December 19, 2012 Author Share Posted December 19, 2012 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. Link to comment Share on other sites More sharing options...
water Posted December 19, 2012 Share Posted December 19, 2012 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 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now