Reading excel doc

[Baraoic]

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.

[PsaltyDS]

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.

[Baraoic]

Ok sorry I found out what I was doing, but thanks for the info PsaltyDS that is better using SQL.