Really rusty user needs help

water · March 1, 2021

Quick and dirty example:

#include <Excel.au3>

; Modify the following lines cof code if needed
Global $sScriptName = "Test"
Global $sWorkbook = "C:\Local\test\test.xlsx"
Global $sInStream = "1005"
Global $iAddress = 2 ; Index of "address" in $aData (starting with 0)
Global $iCompleted = 3 ; Index of "completed" in $aData (starting with 0)
Global $sCompleted = "D" ; Letter of column "completed" in the workbook
; Do not modify code beyond this line

; Start up Excel or connect to a running instance
Global $oExcel = _Excel_Open()
If @error Then Exit MsgBox($MB_ICONERROR, $sScriptName, "Error opening Excel! @error=" & @error & ", @extended=0x" & Hex(@extended, 8))

; Open the workbook
Global $oWorkbook = _Excel_BookOpen($oExcel, $sWorkbook)
If @error Then Exit MsgBox($MB_ICONERROR, $sScriptName, "Error opening Workbook '" & $sWorkbook & "'! @error=" & @error & ", @extended=0x" & Hex(@extended, 8))

; Read the whole workbook to an array
Global $aData = _Excel_RangeRead($oWorkbook)
If @error Then Exit MsgBox($MB_ICONERROR, $sScriptName, "Error reading data! @error=" & @error & ", @extended=0x" & Hex(@extended, 8))

; While
_ProcessInStream($sInStream)
; WEnd

Func _ProcessInStream($sAddress)
    Local $bFound = False ; Search for the address
    For $i = 1 To UBound($aData) - 1
        If $aData[$i][2] = $sAddress Then ; address found
            $bFound = True
            If $aData[$i][$iCompleted] <> "" Then ; Completed has already been set
                MsgBox($MB_ICONERROR, $sScriptName, "Address '" & $sAddress & "' has already been processed!")
            Else
                $aData[$i][$iCompleted] = "X" ; Set "completed" in the array
                _Excel_RangeWrite($oWorkbook, Default, "X", $sCompleted & ($i + 1)) ; Write the completed flag to the workbook
                If @error Then Exit MsgBox($MB_ICONERROR, $sScriptName, "Error writing data to workbook! @error=" & @error & ", @extended=0x" & Hex(@extended, 8))
            EndIf
            ExitLoop
        EndIf
    Next
    If $bFound = False then MsgBox($MB_ICONERROR, $sScriptName, "Address '" & $sAddress & "' could not be found!")
EndFunc   ;==>_ProcessInStream

You have to decide

how to handle addresses which couldn't be found in the workbook (should they be added at the bottom of the workbook?)
how to handle addresses which have already been completed but get processed again?
When should the workbook be saved? After each modification so in case of a script crash or power outage no data gets lost? At end of script?

rharwood2 · March 1, 2021

On 2/25/2021 at 3:17 AM, junkew said:

lValue = _Excel_RangeRead($oExcelDoc, Default, "R" &| cstr($i)| & "C4"

This fails check as undefined function is this something you ^ "cstr($i) wrote as i can not find anything in the documents.

This code reflect changes you suggested , you will see the old code commented out.

mircomvirtualprinter.au3 testlocationsheets.xls

Edited March 1, 2021 by rharwood2
added code and sample excel file

rharwood2 · March 1, 2021

1 hour ago, water said:

Quick and dirty example:

#include <Excel.au3>

; Modify the following lines cof code if needed
Global $sScriptName = "Test"
Global $sWorkbook = "C:\Local\test\test.xlsx"
Global $sInStream = "1005"
Global $iAddress = 2 ; Index of "address" in $aData (starting with 0)
Global $iCompleted = 3 ; Index of "completed" in $aData (starting with 0)
Global $sCompleted = "D" ; Letter of column "completed" in the workbook
; Do not modify code beyond this line

; Start up Excel or connect to a running instance
Global $oExcel = _Excel_Open()
If @error Then Exit MsgBox($MB_ICONERROR, $sScriptName, "Error opening Excel! @error=" & @error & ", @extended=0x" & Hex(@extended, 8))

; Open the workbook
Global $oWorkbook = _Excel_BookOpen($oExcel, $sWorkbook)
If @error Then Exit MsgBox($MB_ICONERROR, $sScriptName, "Error opening Workbook '" & $sWorkbook & "'! @error=" & @error & ", @extended=0x" & Hex(@extended, 8))

; Read the whole workbook to an array
Global $aData = _Excel_RangeRead($oWorkbook)
If @error Then Exit MsgBox($MB_ICONERROR, $sScriptName, "Error reading data! @error=" & @error & ", @extended=0x" & Hex(@extended, 8))

; While
_ProcessInStream($sInStream)
; WEnd

Func _ProcessInStream($sAddress)
    Local $bFound = False ; Search for the address
    For $i = 1 To UBound($aData) - 1
        If $aData[$i][2] = $sAddress Then ; address found
            $bFound = True
            If $aData[$i][$iCompleted] <> "" Then ; Completed has already been set
                MsgBox($MB_ICONERROR, $sScriptName, "Address '" & $sAddress & "' has already been processed!")
            Else
                $aData[$i][$iCompleted] = "X" ; Set "completed" in the array
                _Excel_RangeWrite($oWorkbook, Default, "X", $sCompleted & ($i + 1)) ; Write the completed flag to the workbook
                If @error Then Exit MsgBox($MB_ICONERROR, $sScriptName, "Error writing data to workbook! @error=" & @error & ", @extended=0x" & Hex(@extended, 8))
            EndIf
            ExitLoop
        EndIf
    Next
    If $bFound = False then MsgBox($MB_ICONERROR, $sScriptName, "Address '" & $sAddress & "' could not be found!")
EndFunc   ;==>_ProcessInStream

You have to decide

how to handle addresses which couldn't be found in the workbook (should they be added at the bottom of the workbook?) (seperate log ???)
how to handle addresses which have already been completed but get processed again? (ignore once in it has been done)
When should the workbook be saved? After each modification so in case of a script crash or power outage no data gets lost? At end of script? (this is good design)

Sorry could not get this to work keep saying address not found even after making changes, prob pebcak error attaching sample excel file

testlocationsheets.xls

junkew · March 1, 2021

Sorry cstr should be string

https://www.autoitscript.com/autoit3/docs/functions/String.htm

water · March 1, 2021

After modifying the variable parts at the top of the script and fixing a bug it works great now.

#include <Excel.au3>

; ----------------------------------------------------------
; Modify the following lines cof code if needed
; ----------------------------------------------------------
Global $sScriptName = "Test"
Global $sWorkbook = "C:\Local\test\testlocationsheets.xls"
Global $sInStream = "1005"
Global $iAddress = 3 ; Index of "address" in $aData (starting with 0)
Global $iCompleted = 4 ; Index of "completed" in $aData (starting with 0)
Global $sCompleted = "E" ; Letter of column "completed" in the workbook
; ----------------------------------------------------------
; Do not modify code beyond this line
; ----------------------------------------------------------

; Start up Excel or connect to a running instance
Global $oExcel = _Excel_Open()
If @error Then Exit MsgBox($MB_ICONERROR, $sScriptName, "Error opening Excel! @error=" & @error & ", @extended=0x" & Hex(@extended, 8))

; Open the workbook
Global $oWorkbook = _Excel_BookOpen($oExcel, $sWorkbook)
If @error Then Exit MsgBox($MB_ICONERROR, $sScriptName, "Error opening Workbook '" & $sWorkbook & "'! @error=" & @error & ", @extended=0x" & Hex(@extended, 8))

; Read the whole workbook to an array
Global $aData = _Excel_RangeRead($oWorkbook)
If @error Then Exit MsgBox($MB_ICONERROR, $sScriptName, "Error reading data! @error=" & @error & ", @extended=0x" & Hex(@extended, 8))

; While
_ProcessInStream($sInStream)
; WEnd

Func _ProcessInStream($sAddress)
    Local $bFound = False ; Search for the address
    For $i = 0 To UBound($aData) - 1
        If $aData[$i][$iAddress] = $sAddress Then ; address found
            $bFound = True
            If $aData[$i][$iCompleted] <> "" Then ; Completed has already been set
                MsgBox($MB_ICONERROR, $sScriptName, "Address '" & $sAddress & "' has already been processed!")
            Else
                $aData[$i][$iCompleted] = "X" ; Set "completed" in the array
                _Excel_RangeWrite($oWorkbook, Default, "X", $sCompleted & ($i + 1)) ; Write the completed flag to the workbook
                If @error Then Exit MsgBox($MB_ICONERROR, $sScriptName, "Error writing data to workbook! @error=" & @error & ", @extended=0x" & Hex(@extended, 8))
            EndIf
            ExitLoop
        EndIf
    Next
    If $bFound = False then MsgBox($MB_ICONERROR, $sScriptName, "Address '" & $sAddress & "' could not be found!")
EndFunc   ;==>_ProcessInStream

rharwood2 · March 1, 2021

i did make those same changes at the top except my path was as follows

; Modify the following lines cof code if needed
Global $sScriptName = "Test"
Global $sWorkbook = "C:\fireutils\testlocationsheets.xls"
Global $sInStream = "1005"
Global $iAddress = 3 ; Index of "address" in $aData (starting with 0)
Global $iCompleted = 4 ; Index of "completed" in $aData (starting with 0)
Global $sCompleted = "E" ; Letter of column "completed" in the workbook
; Do not modify code beyond this line

Works like a charm now to figure out how to incorperate it into the program , i guess with this i dont need my function to count the number of entrys now

water · March 1, 2021

1 minute ago, rharwood2 said:

i guess with this i dont need my function to count the number of entrys now

Correct. _Excel_RangeRead now reads the whole worksheet into an array. So the number of rows in the array is the nubmer of entries to process.
I did another update so you can now specify the line of the first entry in the worksheet (if you need to add a header line in the future).

Will post the new code as soon as you have answered my questions in the first post on this page and I have modified the code.

rharwood2 · March 1, 2021

these ones thought i did some where

how to handle addresses which couldn't be found in the workbook (should they be added at the bottom of the workbook?)(Maybe pipe to log file)
how to handle addresses which have already been completed but get processed again? (Ignore, once processed its done)
When should the workbook be saved? After each modification so in case of a script crash or power outage no data gets lost? At end of script? (This is really good Idea as the serial data can come in fairly quickly and it has crashed in the past, so i guess after each mod)

water · March 1, 2021

Latest version:

#include <Excel.au3>
#include <FileConstants.au3>

; ----------------------------------------------------------
; Modify the following lines cof code if needed
; ----------------------------------------------------------
Global $sScriptName = "Test"
Global $sWorkbook = "C:\Local\test\testlocationsheets.xls"
Global $sInStream = "1005"
Global $iFirstLine = 1 ; Row number of first data record in the workbook (starting with 1)
Global $iAddress = 3 ; Index of column "address" in $aData (starting with 0)
Global $iCompleted = 4 ; Index of column "completed" in $aData (starting with 0)
Global $sCompleted = "E" ; Letter of column "completed" in the workbook
Global $sLogFile = "C:\Local\test\testlocation.txt" ; Path to the log file
; ----------------------------------------------------------
; Do not modify code beyond this line
; ----------------------------------------------------------

; Register a function to be called when autoIt exits. Close log file and Excel workbook
OnAutoItExitRegister("_ExitScript")

; Start up Excel or connect to a running instance
Global $oExcel = _Excel_Open()
If @error Then Exit MsgBox($MB_ICONERROR, $sScriptName, "Error opening Excel! @error=" & @error & ", @extended=0x" & Hex(@extended, 8))

; Open the workbook
Global $oWorkbook = _Excel_BookOpen($oExcel, $sWorkbook)
If @error Then Exit MsgBox($MB_ICONERROR, $sScriptName, "Error opening Workbook '" & $sWorkbook & "'! @error=" & @error & ", @extended=0x" & Hex(@extended, 8))

; Read the whole workbook into an array
Global $aData = _Excel_RangeRead($oWorkbook)
If @error Then Exit MsgBox($MB_ICONERROR, $sScriptName, "Error reading data! @error=" & @error & ", @extended=0x" & Hex(@extended, 8))

; Open the Logfile - create path and file if they do no exist
Global $hLog = FileOpen($sLogFile, BitOR($FO_APPEND, $FO_CREATEPATH))
_WriteLog("I", "Starting Script")

; While
_ProcessInStream($sInStream)
; WEnd
Exit

Func _ProcessInStream($sAddress)
    Local $bFound = False ; Search for the address
    For $i = ($iFirstLine - 1) To UBound($aData) - 1
        If $aData[$i][$iAddress] = $sAddress Then ; address found
            $bFound = True
            If $aData[$i][$iCompleted] <> "" Then ; Completed has already been set
                _WriteLog("W", "Address '" & $sAddress & "' has already been processed!")
            Else
                $aData[$i][$iCompleted] = "X" ; Set "completed" in the array
                _Excel_RangeWrite($oWorkbook, Default, "X", $sCompleted & ($i + 1)) ; Write the completed flag to the workbook
                If @error Then Exit MsgBox($MB_ICONERROR, $sScriptName, "Error writing data to workbook! @error=" & @error & ", @extended=0x" & Hex(@extended, 8))
                _Excel_BookSave($oWorkbook)
                If @error Then Exit MsgBox($MB_ICONERROR, $sScriptName, "Error saving workbook! @error=" & @error & ", @extended=0x" & Hex(@extended, 8))
                _WriteLog("I", "Address '" & $sAddress & "' successfully processed!")
            EndIf
            ExitLoop
        EndIf
    Next
    If $bFound = False Then _WriteLog("E", "Address '" & $sAddress & "' could not be found in the Excel workbook!")
EndFunc   ;==>_ProcessInStream

;--------------------------------------
; Wites a message to the logfile
; Format: YYYY/MM/DD HH:MM:SS Type Message
; Type: I - Information (everything worked as expected)
;       W - Warning (everything worked fine - but I wanted to tell you about this)
;       E - Error (Processing could not be started or completed)
;--------------------------------------
Func _WriteLog($sType, $sMessage)
    FileWrite($hLog, @YEAR & "/" & @MON & "/" & @MDAY & " " & @HOUR & ":" & @MIN & ":" & @SEC & " " & $sType & " " & $sMessage & @CRLF)
EndFunc   ;==>_WriteLog

;--------------------------------------
; Does some cleanup before the scriptt ends
;--------------------------------------
Func _ExitScript()
    ; Save workbook and close Excel
    _Excel_BookClose($oWorkbook, True)
    _Excel_Close($oExcel, True)
    ; Close the log file
    _WriteLog("I", "Exiting Script")
    FileClose($hLog)
EndFunc   ;==>_ExitScript

rharwood2 · March 2, 2021

So close i incorperated your script into the orginal, for one of the cases, but it is causeing numerous errors. Could I trouble you to take a look and see what I did wrong ?

mircomvirtualprinterv6.au3

Edited March 2, 2021 by rharwood2
by request 8^)

Jos · March 2, 2021

4 minutes ago, rharwood2 said:

So close i incorperated your script into the orginal for one of the cases but it is causeing numerous errors could i trouble you to take a look and see what i did wrong

You do know that a keyboard also has these funky characters like ,?. to make a sentence much more readable and actually indicated you are asking something?

rharwood2 · March 2, 2021

Update it compiles now but I am still messing something up. Attaching latest au3

EDIT working now but program closes after every line read in from serial string, also need to change string i grab for address into number other wise it does not match what is in excel file, ie grabbing " 1001" from stream but excel file has "1001" Getting so close

mircomvirtualprinterv6.au3

Edited March 2, 2021 by rharwood2
update

rharwood2 · March 2, 2021

Update it is parsing correctly now just need to figure out the closing down issue

mircomvirtualprinterv6.au3

rharwood2 · March 2, 2021

yes !!!! Success Thanks evryone for all your help. Water in the processing when it does the read, if there are spaces in the address field or blank lines in the spread sheet?? is this affecting the input?? On a larger spread sheet while searching, it is restarting the workbook and opening a second copy.

Update: Just checked file 842 Rows in the file, 10 coloums wide, is this going to be issue?

Edited March 2, 2021 by rharwood2

water · March 2, 2021

Spaces could make a difference as my example does a string compare. If needed StringStripWS should be used to remove any whitespace (spaces etc.).
Empty lines get ignored.

rharwood2 · March 3, 2021

I got the space thing fixed, It how I was stripping them from the stream but I will keep that function in mind. Any idea what would cause the script to open a second copy of the workbook? It seems if the address is any distance away in the excel file, ie couple of 100 rows away it can not find the address ( as per the log file ) and it starts a new instance of the file but if the address is close the find works good.

water · March 3, 2021

Seems the data still has some leading or trailing spaces or other whitespace.
Can not think of any reason why a new workbook should be opened.
My example opens the workbook at the beginning and then goes into a loop. So I think it has to do with your code.

rharwood2 · March 3, 2021

water Just noticed this, is this correct?? Does $sInStream get converted to $Address ?? I never noticed the diff till i typed it out in the question below

_ProcessInStream($sInStream) ; your call

Func _ProcessInStream($sAddress) ; the function

$sInStream = StringStripWS($CARDADD,8)

This the line that is setting the varible in your compare statement. should I be doing the same on the other side of the compare???

If StringStripWS($aData[$i][$iAddress],8)  = $sAddress Then

Something like this ???

Edited March 3, 2021 by rharwood2
changed code insertion to comply

water · March 3, 2021

You pass $sInStream as parameter to function _ProcessInStream. In the scope of the function the parameter is accessed by the name $sAddress.
More details can be found in the help file.

$sAddress is the value you provide. But it doesn't hurt to strip whitespace on both sides.

rharwood2 · March 3, 2021

$sInStream = StringStripWS($CARDADD,8)

So i should be instead of this ^^^^ i should be doing this or am I totally messed up.

$sAddress = StringStripWS($CARDADD,8)

Really rusty user needs help

Recommended Posts

Link to comment

Share on other sites

Top Posters In This Topic

Top Posters In This Topic

Popular Posts

TheXman

water

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Create an account or sign in to comment

Create an account

Sign in

Recently Browsing 0 members