Sign in to follow this  
Followers 0
Baraoic

Reading excel doc

3 posts in this topic

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

#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



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

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

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