script stopped working???

rharwood · April 13, 2010

Can anyone see what is going on. this code was working fine how ever now the only way, to get the excel doc to open, is to start excel first with no workbooks open, then i am able to open my spread sheet from within the program.

if any thing is open in excel i get an error on $oDocument = $oExcelDoc.Worksheets(1)

Func AutoUpdate();*******************This is for the auto updating of an excel file from the data coming in on the serial port
    If GUICtrlRead($Checkbox4) = $GUI_UNCHECKED Then
        $oExcelDoc.saved = 1
        $oExcelDoc.close
        $oExcelDoc = 0
    EndIf
    If GUICtrlRead($Checkbox4) = $GUI_CHECKED Then; if we turn on the autoupdate function
        $filename = FileOpenDialog("File picker", "c:\fireutils", "All (*.csv;*.xls)|", 1 + 4); pick the spreadsheet to use
        If @error Then
            MsgBox(4096, "", "No File(s) chosen")
        Else
            $filename = StringReplace($filename, "|", @CRLF)
            ;MsgBox(4096,"","You chose " & $filename)
            ;$excellocation,
        EndIf
        ShellExecute($excellocation)
        If Not FileExists($filename) Then ; Just a check to be sure..
            MsgBox(0, "Excel Data Test", "Error: Can't find file " & $filename)
            Exit
        EndIf
        ; open the spreadsheet for viewing
        $oExcelDoc = ObjGet($filename) ; Get an Excel Object from an existing filename

        If (Not @error) And IsObj($oExcelDoc) Then ; Check again if everything went well
            ; NOTE: $oExcelDoc is a "Workbook Object", not Excel itself!
            $oDocument = $oExcelDoc.Worksheets(1) ; We use the 'Default' worksheet
            ;$oExcelDoc.Visible = 1
            ;may change if we go to another spreadsheet
            $counter = 1
            ;ConsoleWrite($counter & @CRLF)
            while (_ExcelReadCell($oExcelDoc, $counter, 4) <> ""); get the number of devices in the sheet so that we
                $counter = $counter + 1 ;know what the top end is when we parse
                ;ConsoleWrite($counter & @CRLF )
            WEnd
        Else
            MsgBox(0, "Excel Data Test", "Error: Could not open " & $filename & " as an Excel Object.")
        EndIf
    EndIf
EndFunc   ;==>AutoUpdate

bo8ster · April 13, 2010

Have you looked at _ExcelBookOpen()?

Can you please provide a there rest of the code. I could not get the given code to compile.

rharwood · April 14, 2010

Have you looked at _ExcelBookOpen()?

Can you please provide a there rest of the code. I could not get the given code to compile.

ok but it is fairly long

#NoTrayIcon
#Region ;**** Directives created by AutoIt3Wrapper_GUI ****
#AutoIt3Wrapper_Outfile=V2virtualprint.exe
#AutoIt3Wrapper_Res_Comment=CHANGES PARSING TO REFLECT SUPER STUFF
#AutoIt3Wrapper_Res_Description=Virtual printer and parser
#AutoIt3Wrapper_Res_Fileversion=1.0.0.16
#AutoIt3Wrapper_Res_FileVersion_AutoIncrement=p
#AutoIt3Wrapper_Res_LegalCopyright=Robert Harwood 2010
#EndRegion ;**** Directives created by AutoIt3Wrapper_GUI ****
#include <Excel.au3>
#include <GUIConstants.au3>
#include 'CommMG.au3';or if you save the commMg.dll in the @scripdir use ;#include @ScriptDir & '\commg.dll'
#include <GuiEdit.au3>
#include <GuiComboBox.au3>
#include <windowsconstants.au3>
#include <buttonconstants.au3>
#include <GuiRichEdit.au3>
Opt("WINTITLEMATCHMODE", 3)
OnAutoItExitRegister("alldone")
HotKeySet("{ESC}", "alldone")
Global $oExcelDoc = '' ;use for excel funtion and parsing routine
Global $hnd = '';used for logging file
Global $counter = '';used in excel function and parsing routine
Global $address = '';used in excel function and parsing routine
Global $excellocation = '';used to store location of excel executable
Global $filename = '' ;used for file picker
Global $Input1 = ''
Global $BtnSend = ''
Global $speak = '1'
Global $TYPEMESSAGE = ''
Global $Btnpsilence
$strcounter = '1'
$result = '';used for any returned error message setting port
Const $settitle = "VIRTUAL PRINT- set Port", $maintitle = "Virtual Print"
$setflow = 2;default to no flow control
Dim $FlowType[3] = ["XOnXoff", "Hardware (RTS, CTS)", "NONE"]
#Region main program
#Region ### START Koda GUI section ### Form=d:\my documents\miscdelphi\commg\ExampleComm.kxf
$Form2 = GUICreate("Virtual Print", 473, 370, 339, 333, BitOR($WS_MAXIMIZEBOX, $WS_MINIMIZEBOX, $WS_SIZEBOX, $WS_THICKFRAME, $WS_SYSMENU, $WS_CAPTION, $WS_OVERLAPPEDWINDOW, $WS_TILEDWINDOW, $WS_POPUP, $WS_POPUPWINDOW, $WS_GROUP, $WS_TABSTOP, $WS_BORDER, $WS_CLIPSIBLINGS))
$helpmenu = GUICtrlCreateMenu("Help")
$aboutitem = GUICtrlCreateMenuItem("About", $helpmenu)
$Edit1 = GUICtrlCreateEdit("", 10, 25, 449, 223, BitOR($ES_AUTOVSCROLL, $ES_AUTOHSCROLL, $ES_READONLY, $ES_WANTRETURN, $WS_HSCROLL, $WS_VSCROLL))
$BtnSend = GUICtrlCreateButton("Send", 380, 273, 53, 30, $BS_FLAT)
$Input1 = GUICtrlCreateInput("", 18, 279, 361, 21)
$Checkbox1 = GUICtrlCreateCheckbox("Add LF to incoming CR", 340, 4, 150, 17)
$Checkbox2 = GUICtrlCreateCheckbox("Log to file", 105, 4, 100, 17)
$Checkbox3 = GUICtrlCreateCheckbox("Enable Sounds", 205, 4, 100, 17) ; enables alram sounds
$Checkbox4 = GUICtrlCreateCheckbox("Auto update", 18, 260, 100, 17) ; Enables updating of spreadsheet
$Checkbox5 = GUICtrlCreateCheckbox("Auto Silence", 150, 260, 100, 17) ;enables panel silencing
$Checkbox6 = GUICtrlCreateCheckbox("Speech On", 250, 260, 100, 17) ;turns on voice recon
GUICtrlSetState($Checkbox1, $GUI_CHECKED)
GUICtrlSetState($Checkbox2, $GUI_UNCHECKED)
GUICtrlSetState($Checkbox3, $GUI_UNCHECKED)
GUICtrlSetResizing(-1, $GUI_DOCKAUTO)
$BtnSetPort = GUICtrlCreateButton("Set Port", 16, 312, 73, 30, $BS_FLAT)
$BtnSilence = GUICtrlCreateButton("Silence Sounds", 90, 312, 73, 30, $BS_FLAT)
$BtnReset = GUICtrlCreateButton("Reset Panel", 165, 312, 73, 30, $BS_FLAT)
$Btnpsilence = GUICtrlCreateButton("Panel Silence", 240, 312, 73, 30, $BS_FLAT)
$Label21 = GUICtrlCreateLabel("Received text", 34, 6, 70, 17)
$Label31 = GUICtrlCreateLabel("commg.dll version unknown", 340, 328, 135, 17)
GUICtrlSetColor(-1, 0x008080)
GUISetState(@SW_SHOW)
#EndRegion ### END Koda GUI section ###
WinSetTitle($Form2, "", $maintitle)
; get excel path and write to ini file
If FileExists("C:\fireutils\settings.ini") Then
    $hinin = FileOpen("C:\fireutils\settings.ini", 0)
    $excellocation = FileRead($hinin)
    FileClose($hinin)
Else
    SplashTextOn("Notice", "Please wait while we find excel before proceeding, this message should only appear the first time you use this program", -1, -1, -1, -1, 4, "", 24)
    Sleep(100000)
    SplashOff()
    $excellocation = _Find("excel.exe")
    $hiniout = FileOpen("c:\fireutils\settings.ini", 1)
    FileWrite($hiniout, $excellocation)
    FileFlush($hiniout)
    FileClose($hiniout)
EndIf
; if ini file exist get location from there else run find then write ini file for future reading that way we only need to find once
While SetPort(0) = -1
    If MsgBox(4, 'Port not set', 'Do you want to quite the program?') = 6 Then Exit
WEnd
;GUISwitch($Form2)
GUICtrlSetData($Label31, 'using ' & _CommGetVersion(1))
Events()
GUICtrlSetBkColor($Edit1, 0x000000) ;black
GUICtrlSetColor($Edit1, 0xffffff) ; white
GUICtrlSetState($Edit1, $GUI_FOCUS)
While 1
    ;get characters returning when @CR received (@CR is included in the return!)
    $instr = _CommGetLine();_CommGetLine([$endChar=@CR,$maxchar = 0, $maxtime = 0)
    If $instr <> '' Then ;if we got something from serial port
        ;ConsoleWrite($speak)
        If GUICtrlRead($Checkbox6) = $GUI_CHECKED And $speak == '2' Then
            If StringLen($instr) <= '40' Then
                DllCall("super4.dll", "int", "SetRelays", "str", "virprint", "byte", 4, "byte", 1)
                ShellExecuteWait("c:\fireutils\espeak.exe", "-v en-us " & '"' & $TYPEMESSAGE & $instr & '"', "", "", @SW_HIDE)
                DllCall("super4.dll", "int", "SetRelays", "str", "virprint", "byte", 4, "byte", 0)
            EndIf
        EndIf
        If GUICtrlRead($Checkbox1) = $GUI_CHECKED Then $instr = StringReplace($instr, @CR, @CRLF); see if we want crlf
        ;*****************This section does the stream parsing for colour codes based on data returned and if logging
        ;****************is required log the data to a file
        ;***************************************************************************************************************
        ;*******************************************    ALARM    *******************************************************
        ;***************************************************************************************************************
        Select
            Case StringInStr($instr, 'HEAT ALARM ACT') Or StringInStr($instr, 'PULL STATION ACT') Or StringInStr($instr, 'STAGE TWO ACTIVE') Or StringInStr($instr, 'ALARM ACTIVE') Or StringInStr($instr, 'WATERFLOW ACTIVE');EST3 SYNTAX
                ; process alarm condition
                GUICtrlSetColor($Edit1, 0xff0000) ; Red
                If GUICtrlRead($Checkbox5) = $GUI_CHECKED Then
                    DllCall("super4.dll", "int", "SetRelays", "str", "virprint", "byte", 1, "byte", 1)
                    Sleep(1000)
                    DllCall("super4.dll", "int", "SetRelays", "str", "virprint", "byte", 1, "byte", 0)
                EndIf
                $speak = '2'
                $TYPEMESSAGE = "ALARM ACTIVE "
                If GUICtrlRead($Checkbox3) = $GUI_CHECKED Then ;if sound is enabled
                    SoundPlay("c:\fireutils\sounds\Whopfast.wav") ; Alert user to alarm condtion can be silenced by button
                EndIf
                If GUICtrlRead($Checkbox2) = $GUI_CHECKED Then ;if file logging on
                    FileWrite($hnd, $instr)
                    FileFlush($hnd)
                EndIf
                GUICtrlSetData($Edit1, $instr, 1) ;send data to screen
                If GUICtrlRead($Checkbox4) = $GUI_CHECKED Then ;If the autoupdate checkboX is checked
                    $Cardstart = StringInStr($instr, "C:") + 2
                    $CARDADD = StringMid($instr, $Cardstart, 2)
                    $panelstart = StringInStr($instr, "P:") + 2
                    $PANELADD = StringMid($instr, $panelstart, 2)
                    If $PANELADD < "10" Then
                        $panelstart = $panelstart + 1
                        $PANELADD = StringMid($instr, $panelstart, 1)
                    EndIf
                    $devicestart = StringInStr($instr, "D:") + 2
                    $DEVICEADD = StringMid($instr, $devicestart, 4)
                    $address = $PANELADD & $CARDADD & $DEVICEADD
                    For $i = 1 To $counter - 1 ;Loop
                        ConsoleWrite($i & @CRLF)
                        $sCellValue = _ExcelReadCell($oExcelDoc, $i, 4)
                        ;ConsoleWrite($scellvalue & @CRLF)
                        ;ConsoleWrite($address & @CRLF)

                        $sCell2value = _ExcelReadCell($oExcelDoc, $i, 11)
                        If $sCellValue == $address And $sCell2value == "" Then
                            _ExcelWriteCell($oExcelDoc, "X", $i, 11)
                            $oExcelDoc.ActiveSheet.Range("K" & $i).Select
                            ;_ExcelWriteCell($oExcelDoc, "P",  $i, 11)
                            $oExcelDoc.Windows(1).Visible = True
                            $oExcelDoc.saved = 1 ; Prevent questions from excel to save the file
                            ;$oExcelDoc.close
                            ;MsgBox(0, "", "update complete", 2)
                        EndIf
                    Next
                EndIf
                ;***************************************************************************************************************
                ;******************************************* TROUBLE ***********************************************************
                ;***************************************************************************************************************
            Case StringInStr($instr, 'TRBL') or StringInStr($instr, 'GND FAULT')or StringInStr($instr, 'LOCAL MNTR');Watch for this string in serial data
                ;; process Trouble condition
                GUICtrlSetColor($Edit1, 0xffff00) ; yellow
                If GUICtrlRead($Checkbox5) = $GUI_CHECKED Then
                    DllCall("super4.dll", "int", "SetRelays", "str", "virprint", "byte", 1, "byte", 1)
                    Sleep(1000)
                    DllCall("super4.dll", "int", "SetRelays", "str", "virprint", "byte", 1, "byte", 0)
                EndIf
                $speak = '2'
                $TYPEMESSAGE = "TROUBLE ACTIVE "
                If GUICtrlRead($Checkbox2) = $GUI_CHECKED Then ;if file logging on
                    FileWrite($hnd, $instr)
                    FileFlush($hnd)
                EndIf
                If GUICtrlRead($Checkbox3) = $GUI_CHECKED Then ;if sound is enabled
                    Beep(500, 100)
                EndIf
                GUICtrlSetData($Edit1, $instr, 1) ;send data to screen
                If GUICtrlRead($Checkbox4) = $GUI_CHECKED Then ;If the autoupdate checkboX is checked
                    $Cardstart = StringInStr($instr, "C:") + 2
                    $CARDADD = StringMid($instr, $Cardstart, 2)
                    $panelstart = StringInStr($instr, "P:") + 2
                    $PANELADD = StringMid($instr, $panelstart, 2)
                    If $PANELADD < "10" Then
                        $panelstart = $panelstart + 1
                        $PANELADD = StringMid($instr, $panelstart, 1)
                    EndIf
                    $devicestart = StringInStr($instr, "D:") + 2
                    $DEVICEADD = StringMid($instr, $devicestart, 4)
                    $address = $PANELADD & $CARDADD & $DEVICEADD
                    ;ConsoleWrite($Cardstart & @CRLF)
                    ;C;onsoleWrite($panelstart & @CRLF)
                    ;ConsoleWrite($address & @CRLF)
                    For $i = 1 To $counter - 1 ;Loop
                        ;ConsoleWrite($i )
                        $sCellValue = _ExcelReadCell($oExcelDoc, $i, 4)
                        ;ConsoleWrite($scellvalue)
                        $sCell2value = _ExcelReadCell($oExcelDoc, $i, 14)
                        If $sCellValue == $address And $sCell2value == "" Then
                            _ExcelWriteCell($oExcelDoc, "X", $i, 14)
                            $oExcelDoc.ActiveSheet.Range("N" & $i).Select
                            $oExcelDoc.Windows(1).Visible = True
                            $oExcelDoc.saved = 1 ; Prevent questions from excel to save the file
                            ;$oExcelDoc.close
                            ;MsgBox(0, "", "update complete", 2)
                        EndIf
                    Next
                EndIf
                ;***************************************************************************************************************
                ;******************************************* Supervisory *******************************************************
                ;***************************************************************************************************************
            Case StringInStr($instr, 'SUPERVISORY ACT') Or StringInStr($instr, 'GATEVALVE ACT') Or StringInStr($instr, 'SECURITY ACT') Or StringInStr($instr, 'TAMPER ACT');same as above
                ;; process supervisory condition
                GUICtrlSetColor($Edit1, 0xffff00) ; yellow
                If GUICtrlRead($Checkbox5) = $GUI_CHECKED Then
                    DllCall("super4.dll", "int", "SetRelays", "str", "virprint", "byte", 1, "byte", 1)
                    Sleep(1000)
                    DllCall("super4.dll", "int", "SetRelays", "str", "virprint", "byte", 1, "byte", 0)
                EndIf
                $speak = '2'
                $TYPEMESSAGE = "SUPERVISORY ACTIVE "
                If GUICtrlRead($Checkbox2) = $GUI_CHECKED Then
                    FileWrite($hnd, $instr)
                    FileFlush($hnd)
                EndIf
                If GUICtrlRead($Checkbox3) = $GUI_CHECKED Then
                    Beep(500, 100)
                EndIf
                GUICtrlSetData($Edit1, $instr, 1)
                If GUICtrlRead($Checkbox4) = $GUI_CHECKED Then ;If the autoupdate checkboX is checked
                    $Cardstart = StringInStr($instr, "C:") + 2
                    $CARDADD = StringMid($instr, $Cardstart, 2)
                    $panelstart = StringInStr($instr, "P:") + 2
                    $PANELADD = StringMid($instr, $panelstart, 2)
                    If $PANELADD < "10" Then
                        $panelstart = $panelstart + 1
                        $PANELADD = StringMid($instr, $panelstart, 1)
                    EndIf
                    $devicestart = StringInStr($instr, "D:") + 2
                    $DEVICEADD = StringMid($instr, $devicestart, 4)
                    $address = $PANELADD & $CARDADD & $DEVICEADD
                    ;ConsoleWrite($Cardstart & @CRLF)
                    ;ConsoleWrite($panelstart & @CRLF)
                    ;ConsoleWrite($address & @CRLF)
                    For $i = 1 To $counter - 1 ;Loop
                        ;ConsoleWrite($i )
                        $sCellValue = _ExcelReadCell($oExcelDoc, $i, 4)
                        ;ConsoleWrite($scellvalue)
                        $sCell2value = _ExcelReadCell($oExcelDoc, $i, 5)
                        If $sCellValue == $address And $sCell2value == "" Then
                            _ExcelWriteCell($oExcelDoc, "P", $i, 11)
                            $oExcelDoc.ActiveSheet.Range("K" & $i).Select
                            ;_ExcelWriteCell($oExcelDoc, "P",  $i, 11)
                            $oExcelDoc.Windows(1).Visible = True
                            $oExcelDoc.saved = 1 ; Prevent questions from excel to save the file
                            ;$oExcelDoc.close
                            ;MsgBox(0, "", "update complete", 2)
                        EndIf
                    Next
                EndIf
            Case StringInStr($instr, 'RESTORED') Or StringInStr($instr, 'RST')
                ;same as above
                ;; process restored  condition
                GUICtrlSetColor($Edit1, 0x7FFF00) ; Green
                If GUICtrlRead($Checkbox2) = $GUI_CHECKED Then
                    FileWrite($hnd, $instr)
                    FileFlush($hnd)
                EndIf
                If GUICtrlRead($Checkbox3) = $GUI_CHECKED Then
                    Beep(500, 100)
                EndIf
                GUICtrlSetData($Edit1, $instr, 1)
            Case Else
                GUICtrlSetColor($Edit1, 0xffffff) ; set text to white for all normal display
                If GUICtrlRead($Checkbox2) = $GUI_CHECKED Then ;Check for logging
                    FileWrite($hnd, $instr)
                    FileFlush($hnd)
                EndIf
                GUICtrlSetData($Edit1, $instr, 1) ;send data to screen
                $speak = '1'
        EndSelect
    EndIf
WEnd
; ********************************When we are done close things off ***************************************
If GUICtrlRead($Checkbox2) = $GUI_CHECKED Then
    FileClose($hnd)
EndIf
If GUICtrlRead($Checkbox4) = $GUI_CHECKED Then
    $oExcelDoc.saved = 1
    $oExcelDoc.close
    $oExcelDoc = 0
EndIf
AllDone()
Func port11() ; no ideas what this does was part of comm example script
    ;MsgBox(0,'now set to channel',_CommSwitch(2))
    _commSwitch(2)
    $s2 = "1 2 3 4";_CommGetString()
    ConsoleWrite("comm1 gets " & $s2 & @CRLF)
    _CommSendString($s2)
    _CommSwitch(1)
EndFunc   ;==>port11
#EndRegion main program
Func Events()
    Opt("GUIOnEventMode", 1)
    GUISetOnEvent($GUI_EVENT_CLOSE, "justgo")
    GUICtrlSetOnEvent($BtnSend, "SendEvent")
    GUICtrlSetOnEvent($BtnSetPort, "SetPortEvent")
    GUICtrlSetOnEvent($BtnSilence, "Silence")
    GUICtrlSetOnEvent($Checkbox4, "AutoUpdate")
    GUICtrlSetOnEvent($Checkbox2, "Logfile")
    GUICtrlSetOnEvent($aboutitem, "About")
    GUICtrlSetOnEvent($BtnReset, "Reset")
    GUICtrlSetOnEvent($Btnpsilence, "psilence")
EndFunc   ;==>Events
Func SetPortEvent()
    SetPort();needed because a parameter is optional for setport so we can't use "setport" for the event
    GUICtrlSetState($Edit1, $GUI_FOCUS)
EndFunc   ;==>SetPortEvent
Func AutoUpdate();*******************This is for the auto updating of an excel file from the data coming in on the serial port
    If GUICtrlRead($Checkbox4) = $GUI_UNCHECKED Then
        $oExcelDoc.saved = 1
        $oExcelDoc.close
        $oExcelDoc = 0
    EndIf
    If GUICtrlRead($Checkbox4) = $GUI_CHECKED Then; if we turn on the autoupdate function
        $filename = FileOpenDialog("File picker", "c:\fireutils", "All (*.csv;*.xls)|", 1 + 4); pick the spreadsheet to use
        If @error Then
            MsgBox(4096, "", "No File(s) chosen")
        Else
            $filename = StringReplace($filename, "|", @CRLF)
            ;MsgBox(4096,"","You chose " & $filename)
            ;$excellocation,
        EndIf
        ;ShellExecute($excellocation)
        If Not FileExists($filename) Then ; Just a check to be sure..
            MsgBox(0, "Excel Data Test", "Error: Can't find file " & $filename)
            Exit
        EndIf
        ; open the spreadsheet for viewing
        $oExcelDoc = ObjGet($filename) ; Get an Excel Object from an existing filename
            
        If (Not @error) And IsObj($oExcelDoc) Then ; Check again if everything went well
            ; NOTE: $oExcelDoc is a "Workbook Object", not Excel itself!
            $oDocument = $oExcelDoc.Worksheets(1) ; We use the 'Default' worksheet
            ;$oExcelDoc.Visible = 1
            ;may change if we go to another spreadsheet
            $counter = 1
            ;ConsoleWrite($counter & @CRLF)
            while (_ExcelReadCell($oExcelDoc, $counter, 4) <> ""); get the number of devices in the sheet so that we
                $counter = $counter + 1 ;know what the top end is when we parse
                ;ConsoleWrite($counter & @CRLF )
            WEnd
        Else
            MsgBox(0, "Excel Data Test", "Error: Could not open " & $filename & " as an Excel Object.")
        EndIf
    EndIf
EndFunc   ;==>AutoUpdate
Func Logfile();*******************This function turns on and off file logging
    If GUICtrlRead($Checkbox2) = $GUI_UNCHECKED Then
        FileClose($hnd)
    EndIf
    If GUICtrlRead($Checkbox2) = $GUI_CHECKED Then
        $hnd = FileOpen("Virtualprintlog.txt", 1)
    EndIf
EndFunc   ;==>Logfile
Func justgo()
    Exit
EndFunc   ;==>justgo
Func SendEvent() ;send the text in the input and append CR
    _CommSendstring(GUICtrlRead($Input1) & @CR)
    GUICtrlSetData($Input1, '') ;clear the input
    ;GUICtrlSetState($edit1,$GUI_FOCUS);sets the caret back in the terminal screen
EndFunc   ;==>SendEvent
Func Silence() ;stop the sound from alarms etc
    SoundPlay("")
EndFunc   ;==>Silence
Func Reset() ;stop the sound from alarms etc
    $response = MsgBox(1, "Reset", "This button when pressed will send a reset to the EST3 Panel" & @CRLF & "Are you sure you want to reset?????")
    While ($response == 1)
        DllCall("super4.dll", "int", "SetRelays", "str", "virprint", "byte", 2, "byte", 1)
        Sleep(1000)
        DllCall("super4.dll", "int", "SetRelays", "str", "virprint", "byte", 2, "byte", 0)
        $response = 0
    WEnd
EndFunc   ;==>Reset
Func psilence() ;stop the sound from alarms etc
    DllCall("super4.dll", "int", "SetRelays", "str", "virprint", "byte", 1, "byte", 1)
    Sleep(1000)
    DllCall("super4.dll", "int", "SetRelays", "str", "virprint", "byte", 1, "byte", 0)
EndFunc   ;==>psilence
Func About() ;Display version info
    $ver = FileGetVersion("c:\fireutils\virtualprint.exe")
    MsgBox(0, "About", "File version:" & $ver & " Written by Robert Harwood using  AutoIt Gui Creator (C) 2010")
EndFunc   ;==>About
Func AllDone()
    ;MsgBox(0,'will close ports','')
    _Commcloseport()
    ;MsgBox(0,'port closed','')
    Exit
EndFunc   ;==>AllDone
Func _Find($s, $d = @HomeDrive) ;only way I could find to locate the excel. exe file
    If StringRight($d, 1) <> "\" Then $d &= "\"
    Local $h = FileFindFirstFile($d & "*")
    If $h = -1 Then Return 0
    While 1
        $t = FileFindNextFile($h)
        If $t = $s Then Return $d & $t
        $t = $d & $t
        If @error Then Return 0 * FileClose($h)
        If StringInStr(FileGetAttrib($t), "D") Then
            $tmp = _Find($s, $t)
            If $tmp <> "0" Then Return $tmp
            ContinueLoop
        EndIf
    WEnd
    FileClose($h)
    Return 0
EndFunc   ;==>_Find
Func _FindAll($s, $d = @HomeDrive)
    If Not IsDeclared("sRet") Then
        Static $sRet = ""
    EndIf
    If StringRight($d, 1) <> "\" Then $d &= "\"
    Local $h = FileFindFirstFile($d & "*")
    If $h = -1 Then Return ""
    While 1
        $t = FileFindNextFile($h)
        If @error Then ExitLoop
        If $t = $s Then $sRet &= @CRLF & $d & $t
        $t = $d & $t
        If StringInStr(FileGetAttrib($t), "D") Then $tmp = _FindAll($s, $t)
    WEnd
    FileClose($h)
    Return $sRet
EndFunc   ;==>_FindAll
; Function SetPort($mode=1)
; Creates a form for the port settings
;Parameter $mode sets the return value depending on whether the port was set
;Returns  0 if $mode <> 1
;          -1 If` the port not set and $mode is 1
Func SetPort($mode = 1) ;if $mode = 1 then returns -1 if settings not made
    Opt("GUIOnEventMode", 0) ;keep events for $Form2, use GuiGetMsg for $Form3
    #Region ### START Koda GUI section ### Form=d:\my documents\miscdelphi\commg\examplecommsetport.kxf
    $Form3 = GUICreate("virtual Print - set Port", 422, 279, 329, 268, BitOR($WS_MINIMIZEBOX, $WS_CAPTION, $WS_POPUP, $WS_GROUP, $WS_BORDER, $WS_CLIPSIBLINGS, $DS_MODALFRAME), BitOR($WS_EX_TOPMOST, $WS_EX_WINDOWEDGE))
    $Group1 = GUICtrlCreateGroup("Set COM Port", 18, 8, 288, 252)
    $CmboPortsAvailable = GUICtrlCreateCombo("", 127, 28, 145, 25)
    $CmBoBaud = GUICtrlCreateCombo("9600", 127, 66, 145, 25, BitOR($CBS_DROPDOWN, $CBS_AUTOHSCROLL, $CBS_SORT, $WS_VSCROLL))
    GUICtrlSetData(-1, "10400|110|115200|1200|128000|14400|150|15625|1800|19200|2000|2400|256000|28800|3600|38400|4800|50|56000|57600|600|7200|75")
    $CmBoStop = GUICtrlCreateCombo("1", 127, 141, 145, 25)
    GUICtrlSetData(-1, "1|2|1.5")
    $CmBoParity = GUICtrlCreateCombo("none", 127, 178, 145, 25)
    GUICtrlSetData(-1, "odd|even|none")
    $Label2 = GUICtrlCreateLabel("Port", 94, 32, 23, 17)
    $Label3 = GUICtrlCreateLabel("baud", 89, 70, 28, 17)
    $Label4 = GUICtrlCreateLabel("No. Stop bits", 52, 145, 65, 17)
    $Label5 = GUICtrlCreateLabel("parity", 88, 182, 29, 17)
    $CmboDataBits = GUICtrlCreateCombo("8", 127, 103, 145, 25)
    GUICtrlSetData(-1, "7|8")
    $Label7 = GUICtrlCreateLabel("No. of Data Bits", 38, 107, 79, 17)
    $ComboFlow = GUICtrlCreateCombo("NONE", 127, 216, 145, 25)
    GUICtrlSetData(-1, "NONE|XOnXOff|Hardware (RTS, CTS)")
    $Label1 = GUICtrlCreateLabel("flow control", 59, 220, 58, 17)
    GUICtrlCreateGroup("", -99, -99, 1, 1)
    $BtnApply = GUICtrlCreateButton("Apply", 315, 95, 75, 35, $BS_FLAT)
    GUICtrlSetFont(-1, 12, 400, 0, "MS Sans Serif")
    $BtnCancel = GUICtrlCreateButton("Cancel", 316, 147, 76, 35, $BS_FLAT)
    GUICtrlSetFont(-1, 12, 400, 0, "MS Sans Serif")
    GUISetState(@SW_SHOW)
    #EndRegion ### END Koda GUI section ###
    WinSetTitle($Form3, "", $settitle) ;ensure a change to Koda design doesn't stop script working
    $mainxy = WinGetPos($Form2)
    WinMove($Form3, "", $mainxy[0] + 20, $mainxy[1] + 20)
    $portlist = _CommListPorts(0);find the available COM ports and write them into the ports combo
    If @error = 1 Then
        MsgBox(0, 'trouble getting portlist', 'Program will terminate!')
        Exit
    EndIf
    For $pl = 1 To $portlist[0]
        GUICtrlSetData($CmboPortsAvailable, $portlist[$pl]) ;_CommListPorts())
    Next
    GUICtrlSetData($CmboPortsAvailable, $portlist[1]) ;show the first port found
    GUICtrlSetData($ComboFlow, $FlowType[$setflow])
    _GUICtrlComboBox_SetMinVisible($CmBoBaud, 10) ;restrict the length of the drop-down list
    $retval = 0
    While 1
        $msg = GUIGetMsg()
        If $msg = $BtnCancel Then
            If Not $mode Then $retval = -1
            ExitLoop
        EndIf
        If $msg = $BtnApply Then
            Local $sportSetError
            $comboflowsel = GUICtrlRead($ComboFlow)
            For $n = 0 To 2
                If $comboflowsel = $FlowType[$n] Then
                    $setflow = $n
                    ConsoleWrite("flow = " & $setflow & @CRLF)
                    ExitLoop
                EndIf
            Next
            $setport = StringReplace(GUICtrlRead($CmboPortsAvailable), 'COM', '')
            _CommSetPort($setport, $sportSetError, GUICtrlRead($CmBoBaud), GUICtrlRead($CmboDataBits), GUICtrlRead($CmBoParity), GUICtrlRead($CmBoStop), $setflow)
            If $sportSetError = '' Then
                MsgBox(262144, 'Connected ', 'to COM' & $setport)
            Else
                MsgBox(262144, 'Setport error = ', $sportSetError)
            EndIf
            $mode = 1;
            ExitLoop
        EndIf
        ;stop user switching back to $form2
        If WinActive($maintitle) Then
            ConsoleWrite('main is active' & @CRLF)
            If WinActivate($settitle) = 0 Then MsgBox(0, 'not found', $settitle)
        EndIf
    WEnd
    GUIDelete($Form3)
    WinActivate($maintitle)
    Events()
    Return $retval
EndFunc   ;==>SetPort

bo8ster · April 14, 2010

Firstly, thankyou for putting the code in code tags. Really happy about that.

I have reviewed your code and have made some changes.

To directly answer your question, AutoIt may not see Worksheets(1) as a property of $oExcelDoc. Have a look at ExcelSheetActivate and ExcelSheetList examples.

Func AutoUpdate();*******************This is for the auto updating of an excel file from the data coming in on the serial port
    If GUICtrlRead($Checkbox4) = $GUI_UNCHECKED Then
        $oExcelDoc.saved = 1
        $oExcelDoc.close
        $oExcelDoc = 0
    EndIf
    If GUICtrlRead($Checkbox4) = $GUI_CHECKED Then; if we turn on the autoupdate function
        $filename = FileOpenDialog("File picker", "c:\fireutils", "All (*.csv;*.xls)|", 1 + 4); pick the spreadsheet to use
        If @error Then
            MsgBox(4096, "", "No File(s) chosen")
        Else
            $filename = StringReplace($filename, "|", @CRLF)
            ;MsgBox(4096,"","You chose " & $filename)
            ;$excellocation,
        EndIf
        ;ShellExecute($excellocation)
        If Not FileExists($filename) Then ; Just a check to be sure..
            MsgBox(0, "Excel Data Test", "Error: Can't find file " & $filename)
            Exit
        EndIf
        ; open the spreadsheet for viewing
;~         $oExcelDoc = ObjGet($filename) ; Get an Excel Object from an existing filename
        $oExcelDoc = _ExcelBookOpen($filename, 0) ; Opens an existing workbook and returns its object identifier.

        If (Not @error) And IsObj($oExcelDoc) Then ; Check again if everything went well
            ; NOTE: $oExcelDoc is a "Workbook Object", not Excel itself!
            $oDocument = $oExcelDoc.Worksheets(1) ; We use the 'Default' worksheet
            ;$oExcelDoc.Visible = 1
            ;may change if we go to another spreadsheet
            $counter = 1
            ;ConsoleWrite($counter & @CRLF)
            while (_ExcelReadCell($oExcelDoc, $counter, 4) <> 0); get the number of devices in the sheet so that we
                 If @error Then ExitLoop
                $counter = $counter + 1 ;know what the top end is when we parse
                ;ConsoleWrite($counter & @CRLF )
            WEnd
        Else
            MsgBox(0, "Excel Data Test", "Error: Could not open " & $filename & " as an Excel Object.")
        EndIf
    EndIf
EndFunc   ;==>AutoUpdate

If I could offer some advice:

You code is good esp with regard to error protection however it seems to be a script that has grown too large. Simply you do not have enough functions esp with to do with the control of some global vars such as oExcelDoc. The code is hard to follow and via inspection, gives to clear indecation global vars are set corrctly at the time of use.

Example is $oExcelDoc While this has been attempted to be set up in the AutoUpdate() function, it is used in the swtich statement (line 141 after it is sent to ""). There is no way for me to tell via inspection that it is correct at the time of use.

I would recommend better control of your global varabiles where you use functions to control them.

Global $oExcelDoc = ''

Func getExeclDoc()
    If $oExcelDoc <> '' And IsObj($oExcelDoc) = 1 Then ; $oExcelDoc is valid
        Return $oExcelDoc
    Else
        Return _setupoExeclDoc()
    EndIf
EndFunc   ;==>getExeclDoc

Func _setupoExeclDoc()
    Local $filename = FileOpenDialog("File picker", "c:\fireutils", "All (*.csv;*.xls)|", 1 + 4); pick the spreadsheet to use
    If @error Then
        MsgBox(4096, "", "No File(s) chosen")
    Else
        $filename = StringReplace($filename, "|", @CRLF)
        ;MsgBox(4096,"","You chose " & $filename)
        ;$excellocation,
    EndIf
    ;ShellExecute($excellocation)
    If Not FileExists($filename) Then ; Just a check to be sure..
        MsgBox(0, "Excel Data Test", "Error: Can't find file " & $filename)
        SetError(1, 0, 0)
        Return 0; error indication
    EndIf
    $oExcelDoc = _ExcelBookOpen($filename, 0)
    Return $oExcelDoc
EndFunc   ;==>_setupoExeclDoc

Func AutoUpdate();*******************This is for the auto updating of an excel file from the data coming in on the serial port
    If GUICtrlRead($Checkbox4) = $GUI_UNCHECKED Then
        $oExcelDoc.saved = 1
        $oExcelDoc.close
        $oExcelDoc = 0
    EndIf
    If GUICtrlRead($Checkbox4) = $GUI_CHECKED Then; if we turn on the autoupdate function

        If (Not @error) And IsObj(getExeclDoc()) Then ; Check again if everything went well
            ; NOTE: $oExcelDoc is a "Workbook Object", not Excel itself!
            $oDocument = getExeclDoc().Worksheets(1) ; We use the 'Default' worksheet
            ;$oExcelDoc.Visible = 1
            ;may change if we go to another spreadsheet
            $counter = 1
            ;ConsoleWrite($counter & @CRLF)
            ; at this stage you will know if oExcelDoc is valid or not.
            while (_ExcelReadCell(getExeclDoc(), $counter, 4) <> 0); get the number of devices in the sheet so that we
                If @error Then ExitLoop
                $counter = $counter + 1 ;know what the top end is when we parse
                ;ConsoleWrite($counter & @CRLF )
            WEnd
        Else
            MsgBox(0, "Excel Data Test", "Error: Could not open " & $filename & " as an Excel Object.")
        EndIf
    EndIf
EndFunc   ;==>AutoUpdate

Sign In

script stopped working???

Recommended Posts

rharwood

Link to comment

Share on other sites

bo8ster

Link to comment

Share on other sites

rharwood

Link to comment

Share on other sites

bo8ster

Link to comment

Share on other sites

Create an account or sign in to comment

Create an account

Sign in

Recently Browsing 0 members

Browse

AutoIt Resources

Release

Beta