Jump to content

Recommended Posts

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

Link to post
Share on other sites
  • Replies 45
  • Created
  • Last Reply

Top Posters In This Topic

Top Posters In This Topic

Popular Posts

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

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. Mixi

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?  :think:

 

>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 by TheXman
Link to post
Share on other sites
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 by TheXman
Link to post
Share on other sites

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 

Link to post
Share on other sites
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.

Link to post
Share on other sites

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 by rharwood2
Link to post
Share on other sites

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 by water

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2021-06-05 - Version 1.5.4.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2021-06-14 - Version 1.6.5.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki, WebDriver - Wiki

 

Link to post
Share on other sites

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 by rharwood2
Link to post
Share on other sites

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

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2021-06-05 - Version 1.5.4.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2021-06-14 - Version 1.6.5.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki, WebDriver - Wiki

 

Link to post
Share on other sites

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

 

Link to post
Share on other sites

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.

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2021-06-05 - Version 1.5.4.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2021-06-14 - Version 1.6.5.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki, WebDriver - Wiki

 

Link to post
Share on other sites

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.

Link to post
Share on other sites

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

Link to post
Share on other sites
Posted (edited)

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 by rharwood2
addtional info
Link to post
Share on other sites

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)

 

 

Link to post
Share on other sites
Posted (edited)

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 by rharwood2
spelling correction
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
  • Recently Browsing   0 members

    No registered users viewing this page.

×
×
  • Create New...