Jump to content
JohnyX

Excel UDF Error

Recommended Posts

JohnyX
Posted (edited)

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?

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

Untitled.png

Edited by JohnyX

Share this post


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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites
JohnyX
Posted (edited)

Thanks. :D

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

Share this post


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

  • Thanks 1

√-1 2^3 ∑ π, and it was delicious!

Share this post


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

×