Excel UDF Error

[JohnyX]

Hello, i get this error when running the compiled script or au3 file by double-click, but it works when running from SciTE.

Can anyone help me fix it please?

expandcollapse popup

#AutoIt3Wrapper_UseX64=y
#include <Array.au3>
#include <File.au3>
#include <Excel.au3>
#include <Date.au3>
#include <Timers.au3>

Local $hStarttime = _Timer_Init()
Global $oShape
Local $Dir = "E:\New folder (3)\New folder\"
Local $sSaveLoc = "C:\Users\john\Desktop\Test"

$aArray = _FileListToArrayRec($Dir, "*", $FLTAR_FILES, $FLTAR_RECUR, $FLTAR_SORT, $FLTAR_RELPATH)
;_ArrayDisplay($aArray, "File List")
Local $oExcel = _Excel_Open(True)
$x = 1
For $i = 1 To UBound($aArray, $UBOUND_ROWS) - 1 Step +1
$seconds=Round(_Timer_Diff($hStarttime)/1000,2)
ToolTip("Elapsed Time: "&$seconds&@CRLF& "Progress: "&$x, 0, 0)
Local $sWorkbook = $Dir & $aArray[$i]




Local $sDriveD = "", $sDirD= "", $sFileNameD = "", $sExtensionD = ""
Local $aPathSplitD = _PathSplit($sWorkbook, $sDriveD, $sDirD, $sFileNameD, $sExtensionD)
Local $sFullFileSavePathDirD = $sDriveD & $sDirD
Local $sPathSplitD = StringMid($sFullFileSavePathDirD, 18) 




If StringRight($aArray[$i], 4) = ".xls" Or StringRight($aArray[$i], 5) = ".xlsx" Then
    ;MsgBox(Default, Default, $Dir & $aArray[$i])
    Local $oWorkbook = _Excel_BookOpen($oExcel, $sWorkbook)


    If StringInStr(_Excel_RangeRead($oWorkbook, Default, "A4"), "Identifier") Then




Local $sDrive = "", $sDir = "", $sFileName = "", $sExtension = ""
Local $aPathSplit = _PathSplit($sWorkbook, $sDrive, $sDir, $sFileName, $sExtension)
    $split_result = StringSplit($sFileName, "_")
If @error Then
    FileWriteLine(@DesktopDir&"\logrev.txt", $Dir & $aArray[$i] & "  -  Not an Array.")
ElseIf UBound($split_result) <> 0 Then
    $sprez = $split_result[2]
    $sStringFormat = StringFormat("%02d", StringRight($sprez, 2)+1)
    $split_replace = StringReplace($sFileName, $split_result[2], StringReplace($split_result[2], StringRight($sprez, 2), $sStringFormat))
    $sFullFileSavePath = $sDrive & $sDir & $split_replace & $sExtension
    $sFullFileSavePathDir = $sDrive & $sDir
    $sNewRevName = $split_replace & $sExtension
EndIf




$sLogo = @DesktopDir & "\logo.jpg"

For $oShape In $oWorkbook.ActiveSheet.Shapes
    If $oShape.TopLeftCell.Address = "$A$1" Then
                $iTop = $oShape.Top
                $iLeft = $oShape.Left
                $iWidth = $oShape.Width
                $iHeight = $oShape.Height
                $oShape.Delete
        For $oShape In $oWorkbook.ActiveSheet.Shapes
            $oShape.Delete
        Next
                $oExcel.Worksheets(1).Shapes.AddPicture($sLogo, False, True, $iLeft, $iTop, $iWidth, $iHeight)
        ExitLoop
    EndIf
Next




        Local $aResult1 = _Excel_RangeFind($oWorkbook, "Some Text 1")
        If @error Then Exit MsgBox(0, "Error", "_FileListToArray1 returned @error = " & @error)

        Local $aResult2 = _Excel_RangeFind($oWorkbook, "Some Text 2")
        If @error Then Exit MsgBox(0, "Error", "_FileListToArray2 returned @error = " & @error)

        Local $aResult3 = _Excel_RangeFind($oWorkbook, "Some Text 3")
        If @error Then Exit MsgBox(0, "Error", "_FileListToArray3 returned @error = " & @error)




        If UBound($aResult1) = 0 Then
            FileWriteLine(@ScriptDir & "\log.txt", $Dir & $aArray[$i] & "  -  Some Text 1")
        ElseIf UBound($aResult1) <> 0 Then
            $var1 = $aResult1[0][2]
            If StringIsDigit(StringRight($var1, 2)) = True Then
                $reptext1 = StringReplace($var1, StringRight($var1, 2), StringRight($var1, 2) + 1)
                Local $sResult1 = _Excel_RangeRead($oWorkbook, Default, $reptext1)
                _Excel_RangeWrite($oWorkbook, Default, "1st Signature", $reptext1)
            ElseIf StringIsDigit(StringRight($var1, 1)) = True Then
                $reptext1 = StringReplace($var1, StringRight($var1, 1), StringRight($var1, 1) + 1)
                Local $sResult = _Excel_RangeRead($oWorkbook, Default, $reptext1)
                _Excel_RangeWrite($oWorkbook, Default, "1st Signature", $reptext1)
            EndIf

            $sSign1 = @DesktopDir & "\img1.jpg"
            $img1find = StringReplace(StringReplace($var1, StringRight($var1, 2), StringRight($var1, 2) + 1), StringMid($var1, 2, 1), "D")
            _Excel_PictureAdd($oWorkbook, Default, $sSign1, $img1find, Default, 100, 25)

        EndIf




        If UBound($aResult2) = 0 Then
            FileWriteLine(@ScriptDir & "\log.txt", $Dir & $aArray[$i] & "  -  Some Text 2")
        ElseIf UBound($aResult2) <> 0 Then
            $var2 = $aResult2[0][2]
            If StringIsDigit(StringRight($var2, 2)) = True Then
                $reptext2 = StringReplace($var2, StringRight($var2, 2), StringRight($var2, 2) + 1)
                Local $sResult2 = _Excel_RangeRead($oWorkbook, Default, $reptext2)
                _Excel_RangeWrite($oWorkbook, Default, "Second Signature", $reptext2)
            ElseIf StringIsDigit(StringRight($var2, 1)) = True Then
                $reptext2 = StringReplace($var2, StringRight($var2, 1), StringRight($var2, 1) + 1)
                Local $sResult = _Excel_RangeRead($oWorkbook, Default, $reptext2)
                _Excel_RangeWrite($oWorkbook, Default, "Second Signature", $reptext2)
            EndIf
            $sSign2 = @DesktopDir & "\img2.jpg"
            $img2find = StringReplace(StringReplace($var2, StringRight($var2, 2), StringRight($var2, 2) + 1), StringMid($var2, 2, 1), "O")
            _Excel_PictureAdd($oWorkbook, Default, $sSign2, $img2find, Default, 100, 25)

        EndIf




        If UBound($aResult3) = 0 Then
            FileWriteLine(@ScriptDir & "\log.txt", $Dir & $aArray[$i] & "  -  Some Text 3")
        ElseIf UBound($aResult3) <> 0 Then
            $var3 = $aResult3[0][2]
            If StringIsDigit(StringRight($var3, 2)) = True Then
                $reptext3 = StringReplace($var3, StringRight($var3, 2), StringRight($var3, 2) + 1)
                Local $sResult3 = _Excel_RangeRead($oWorkbook, Default, $reptext3)
                _Excel_RangeWrite($oWorkbook, Default, "Third Signature", $reptext3) 
            ElseIf StringIsDigit(StringRight($var3, 1)) = True Then
                $reptext3 = StringReplace($var3, StringRight($var3, 1), StringRight($var3, 1) + 1)
                Local $sResult = _Excel_RangeRead($oWorkbook, Default, $reptext3)
                _Excel_RangeWrite($oWorkbook, Default, "Third Signature", $reptext3) 
            EndIf
            $sSign3 = @DesktopDir & "\img3.jpg"
            $img3find = StringReplace(StringReplace($var3, StringRight($var3, 2), StringRight($var3, 2) + 1), StringMid($var3, 2, 1), "AA")
            _Excel_PictureAdd($oWorkbook, Default, $sSign3, $img3find, Default, 100, 25)


        EndIf




Local $aIndex = _Excel_RangeFind($oWorkbook, "Index")
If @error Then Exit MsgBox(0, "Error", "Find Error returned @error = " & @error)
        If UBound($aIndex) = 0 Then
            FileWriteLine(@ScriptDir & "\log.txt", $Dir & $aArray[$i] & "  -  Index")
        ElseIf UBound($aIndex) <> 0 Then
            $var4 = $aIndex[0][2]
            ;_ArrayDisplay($aIndex)
            If StringIsDigit(StringRight($var4, 2)) = True Then
                $reptext4 = StringReplace($var4, StringRight($var4, 2), StringRight($var4, 2) + 1)
                Local $sIndex= _Excel_RangeRead($oWorkbook, Default, $reptext4)
                ;MsgBox(Default, Default, $sIndex & "/" & $sIndex+1)
            If Not StringIsDigit($sIndex) Then FileWriteLine(@ScriptDir & "\log.txt", $Dir & $aArray[$i] & "  Index is not Digit.")
                _Excel_RangeWrite($oWorkbook, Default, $sIndex+1, $reptext4) ; Inlocuire Nume 3
            ElseIf StringIsDigit(StringRight($var4, 1)) = True Then
                $reptext4 = StringReplace($var4, StringRight($var4, 1), StringRight($var4, 1) + 1)
                Local $sIndex = _Excel_RangeRead($oWorkbook, Default, $reptext4)
                ;MsgBox(Default, Default, $sIndex & "/" & $sIndex+1)
                If Not StringIsDigit($sIndex) Then FileWriteLine(@ScriptDir & "\log.txt", $Dir & $aArray[$i] & "  Index is not Digit.")
                _Excel_RangeWrite($oWorkbook, Default, $sIndex+1, $reptext4) ; Inlocuire Nume 3
            EndIf
        EndIf




Local $sDateF = _Excel_RangeRead($oWorkbook, Default, "AC5")
Local $write  = _Excel_RangeWrite($oWorkbook, Default, _NowDate(), "AC5")




        FileWriteLine(@ScriptDir & "\log.txt",$x & ". Link: "& $Dir & $aArray[$i] _
                                         & @CRLF & "Old 1: " & $sResult1 _
                                         & @CRLF & "Old 2: " & $sResult2 _
                                         & @CRLF & "Old 3: " & $sResult3 _
                                         & @CRLF & "Old Index/New Index: " & $sIndex & " / " & $sIndex+1 _
                                         & @CRLF & @CRLF)




$sPathSplit = StringMid($sFullFileSavePathDir, 18)   
DirCreate($sPathSplit)
;MsgBox(Default, Default, $sPathSplit)
If StringRight($aArray[$i], 4) = ".xls" Then
    _Excel_BookSaveAs($oWorkbook, $sSaveLoc & $sPathSplit & $sNewRevName)
EndIf
If StringRight($aArray[$i], 5) = ".xlsx" Then
    _Excel_BookSaveAs($oWorkbook, $sSaveLoc & $sPathSplit & $sNewRevName)
EndIf




        _Excel_BookClose($oWorkbook, False)
    EndIf
    _Excel_BookClose($oWorkbook, False)
        $x = $x + 1
EndIf




If StringRight($aArray[$i], 4) <> ".xls" And StringRight($aArray[$i], 5) <> ".xlsx" Then
;MsgBox(Default, Default, $sPathSplit)
    FileCopy($Dir & $aArray[$i], $sSaveLoc & $sPathSplitD, $FC_OVERWRITE + $FC_CREATEPATH)
EndIf
Next

MsgBox(Default, "Elapsed Time", _Timer_Diff($hStarttime))
_ArrayDisplay($aArray, "File List.")

Thank you.

Edited May 24, 2018 by JohnyX

[water]

Seems you run an older version of AutoIt. Either update AutoIt or implement the following fixes: https://www.autoitscript.com/forum/topic/184106-fixed-versions-of-_excel_rangefind-_excel_bookopen-and-_word_docsaveas/

[JohnyX]

Thanks. 

One more question please, I have this two Arrays which are very big, each one returns about 10k elements. 

Both of them should have identical directory structure and files, still they are not, 5 files are missing from "$Dir1".

$Dir1 = @DesktopDir&"\test1\"
$Dir2 = @DesktopDir&"\test2\"
$aArray1 = _FileListToArrayRec($Dir1, "*", $FLTAR_FILES, $FLTAR_RECUR, $FLTAR_SORT, $FLTAR_RELPATH)
$aArray2 = _FileListToArrayRec($Dir2, "*", $FLTAR_FILES, $FLTAR_RECUR, $FLTAR_SORT, $FLTAR_RELPATH)

How can I compare this two arrays side-by-side to see which files are missing from the first path. 

Or how can I delete all pairs of duplicates to get only those 5 missing files? 

 

Thank you again. 

Edited May 25, 2018 by JohnyX

[JLogan3o13]

@JohnyX this is where searching the forum becomes your best friend. There are numerous threads around comparing items in arrays. Here is just one quick and dirty example you would have found:

#include <Array.au3>

Local $aArray1[0]
Local $aArray2[0]

    For $a = 1 To 100       ;Build arrays
        _ArrayAdd($aArray1, $a)
        Switch $a
            Case 20, 34, 45, 67, 89, 91
                ;Do nothing
            Case Else
                _ArrayAdd($aArray2, $a)
        EndSwitch
    Next

For $iIndex In $aArray1
    If (_ArraySearch($aArray2, $iIndex)) = -1 Then ConsoleWrite($aArray1[$iIndex] & " not found in $aArray2" & @CRLF)
Next

You should be able to modify this to your needs pretty easily.