Really rusty user needs help

[rharwood2]

According to the date in my file i created a auto it  program 11 years ago and it was working fine for the longest time and because of job changes i did not touch auto it  again till now. Tried to run my old program and it of course failed,  doing some research, I found the call to excel had changed(changes that broke auto it). My program took input from a serial stream , and parsed it  to obtain a numeric address,  at this point it would open a excel file and if it found a matching  address in  the excel file it would make it in the excel file as found.  The program used _ExcelReadCell and _Excel_Write_Cell  to manipulate the excel file . I have found that these have been replaced  with Excel_read_range and Excel_write_range but for the live of me i can not figure out how to duplicate  the funcunality  using these , like i said it been 11 years since i worked on this . Any help would be greatly appreciated. Be warned I write very ugly code.

virtualprintv2.au3

[TheXman]

By "help", what exactly are you asking for?  You only have 3 errors and 1 warning in the script.  2 of the errors are Excel UDF functions that have been replaced and the other error is a simple syntax issue.  The warning is about Dim being deprecated.  If you know the current replacement functions and have looked at their examples in the help file, what specifically do you need help with? 

 

>Running AU3Check (3.3.14.5)  params:-w 3 -w 4 -w 6  from:C:\Portable Apps\AutoIt3  input:C:\Temp\virtualprintv2.au3
"C:\Temp\virtualprintv2.au3"(36,61) : warning: 'Dim' deprecated as declaration. Prefer to use Local or Global.
Dim $FlowType[3] = ["XOnXoff", "Hardware (RTS, CTS)", "NONE"]
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^
"C:\Temp\virtualprintv2.au3"(160,72) : error: missing separator character before keyword.
            Case StringInStr($instr, 'TRBL') or StringInStr($instr, 'GND FAULT')or
            ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^
"C:\Temp\virtualprintv2.au3"(141,53) : error: _ExcelReadCell(): undefined function.
                        $sCellValue = _ExcelReadCell($oExcelDoc, $i, 4)
                        ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^
"C:\Temp\virtualprintv2.au3"(147,47) : error: _ExcelWriteCell(): undefined function.
                            _ExcelWriteCell($oExcelDoc, "X", $i, 11)
                            ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^
C:\Temp\virtualprintv2.au3 - 3 error(s), 1 warning(s)
!>14:27:10 AU3Check ended. Press F4 to jump to next error.rc:2

 

Edited February 23, 2021 by TheXman

[rharwood2]

Are the functions straight replacements? I am also getting a error on commMG.dll  Attached file says it compiles ok but does not parse correctly.  does the debugger still function correctly? Its a steep learning curve after 11 years.

virtualprint_mircom.au3

[TheXman]

1 hour ago, rharwood2 said:

Are the functions straight replacements?

I seriously doubt that a function that read a cell and a function that has the ability to read a range (cell or a range of cells) is a "direct" parameter-for-parameter replacement.  The same would be true for an old function that wrote a cell.  To find out the differences, you will need to find definitions of the old functions and compare them to the current functions.  You can probably get an old version with that help file from here:  https://www.autoitscript.com/autoit3/files/archive/autoit/.  You also may be able to download and install that old version and get your script running again as-is.

1 hour ago, rharwood2 said:

I am also getting a error on commMG.dll

What, am I supposed to guess the error that you're getting?

1 hour ago, rharwood2 said:

does the debugger still function correctly?

Which debugger?  There are several debuggers available.

 

Hopefully you are starting to understand that to get better answers, you need to ask better questions. 

Edited February 23, 2021 by TheXman

[rharwood2]

Guess it is basically the excel portion of the script i am going to need help with, the old script looked at one colume and figured out a count, (basically number of records. Then it would  receive a address from the serial stream and  look it up in the excel file. When it found the entry it woulds put a "X" in a seperate colume(completed colume) and return to the script to wait for  another address to find.  Based on what I currently have in the attached code above am i going to have to do a complete rewrite or can the current script  be salvaged. 

desc                                             type          address            completed

BLOCK 1 CELL 1- SMOKE        SIGA-PS    1001                     x                           
BLOCK 1 CELL 2- SMOKE        SIGA-PS    1002                     x                           
BLOCK 1 CELL 3- SMOKE        SIGA-PS    1003                                                
This is a sample of tye excel file  it is looking at 

[TheXman]

3 minutes ago, rharwood2 said:

Based on what I currently have in the attached code above am i going to have to do a complete rewrite or can the current script  be salvaged.

Did you try what I suggested in my last post regarding trying to find and install an older version of AutoIt that has the old version of the Excel UDF?  At the least, you could just try to copy & use just the older version of the Excel UDF if you don't want to install a full old version of AutoIt.  That would allow you to use your original script as-is.

[rharwood2]

Ill try and install a older version on a different machine, will i need to find a 32 bit machine i guess? Current is new machine , one of the reasons i up graded  the autoit.

 

[rharwood2]

any suggestion on a version i have tried 3.0, 3.1 currently 3.2 none can open the file< update got file to edit but can not Excel.au3 when did it get added or is it a seperate install

 

 

Edited February 24, 2021 by rharwood2

[rharwood2]

update again now up to 3.3 to get excel.au3 and fixed the commMG error now just GFui_Richedit, any idea when it got addeded ??

 

[water]

To make an old script work with the old Excel UDF and without the need to worry about the AutoIt version you use I suggest the following approach:

• Download autoit-v3.3.10.1 ZIP-file from the archive
• Open the ZIP-file and extract the Excel.au3 file from directory \install\Include
• Add the Excel.au3 to your user include directory and rename it to Excel_Old.au3 (or whatever name you prefer)
• Now you can include this old UDF to your script and it should work with any version of AutoIt

For your information:

• Have a look at the changelog: In version v3.3.12.0 the old Excel UDF has been replaced.
• Have a look at the wiki. There you can see what has changed when the UDF was rewritten.

Edited February 24, 2021 by water

[rharwood2]

Thank you so I take it that version was the last one with the older excel.udf . I have reinstalled a older version of autoit to try and recompile the script but am stuck on Gui_RichEdit do you know what version of autoit that was introduced? I will be doing what you suggested to my new install

Edited February 25, 2021 by rharwood2

[rharwood2]

Thanks again water  worked like a charm

[water]

Please have a look at the full changelog. There you should find the needed information.

https://www.autoitscript.com/autoit3/docs/autoit_changelog_complete.txt

[junkew]

I would stay on latest autoit version no reason to believe you cannot fix it if your com udf is still working. 

The $sCellValue = _ExcelReadCell($oExcelDoc, $i, 4) you should be able to rewrite with RxCx notation like

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

_Excel_RangeRead($oWorkbook, Default, "A1")
_Excel_RangeRead($oWorkbook, Default, "R1C1")

For _ExcelWriteCell($oExcelDoc, "X", $i, 11)

_Excel_RangeWrite($oExcelDoc, Default, "X", "R" & cstr($i) & "C11")

https://www.autoitscript.com/autoit3/docs/libfunctions/Excel Management.htm

And you could make a small function with the old name and calling the new syntax

func _ExcelReadCell($oExcelDoc, $row, $col) 
    return _Excel_RangeRead($oExcelDoc, Default, "R" & cstr($row) & "C" & cstr($col))
endfunc

 

[water]

It's not that easy to translate a script.
The old functions connect to Excel on every call. The new UDF does it once by calling _Excel_Open.

I suggest to either use the old or the new UDF. Mixing them by writing wrappers takes time and is hard to debug.

[junkew]

Just helping out to get rust away from OP as the excel part seems straigh forward reading and setting some values in a file

• Use latest AutoIt and latest Excel UDF

Old way

; open the spreadsheet for viewing
        $oExcelDoc = ObjGet($filename) ; Get an Excel Object from an existing filename

New way

; Create application object or connect to an already running Excel instance
Local $oExcel = _Excel_Open()
Local $oExcelDoc = _Excel_BookOpen($oExcel, $filename)

Old way

; NOTE: $oExcelDoc is a "Workbook Object", not Excel itself!
            $oExcelDoc.Windows(1).Visible = 1; Set the first worksheet in the workbook visible
            $oExcelDoc.Application.Visible = 1;
            $oDocument = $oExcelDoc.Worksheets(1) ; We use the 'Default' worksheet

New way (this part will stay the same)

; NOTE: $oExcelDoc is a "Workbook Object", not Excel itself!
            $oExcelDoc.Windows(1).Visible = 1; Set the first worksheet in the workbook visible
            $oExcelDoc.Application.Visible = 1;
            $oDocument = $oExcelDoc.Worksheets(1) ; We use the 'Default' worksheet

Old way

;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

New way 

As was given above and I agree with @water keeping compatibility functions around is techical debt you should carefully think about but staying at older AutoIt version would be more painfull technical debt I feel.

[rharwood2]

Now i am totaly lost got this to work sorta

        For $i = 1 To $counter - 1 ;Loop
                    
                        $sCellValue = _ExcelReadCell($oExcelDoc, $i, 4)
                        MsgBox(262144, 'Debug line ~' & @ScriptLineNumber, 'Selection:' & @CRLF & '                        $sCellValue' & @CRLF & @CRLF & 'Return:' & @CRLF &                         $sCellValue) ;### Debug MSGBOX
                        $sCell2value = _ExcelReadCell($oExcelDoc, $i, 11) 

MsgBox(262144, 'Debug line ~' & @ScriptLineNumber, 'Selection:' & @CRLF & '                        $sCell2value' & @CRLF & @CRLF & 'Return:' & @CRLF &                         $sCell2value) ;### Debug MSGBOX

                        If $sCellValue == $address And $sCell2value  == "" Then
                            MsgBox(4, 'We are here', 'Passed the if statement')
                            _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

 

However the  "$sCell2Value" never seems to populate(shows nothing in debug message)  and thus the, IF Then Statement never gets executed so the call to the write never happens.

Think this may have something to do with the way i am opening the spread sheet as i am also getting a message that the file is already open and i must open read only.Am i on the right track here

[rharwood2]

think i found reason for read only  i wrote a function to count the entries in the file  but i never close it:

    ShellExecute($filename)
        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.Visible = 1
        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
            ;may change if we go to another spreadsheet
            $counter = 1
            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
                MsgBox(4096, "", $counter)
                ;ConsoleWrite($counter)
            WEnd

Prob ably should close file here until it is needed  again Correct????

Thanks to all that are helping here, its been a huge learning curve trying to relearn all this after all this time.

Edited March 1, 2021 by rharwood2
addtional info

[junkew]

Hard to say. Seems you switched to earlier version of AutoIt. My advice is to rewrite with latest AutoIt and Excel UDF.

Your algorithm is a little hard to follow

1. You have a form and when you turn checkbox on you call autoupdate and open excel but not close it (which could be fine)
2. If there is something from the serial port
   1. If the autoupdate checkboX is checked
      1. Find the excel row
      2. update it with a certain value beeing read

Maybe start with a function that only sets your value so you can easier test it like below and remove your loop construct you have 3 times

If that works you only have to potentially open.close excel workbook if you do not want it globally opened

func setExcelValue($address,  $checkColumn, $updateColumn)
   For $i = 1 To $counter - 1 ;Loop
      ;ConsoleWrite($i )
      $sCellValue = _ExcelReadCell($oExcelDoc, $i, 4)
      ;ConsoleWrite($scellvalue)
      $sCell2value = _ExcelReadCell($oExcelDoc, $i, $checkColumn)
      If $sCellValue == $address And $sCell2value == "" Then
          _ExcelWriteCell($oExcelDoc, "X", $i, $updateColumn)
          $oExcelDoc.ActiveSheet.Range("K" & $i).Select
          ;_ExcelWriteCell($oExcelDoc, "P",  $i, $updateColumn)
          $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
endfunc

setExcelValue($address, 11, 11)
setExcelValue($address, 14, 14)
setExcelValue($address, 5, 11)

 

 

[rharwood2]

i will explain basic idea behind this, it was orginally written to be a vitual printer for a panel that was outputing serial data to a printer, hense the name virtualprint.  Addtional features were added, ie sounds, relay controls , etc the excel portion was developed to take the real time data from the panel and update an Excel report  as devices were tripped during an inspection.

Basic logic for this was at the time was to first get a count of the number of entrys in the excel file which i did with a while loop , counting the non blank entrys in the address field of the spread sheet.(the function that i think can now be done with range read?)  Then as data from the serial stream came in it would capture the muti part address from the stream and if it matched one of the existing addresses (the Excel read)

in the Excel file it would mark it as checked off (the excel write).  I warned right off the bat i write terrible code and yes  there was probably better ways to do it the when i wrote this orginally, i did not have the benifit of forums like this for help and mentorship.

Edited March 1, 2021 by rharwood2
spelling correction