andy09 Posted February 4, 2009 Share Posted February 4, 2009 Hi, I have an excel file rawdata.exl that gets overwritten every 10th second. (I can't do much about this) I copy values from rawdata.xls by opening it with _excelBookOpen and reads the values I want into an array. I've to close rawdata.xls each time in order for windows to overwrite it using _excelBookClose() I takes quite a long time for windows to open and close this file all the time. Is there a faster way using other excel commands? Best rergards Andy Link to comment Share on other sites More sharing options...
Spiff59 Posted February 4, 2009 Share Posted February 4, 2009 You'd want to avoid using those functions over and over when processing massive numbers of spreadsheets or frequent updates, as in your case. They start and restart the Excel application each time called. I think this is basically the process flow you'll need: $oExcel = ObjCreate("Excel.Application") ; Start Excel $oExcel.Visible = 0 While 1 ; Repeatedly update workbook $oExcel.WorkBooks.Open($FilePath) ; Do your work here... $oExcel.ActiveWorkbook.$Save $oExcel.ActiveWorkbook.$Close Wend $oExcel.Quit ; End Excel Link to comment Share on other sites More sharing options...
PsaltyDS Posted February 4, 2009 Share Posted February 4, 2009 If you don't have to catch every single change, then you could also do a FileCopy() to temp file and work with that for longer than the time between updates. Since you are performing read only operations on the data, staying in sync with the updates to the primary file is not an issue. Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law Link to comment Share on other sites More sharing options...
andy09 Posted February 4, 2009 Author Share Posted February 4, 2009 Hi, Spiff59, I would like to get a solutio nas you described to work. Allthough, I can't find the command .WorkBooks.Open($FilePath) In the help file. and also I can't get it to work. Is there a command like that in autoit or are you referring to something else? Best regards Andy Link to comment Share on other sites More sharing options...
Spiff59 Posted February 4, 2009 Share Posted February 4, 2009 (edited) Hi, Spiff59, I would like to get a solutio nas you described to work. Allthough, I can't find the command .WorkBooks.Open($FilePath) In the help file. and also I can't get it to work. Is there a command like that in autoit or are you referring to something else? Best regards Andy Those commands are models/objects/methods built into Excel's COM+ interface. Once you've created an excel object ($oExcel in the example), you prefix the excel COM+ commands with that object name (or other objects built off the original object) and excel interprets the commands. The commands to use COM+, like ObjCreate(), are built into Autoit, the specific excel objects and methods are not. In the "Do your work here..." section of the example, you'd be interested in commands like: $cellvalue = $oExcel.Activesheet.Range("B1").Value; read a cell value ; or using a different manner of specifying the target cell range: $cellvalue = $oExcel.Activesheet.Range($row, $column).Value; read a cell value $oExcel2.Activesheet.Range($row, $column).Value = $x; write a cell value The MSDN reference for Excel's COM+ interface begins here I believe: Excel Object Model Overview EDIT: Where and what error are you getting? I've never had the ObjCreate fail yet, so don;t bother with an "IsObj()" test after it anymore. Sure you have your pathname to the .xls file corrent? Edited February 4, 2009 by Spiff59 Link to comment Share on other sites More sharing options...
andy09 Posted February 4, 2009 Author Share Posted February 4, 2009 THanks, I'll look into this. But do I neeed to declare anything in the beginning of the script to sue these excel COM+ commands (Include or similar in the head of the script file)?? I've tried this bu it doesn't work. The program complains about the declaration of variables. Maybe I can't use the array script inside the "workbooks open /close"? CODE$sFilePath1 = "C:\Test.xls" $oExcel.WorkBooks.Open($FilePath1) $aArray1 = _ExcelReadArray($oExcel, 2, 4, 70, 1) ;Direction is Vertical $oExcel.ActiveWorkbook.$Save $oExcel.ActiveWorkbook.$Close Looking forward to your next reply. Thank you so much for everything so far. Br Andy Link to comment Share on other sites More sharing options...
Spiff59 Posted February 4, 2009 Share Posted February 4, 2009 (edited) THanks, I'll look into this. But do I neeed to declare anything in the beginning of the script to sue these excel COM+ commands (Include or similar in the head of the script file)?? I've tried this bu it doesn't work. The program complains about the declaration of variables. Maybe I can't use the array script inside the "workbooks open /close"? CODE$sFilePath1 = "C:\Test.xls" $oExcel.WorkBooks.Open($FilePath1) $aArray1 = _ExcelReadArray($oExcel, 2, 4, 70, 1) ;Direction is Vertical $oExcel.ActiveWorkbook.$Save $oExcel.ActiveWorkbook.$Close Looking forward to your next reply. Thank you so much for everything so far. Br Andy You're not creating the initial object or link to the excel application, $oExcel is yet undefined in your example. You need the line: $oExcel = ObjCreate("Excel.Application"); Start Excel If you prefer the excel window be visible: $oExcel.Visible = 0 Stick some of that in front of your test code. Looking at "program files/autoit/include/excel.au3" will show you what any function included in the Excel UDF is actually doing. I'll often chop pieces out of those functions. I would think as long those functions aren't destroying the $oExcel object you wish to keep active, that you can mix those functions in. EDIT: Oops, I meant "If you prefer the excel window be invisible:", and you've got an "s" in front of the pathname in your declaration, but no "s" in the .Open command. Edited February 4, 2009 by Spiff59 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