Baraoic 0 Report post Posted September 22, 2008 Ok I was making a script to open an excel doc in read only and read a few cells. The problem is that the excel doc is updated different times so if its opened it wont refresh unless I close the document and reopen it. Doing that causes me to looks my variables then messing up the reading of the other variables. Heres what I have so far expandcollapse popup#include <Date.au3> #include <Excel.au3> #include <File.au3> Opt("OnExitFunc", "OnAutoItExit") If UBound(ProcessList(@ScriptName)) > 2 Then MsgBox(64, "* NOTE *", "Program already running", 5) Exit EndIf $DATE = StringReplace(_DateAdd('d', -1, _NowCalcDate()), "/", "") $ExcelDoc1 = "C:\Documents and Settings\me\Desktop\Counts.xls" $oExcel1 = _ExcelBookOpen($ExcelDoc1, 1, True) _ExcelSheetActivate($oExcel1, "DAILY COUNTS") $NowCell1 = _ExcelReadArray($oExcel1, 1, 2, 255, 0, 1) ;var2=start down 1, var3=start right 2, var4=end at 255 (MAX = 256 which is the Max # of cells excel can have if starting from cell 1) ;var5=default is 0 which is right (1=down), var6=sets index base of 0 or 1 of the array created ;starting index base at 1 makes $array[0] have the # of array objects $N1 = 1 $Error = False For $C1 = 0 To $NowCell1[0];Search all cells in the row If $NowCell1[$N1] = $DATE & "000000" Then;For some reason excell puts 6 0's at the end of the date ExitLoop Else $N1 = $N1 + 1 EndIf Next Do $DN = _ExcelReadCell($oExcel1, 14, ($N1 + 1)) If $DN = 0 Then _ExcelBookClose($oExcel1) Sleep(10000) $ExcelDoc1 = "C:\Documents and Settings\me\Desktop\Counts.xls" $oExcel1 = _ExcelBookOpen($ExcelDoc1, 1, True) _ExcelSheetActivate($oExcel1, "DAILY COUNTS") $DN = _ExcelReadCell($oExcel1, 14, ($N1 + 1)) ElseIf $DN < 80000 Or $DN > 200000 Then $DN = "ERROR: " & $DN $Error = True ExitLoop EndIf Until Not $DN = 0 MsgBox(0, "a", $DN) Do $DS = _ExcelReadCell($oExcel1, 15, ($N1 + 1)) If $DS = 0 Then _ExcelBookClose($oExcel1) Sleep(300000) $ExcelDoc1 = "C:\Documents and Settings\me\Desktop\Counts.xls" $oExcel1 = _ExcelBookOpen($ExcelDoc1, 1, True) _ExcelSheetActivate($oExcel1, "DAILY COUNTS") $DN = _ExcelReadCell($oExcel1, 14, ($N1 + 1)) ElseIf $DS < 8000 Or $DS > 20000 Then $DS = "ERROR: " & $DS $Error = True ExitLoop EndIf Until Not $DS = 0 MsgBox(0, "a", $Ds) $TN = _ExcelReadCell($oExcel1, 18) If $TN < 80000 Then $TN = "ERROR: " & $TN $Error = True EndIf $TS = _ExcelReadCell($oExcel1, 21) If $TS < 8000 Then $TS = "ERROR: " & $TS $Error = True EndIf _ExcelBookClose($oExcel1) Sleep(500) The problem is at $DN you can see if the number isn't updated then it will close the document, wait, then reopen thus making a new instance of it that $DS wont be able to use. I know there has got to be a better way to do this any help would be appreciated, thanks. Share this post Link to post Share on other sites
PsaltyDS 27 Report post Posted September 22, 2008 Ok I was making a script to open an excel doc in read only and read a few cells. The problem is that the excel doc is updated different times so if its opened it wont refresh unless I close the document and reopen it. Doing that causes me to looks my variables then messing up the reading of the other variables. Heres what I have so far CODE#include <Date.au3> #include <Excel.au3> #include <File.au3> Opt("OnExitFunc", "OnAutoItExit") If UBound(ProcessList(@ScriptName)) > 2 Then MsgBox(64, "* NOTE *", "Program already running", 5) Exit EndIf $DATE = StringReplace(_DateAdd('d', -1, _NowCalcDate()), "/", "") $ExcelDoc1 = "C:\Documents and Settings\me\Desktop\Counts.xls" $oExcel1 = _ExcelBookOpen($ExcelDoc1, 1, True) _ExcelSheetActivate($oExcel1, "DAILY COUNTS") $NowCell1 = _ExcelReadArray($oExcel1, 1, 2, 255, 0, 1) ;var2=start down 1, var3=start right 2, var4=end at 255 (MAX = 256 which is the Max # of cells excel can have if starting from cell 1) ;var5=default is 0 which is right (1=down), var6=sets index base of 0 or 1 of the array created ;starting index base at 1 makes $array[0] have the # of array objects $N1 = 1 $Error = False For $C1 = 0 To $NowCell1[0];Search all cells in the row If $NowCell1[$N1] = $DATE & "000000" Then;For some reason excell puts 6 0's at the end of the date ExitLoop Else $N1 = $N1 + 1 EndIf Next Do $DN = _ExcelReadCell($oExcel1, 14, ($N1 + 1)) If $DN = 0 Then _ExcelBookClose($oExcel1) Sleep(10000) $ExcelDoc1 = "C:\Documents and Settings\me\Desktop\Counts.xls" $oExcel1 = _ExcelBookOpen($ExcelDoc1, 1, True) _ExcelSheetActivate($oExcel1, "DAILY COUNTS") $DN = _ExcelReadCell($oExcel1, 14, ($N1 + 1)) ElseIf $DN < 80000 Or $DN > 200000 Then $DN = "ERROR: " & $DN $Error = True ExitLoop EndIf Until Not $DN = 0 MsgBox(0, "a", $DN) Do $DS = _ExcelReadCell($oExcel1, 15, ($N1 + 1)) If $DS = 0 Then _ExcelBookClose($oExcel1) Sleep(300000) $ExcelDoc1 = "C:\Documents and Settings\me\Desktop\Counts.xls" $oExcel1 = _ExcelBookOpen($ExcelDoc1, 1, True) _ExcelSheetActivate($oExcel1, "DAILY COUNTS") $DN = _ExcelReadCell($oExcel1, 14, ($N1 + 1)) ElseIf $DS < 8000 Or $DS > 20000 Then $DS = "ERROR: " & $DS $Error = True ExitLoop EndIf Until Not $DS = 0 MsgBox(0, "a", $Ds) $TN = _ExcelReadCell($oExcel1, 18) If $TN < 80000 Then $TN = "ERROR: " & $TN $Error = True EndIf $TS = _ExcelReadCell($oExcel1, 21) If $TS < 8000 Then $TS = "ERROR: " & $TS $Error = True EndIf _ExcelBookClose($oExcel1) Sleep(500)The problem is at $DN you can see if the number isn't updated then it will close the document, wait, then reopen thus making a new instance of it that $DS wont be able to use. I know there has got to be a better way to do this any help would be appreciated, thanks. You don't explain why $DS can't use the new instance of $oExcel and I don't see the problem. If you must monitor data while it is being updated that frequently by other processes a database service makes more sense than a spreadsheet file. But you may be able to treat the spreadsheet as though it was a database by accessing it with an ADO connection instead of Excel APIs. Search for posts by ptrex about connecting to .xls files with ADO. 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 Share this post Link to post Share on other sites
Baraoic 0 Report post Posted September 22, 2008 Ok sorry I found out what I was doing, but thanks for the info PsaltyDS that is better using SQL. Share this post Link to post Share on other sites