lorenkinzel

script can't find excel anymore

12 posts in this topic

#1 ·  Posted (edited)

I have a few scripts that read/write to excel & have been using for a few years. No problems.

As of today, on my work computer (win 7) the compiled script can not find active cell.

One other computer at work (win 7) can not find the excel book.

A third machine (win 7) has no problem with the script.

A 4th (win 7) works with write errors.

At home, (xp & win 8.1) all is well.

The script is not running over a network, but on the individual machines. I'm speculating an update boo-boo or security issue?

here's the script:

#cs ----------------------------------------------------------------------------
    beginnings of another helper for the GS spreadsheet
#ce ----------------------------------------------------------------------------
#OnAutoItStartRegister "OnAutoItStart"
#include <GUIConstantSex.au3>
#include <GUIConstants.au3>
#include <EditConstants.au3>
#include <WindowsConstants.au3>
#include <ButtonConstants.au3>
#include <StaticConstants.au3>
#include <Excel.au3>
#include <SendMessage.au3>
#include <GUIConstants.au3>
#include <Misc.au3>
Opt("WinTitleMatchMode", 2)
Opt("GUIOnEventMode", 1)
Global $inputType[22]
Global $input1Num[22]
Global $input2Num[22]
Global $input3Num[22]
Global $input4Num[22]
Global $input5Num[22]
Global $inputQty[22]
Global $inputQty2[22]
Global $inputQty3[22]
Global $inputTCode[22]
Global $__Restart = False;part of the restart func
Global $oExcel = ObjGet("", "Excel.Application"); Get an EXISTING Excel Object (book open before script)
Global Const $SC_DRAGMOVE = 0xF012
#Region topic/55024-
Const $SC_MOVE = 0xF010
Const $SC_SIZE = 0xF000

Global $i_DRAGFULLWINDOWS_Current
Global $i_DRAGFULLWINDOWS_Initial = _SPI_GETDRAGFULLWINDOWS()
OnAutoItExitRegister("_Reset_DRAGFULLWINDOWS")

Func _Reset_DRAGFULLWINDOWS()
    DllCall("user32.dll", "int", "SystemParametersInfo", "int", 37, "int", $i_DRAGFULLWINDOWS_Initial, "ptr", 0, "int", 2)
EndFunc   ;==>_Reset_DRAGFULLWINDOWS
#EndRegion topic/55024-

$theForm = GUICreate("Assemblies 2 go", 698, 708, @DesktopWidth - 710, 10, $WS_SIZEBOX + $WS_MAXIMIZEBOX + $WS_MINIMIZEBOX, $WS_EX_ACCEPTFILES)
GUISetBkColor(0x8c6928, $theForm)
WinSetOnTop($theForm, "", 1)
GUISetOnEvent($GUI_EVENT_CLOSE, "xIt")
GUISetOnEvent($GUI_EVENT_PRIMARYDOWN, "move");allow drag from any point on background

;topic/55024-
GUIRegisterMsg($WM_EXITSIZEMOVE, "WM_EXITSIZEMOVE");>>>>>>>>>>>>>>>>>>>>>>>>
GUIRegisterMsg($WM_SYSCOMMAND, "On_WM_SYSCOMMAND");>>>>>>>>>>>>>>>>>>>>>>>>>>
;topic/55024-

Func move()
    _SendMessage($theForm, $WM_SYSCOMMAND, $SC_DRAGMOVE, 0)
EndFunc   ;==>move
Func xIt()
    GUISetStyle($theForm, -1, $WS_EX_COMPOSITED)
    DllCall("user32.dll", "int", "AnimateWindow", "hwnd", $theForm, "int", 1000, "long", 0x00050010) ; implode (animation exit effect)
    Exit
EndFunc   ;==>xIt

$oMyError = ObjEvent("AutoIt.Error", "MyErrFunc") ;Initialize a COM error handler(that I fail to understand.

$reStartButtonBorder = GUICtrlCreateLabel("", 0, 0, 17, 17)
GUICtrlSetState($reStartButtonBorder, $GUI_DISABLE)
GUICtrlSetBkColor($reStartButtonBorder, 0x00F000)
$reStartButton = GUICtrlCreateButton("R", 1, 1, 15, 15)
GUICtrlSetOnEvent($reStartButton, "_ScriptRestart")
GUICtrlSetTip($reStartButton, "restart")
$sourceButtonBorder = GUICtrlCreateLabel("", 640, 620, 17, 17)
GUICtrlSetState($sourceButtonBorder, $GUI_DISABLE)
GUICtrlSetBkColor($sourceButtonBorder, 0x00F000)
$sourceButton = GUICtrlCreateButton("S", 641, 621, 15, 15)
GUICtrlSetOnEvent($sourceButton, "showSource")
GUICtrlSetTip($sourceButton, "deposit source-file at desktop")
;>>>>>>>>>>>>>>>>>>>mat type
For $y = 0 To 21
    $inputType[$y] = GUICtrlCreateInput("", 1, ($y) * 20 + 66, 145, 18)
    GUICtrlSetBkColor($inputType[$y], 0xC0D0FF)
Next
;>>>>>>>>>>>>>>>>Qty per foot Primary assembly
For $y = 0 To 21
    $inputQty[$y] = GUICtrlCreateInput("", 151, ($y) * 20 + 66, 60, 18)
    GUICtrlSetBkColor($inputQty[$y], 0xC0D0FF)
    GUICtrlSetTip($inputQty[$y], ' Formulas in this column DO NOT begin with an " = "')
Next
;>>>>>>>>>>>>>>>>Qty per foot Secondary assembly
For $y = 0 To 21
    $inputQty2[$y] = GUICtrlCreateInput("", 151 + 65, ($y) * 20 + 66, 60, 18)
    GUICtrlSetBkColor($inputQty2[$y], 0xC0D0FF)
    GUICtrlSetTip($inputQty2[$y], ' Formulas in this column DO NOT begin with an " = "')
Next
;>>>>>>>>>>>>>>>>Qty per foot Third assembly
For $y = 0 To 21
    $inputQty3[$y] = GUICtrlCreateInput("", 151 + 130, ($y) * 20 + 66, 60, 18)
    GUICtrlSetBkColor($inputQty3[$y], 0xC0D0FF)
    GUICtrlSetTip($inputQty3[$y], ' Formulas in this column DO NOT begin with an " = "')
Next
;>>>>>multiply qyt per foot to each grid
$qtyMultiplyButton = GUICtrlCreateButton("populate", 164, 510, 85, 21)
GUICtrlSetOnEvent($qtyMultiplyButton, "populate")
;>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>grid multiplier << how many feet in grid>>
$gridMultLabel = GUICtrlCreateLabel("L.F. primary      -->", 265, 1, 90, 18);265
$grid1Multiplier = GUICtrlCreateInput("", 361, 1, 50, 18)
GUICtrlSetBkColor($grid1Multiplier, 0xC0D0FF)
$grid2Multiplier = GUICtrlCreateInput("", 415, 1, 50, 18)
GUICtrlSetBkColor($grid2Multiplier, 0xC0D0FF)
$grid3Multiplier = GUICtrlCreateInput("", 469, 1, 50, 18)
GUICtrlSetBkColor($grid3Multiplier, 0xC0D0FF)
$grid4Multiplier = GUICtrlCreateInput("", 523, 1, 50, 18)
GUICtrlSetBkColor($grid4Multiplier, 0xC0D0FF)
$grid5Multiplier = GUICtrlCreateInput("", 577, 1, 50, 18)
GUICtrlSetBkColor($grid5Multiplier, 0xC0D0FF)
;==========================================multiplier for secondary assembly type
$gridMultLabel2 = GUICtrlCreateLabel("L.F. secondary -->", 265, 21, 90, 18)
$grid1Multiplier2 = GUICtrlCreateInput("", 361, 21, 50, 18)
GUICtrlSetBkColor($grid1Multiplier2, 0xC0D0FF)
$grid2Multiplier2 = GUICtrlCreateInput("", 415, 21, 50, 18)
GUICtrlSetBkColor($grid2Multiplier2, 0xC0D0FF)
$grid3Multiplier2 = GUICtrlCreateInput("", 469, 21, 50, 18)
GUICtrlSetBkColor($grid3Multiplier2, 0xC0D0FF)
$grid4Multiplier2 = GUICtrlCreateInput("", 523, 21, 50, 18)
GUICtrlSetBkColor($grid4Multiplier2, 0xC0D0FF)
$grid5Multiplier2 = GUICtrlCreateInput("", 577, 21, 50, 18)
GUICtrlSetBkColor($grid5Multiplier2, 0xC0D0FF)
;==========================================multiplier for third assembly type
$gridMultLabel3 = GUICtrlCreateLabel("L.F. 3rd -->", 265, 41, 90, 18)
$grid1Multiplier3 = GUICtrlCreateInput("", 361, 41, 50, 18)
GUICtrlSetBkColor($grid1Multiplier3, 0xC0D0FF)
$grid2Multiplier3 = GUICtrlCreateInput("", 415, 41, 50, 18)
GUICtrlSetBkColor($grid2Multiplier3, 0xC0D0FF)
$grid3Multiplier3 = GUICtrlCreateInput("", 469, 41, 50, 18)
GUICtrlSetBkColor($grid3Multiplier3, 0xC0D0FF)
$grid4Multiplier3 = GUICtrlCreateInput("", 523, 41, 50, 18)
GUICtrlSetBkColor($grid4Multiplier3, 0xC0D0FF)
$grid5Multiplier3 = GUICtrlCreateInput("", 577, 41, 50, 18)
GUICtrlSetBkColor($grid5Multiplier3, 0xC0D0FF)


;>>>>>>>>>>>>>>>>>>>Qty1
For $y = 0 To 21
    $input1Num[$y] = GUICtrlCreateInput("", 361, ($y) * 20 + 66, 50, 18)
Next
;>>>>>>>>>>>>>>>>>>>Qty2
For $y = 0 To 21
    $input2Num[$y] = GUICtrlCreateInput("", 415, ($y) * 20 + 66, 50, 18)
Next
;>>>>>>>>>>>>>>>>>>>Qty3
For $y = 0 To 21
    $input3Num[$y] = GUICtrlCreateInput("", 469, ($y) * 20 + 66, 50, 18)
Next
;>>>>>>>>>>>>>>>>>>>Qty4
For $y = 0 To 21
    $input4Num[$y] = GUICtrlCreateInput("", 523, ($y) * 20 + 66, 50, 18)
Next
;>>>>>>>>>>>>>>>>>>>Qty5
For $y = 0 To 21
    $input5Num[$y] = GUICtrlCreateInput("", 578, ($y) * 20 + 66, 50, 18)
Next
;>>>>>>>time-code inputs
For $y = 0 To 21
    $inputTCode[$y] = GUICtrlCreateInput("", 635, ($y) * 20 + 66, 60, 18)
    GUICtrlSetBkColor($inputTCode[$y], 0xC0D0FF)
    GUICtrlSetTip($inputTCode[$y], 'any formulas in this column MUST begin with an " = "')
Next
;>navigation instruments
$bookNameInput = GUICtrlCreateLabel("", 1, 610, 283, 18)
$activeRowNumInput = GUICtrlCreateLabel("active row #", 1, 629, 80, 18)
$activeColumnNumInput = GUICtrlCreateLabel("active column #", 1, 648, 80, 18)
$row_colSplitButton = GUICtrlCreateButton("get excel cell", 83, 628, 121, 20)
GUICtrlSetOnEvent($row_colSplitButton, "splitCellNum")
;>>>>>>>>>write item types to excel
$writeButton = GUICtrlCreateButton("write data", 10, 510, 121, 21)
GUICtrlSetOnEvent($writeButton, "writeToExcel")
;>>>>>>>>>write quantities to excel
$grid1Button = GUICtrlCreateButton("write", 361, 510, 50, 20);361
GUICtrlSetOnEvent($grid1Button, "WriteGrid1")
$grid2Button = GUICtrlCreateButton("write", 415, 510, 50, 20);415
GUICtrlSetOnEvent($grid2Button, "WriteGrid2")
$grid3Button = GUICtrlCreateButton("write", 469, 510, 50, 20);469
GUICtrlSetOnEvent($grid3Button, "WriteGrid3")
$grid4Button = GUICtrlCreateButton("write", 523, 510, 50, 20);523
GUICtrlSetOnEvent($grid4Button, "WriteGrid4")
$grid5Button = GUICtrlCreateButton("write", 577, 510, 50, 20);577
GUICtrlSetOnEvent($grid5Button, "WriteGrid5")
;>>>>>>>>>write time-codes to excel
$TCodeButton = GUICtrlCreateButton("write", 640, 510, 50, 20)
GUICtrlSetOnEvent($TCodeButton, "writeTimeCode")
;>>>>>>>>>>section visual seperators (black lines)GUICtrlCreateGraphic
$line = GUICtrlCreateLabel("", 1, 540, 681, 4)
GUICtrlSetBkColor($line, 0x000000)
$line2 = GUICtrlCreateLabel("", 147, 540, 4, 60)
GUICtrlSetBkColor($line2, 0x000000)
$line3 = GUICtrlCreateLabel("", 1, 600, 150, 4)
GUICtrlSetBkColor($line3, 0x000000)
;>>>>>>> .ini items
$nuIniInput = GUICtrlCreateInput("", 1, 550, 145, 21);enter name of new .ini(assembly)
$nuIniButton = GUICtrlCreateButton("save as new assembly", 1, 575, 145, 21);save assembly
GUICtrlSetTip($nuIniButton, "enter name first")
GUICtrlSetOnEvent($nuIniButton, "saveNuAssembly")
$loadAssemblyButton = GUICtrlCreateButton("load saved assembly", 160, 550, 145, 21);Load existing assembly
GUICtrlSetOnEvent($loadAssemblyButton, "loadAssembly")

$dragLoadAssemblyInput = GUICtrlCreateInput("", 315, 550, 245, 50);Load existing assembly via drag-drop
GUICtrlSetState($dragLoadAssemblyInput, $GUI_DROPACCEPTED)
GUICtrlSetTip($dragLoadAssemblyInput, "drag .ass file here")
GUISetOnEvent($GUI_EVENT_DROPPED, "dragLoadAssembly")

$refreshButton = GUICtrlCreateButton("find excel book", 315, 605, 245, 20)
GUICtrlSetOnEvent($refreshButton, "refreshOexcel")

$wintitle = WinGetTitle(".xls")
GUICtrlSetData($bookNameInput, $wintitle)
GUISetState(@SW_SHOW)

While 1
    Sleep(10)
WEnd

;topic/55024-
Func On_WM_SYSCOMMAND($hWnd, $Msg, $wParam, $lParam)
    Switch BitAND($wParam, 0xFFF0)
        Case $SC_MOVE, $SC_SIZE
            $i_DRAGFULLWINDOWS_Current = _SPI_GETDRAGFULLWINDOWS()
            DllCall("user32.dll", "int", "SystemParametersInfo", "int", 37, "int", 0, "ptr", 0, "int", 2)
    EndSwitch
EndFunc   ;==>On_WM_SYSCOMMAND

Func WM_EXITSIZEMOVE()
    DllCall("user32.dll", "int", "SystemParametersInfo", "int", 37, "int", $i_DRAGFULLWINDOWS_Current, "ptr", 0, "int", 2)
EndFunc   ;==>WM_EXITSIZEMOVE

Func _SPI_GETDRAGFULLWINDOWS()
    $tBool = DllStructCreate("int")
    DllCall("user32.dll", "int", "SystemParametersInfo", "int", 38, "int", 0, "ptr", DllStructGetPtr($tBool), "int", 0)
    Return DllStructGetData($tBool, 1)
EndFunc   ;==>_SPI_GETDRAGFULLWINDOWS
;topic/55024-

Func MyErrFunc();by others
    Local $err = $oMyError.number
    If $err = 0 Then $err = -1
    SetError($err)
EndFunc   ;==>MyErrFunc

Func refreshOexcel();==========================Allows reading Excel book that was started after script. I forget sometimes to stert Excel first.
    $oExcel = ""
    $wintitle = ""
    Sleep(100)
    $oExcel = ObjGet("", "Excel.Application")
    $wintitle = WinGetTitle(".xls")
    Sleep(100)
    GUICtrlSetData($bookNameInput, $wintitle)
EndFunc   ;==>refreshOexcel

Func splitCellNum();get R1C1 address of selected cell
    If GUICtrlRead($bookNameInput) <> "" Then;keeps it from crashing if excel is not open
        While WinExists($wintitle)
            $Row = $oExcel.ActiveCell.Row
            $Column = $oExcel.ActiveCell.Column
            GUICtrlSetData($activeRowNumInput, $Row)
            GUICtrlSetData($activeColumnNumInput, $Column)
            Sleep(100)
            ExitLoop
        WEnd
    EndIf
EndFunc   ;==>splitCellNum

Func writeToExcel();the item-type inputs
    splitCellNum()
    Sleep(100)
    GUICtrlSetBkColor($writeButton, 0xFFD0B0)
    Local $Row = Number(GUICtrlRead($activeRowNumInput))
    Local $rowNum = $Row
    Local $Column = Number(GUICtrlRead($activeColumnNumInput))
    For $y = 0 To 21
        _ExcelWriteCell($oExcel, GUICtrlRead($inputType[$y]), $rowNum, $Column)
        Sleep(500)
        $rowNum += 1
    Next
    GUICtrlSetStyle($writeButton, 0)
EndFunc   ;==>writeToExcel

Func WriteGrid1();write the quantity inputs to excel
    splitCellNum()
    Sleep(100)
    GUICtrlSetBkColor($grid1Button, 0xFFD0B0); so you know when function is still running.
    Local $Row = Number(GUICtrlRead($activeRowNumInput))
    Local $rowNum = $Row
    Local $Column = Number(GUICtrlRead($activeColumnNumInput))
    For $y = 0 To 21
        _ExcelWriteCell($oExcel, GUICtrlRead($input1Num[$y]), $rowNum, $Column)
        Sleep(10)
        $rowNum += 1
    Next
    GUICtrlSetStyle($grid1Button, 0);the function is finished. The button is no longer orange.
EndFunc   ;==>WriteGrid1

Func WriteGrid2()
    splitCellNum()
    Sleep(100)
    GUICtrlSetBkColor($grid2Button, 0xFFD0B0)
    Local $Row = Number(GUICtrlRead($activeRowNumInput))
    Local $rowNum = $Row
    Local $Column = Number(GUICtrlRead($activeColumnNumInput))
    For $y = 0 To 21
        _ExcelWriteCell($oExcel, GUICtrlRead($input2Num[$y]), $rowNum, $Column)
        Sleep(10)
        $rowNum += 1
    Next
    GUICtrlSetStyle($grid2Button, 0)
EndFunc   ;==>WriteGrid2

Func WriteGrid3()
    splitCellNum()
    Sleep(100)
    GUICtrlSetBkColor($grid3Button, 0xFFD0B0)
    Local $Row = Number(GUICtrlRead($activeRowNumInput))
    Local $rowNum = $Row
    Local $Column = Number(GUICtrlRead($activeColumnNumInput))
    For $y = 0 To 21
        _ExcelWriteCell($oExcel, GUICtrlRead($input3Num[$y]), $rowNum, $Column)
        Sleep(10)
        $rowNum += 1
    Next
    GUICtrlSetStyle($grid3Button, 0)
EndFunc   ;==>WriteGrid3

Func WriteGrid4()
    splitCellNum()
    Sleep(100)
    GUICtrlSetBkColor($grid4Button, 0xFFD0B0)
    Local $Row = Number(GUICtrlRead($activeRowNumInput))
    Local $rowNum = $Row
    Local $Column = Number(GUICtrlRead($activeColumnNumInput))
    For $y = 0 To 21
        _ExcelWriteCell($oExcel, GUICtrlRead($input4Num[$y]), $rowNum, $Column)
        Sleep(10)
        $rowNum += 1
    Next
    GUICtrlSetStyle($grid4Button, 0)
EndFunc   ;==>WriteGrid4

Func WriteGrid5()
    splitCellNum()
    Sleep(100)
    GUICtrlSetBkColor($grid5Button, 0xFFD0B0)
    Local $Row = Number(GUICtrlRead($activeRowNumInput))
    Local $rowNum = $Row
    Local $Column = Number(GUICtrlRead($activeColumnNumInput))
    For $y = 0 To 21
        _ExcelWriteCell($oExcel, GUICtrlRead($input5Num[$y]), $rowNum, $Column)
        Sleep(10)
        $rowNum += 1
    Next
    GUICtrlSetStyle($grid5Button, 0)
EndFunc   ;==>WriteGrid5

Func writeTimeCode();allows entering time codes without overwriting stock codes
    splitCellNum()
    Sleep(100)
    GUICtrlSetBkColor($TCodeButton, 0xFFD0B0)
    Local $Row = Number(GUICtrlRead($activeRowNumInput))
    Local $rowNum = $Row
    Local $Column = Number(GUICtrlRead($activeColumnNumInput))
    For $y = 0 To 21
        Local $materialType = GUICtrlRead($inputType[$y])
        Local $noEqSign = StringLeft($materialType, 1)
        If $noEqSign <> "=" Then
            _ExcelWriteCell($oExcel, GUICtrlRead($inputTCode[$y]), $rowNum, $Column)
        EndIf
        Sleep(10)
        $rowNum += 1
    Next
    GUICtrlSetStyle($TCodeButton, 0)
EndFunc   ;==>writeTimeCode

Func populate();allows you to enter formula rather than just a number. Easier to modify in other projects.
    For $y = 0 To 21
        GUICtrlSetData($inputQty[$y], Execute(GUICtrlRead($inputQty[$y])));each input becomes a calculator
    Next
    populateGrids()

    For $y = 0 To 21
        GUICtrlSetData($inputQty2[$y], Execute(GUICtrlRead($inputQty2[$y])))
    Next
    populateGrids()

    For $y = 0 To 21
        GUICtrlSetData($inputQty3[$y], Execute(GUICtrlRead($inputQty3[$y])))
    Next
    populateGrids()
EndFunc   ;==>populate

Func populateGrids();Multiply item/L.F. by the footage in each grid
    ;inptuQty = how much per foot       gridMultiplier = how many feet
    Local $footage1 = Number(GUICtrlRead($grid1Multiplier))
    Local $footage1b = Number(GUICtrlRead($grid1Multiplier2))
    Local $footage1c = Number(GUICtrlRead($grid1Multiplier3))
    For $y = 0 To 21
        $a = (Number(GUICtrlRead($inputQty[$y])) * $footage1)
        $b = Ceiling($a)
        $c = (Number(GUICtrlRead($inputQty2[$y])) * $footage1b)
        $d = Ceiling($c)
        $e = (Number(GUICtrlRead($inputQty3[$y])) * $footage1c)
        $f = Ceiling($e)
        GUICtrlSetData($input1Num[$y], $b + $d + $f)
        If GUICtrlRead($input1Num[$y]) = 0 Then
            GUICtrlSetData($input1Num[$y], "")
        EndIf
    Next

    Local $footage2 = Number(GUICtrlRead($grid2Multiplier))
    Local $footage2b = Number(GUICtrlRead($grid2Multiplier2))
    Local $footage2c = Number(GUICtrlRead($grid2Multiplier3))
    For $y = 0 To 21
        $a = (Number(GUICtrlRead($inputQty[$y])) * $footage2)
        $b = Ceiling($a)
        $c = (Number(GUICtrlRead($inputQty2[$y])) * $footage2b)
        $d = Ceiling($c)
        $e = (Number(GUICtrlRead($inputQty3[$y])) * $footage2c)
        $f = Ceiling($e)
        GUICtrlSetData($input2Num[$y], $b + $d + $f)
        If GUICtrlRead($input2Num[$y]) = 0 Then
            GUICtrlSetData($input2Num[$y], "")
        EndIf
    Next

    Local $footage3 = Number(GUICtrlRead($grid3Multiplier))
    Local $footage3b = Number(GUICtrlRead($grid3Multiplier2))
    Local $footage3c = Number(GUICtrlRead($grid3Multiplier3))
    For $y = 0 To 21
        $a = (Number(GUICtrlRead($inputQty[$y])) * $footage3)
        $b = Ceiling($a)
        $c = (Number(GUICtrlRead($inputQty2[$y])) * $footage3b)
        $d = Ceiling($c)
        $e = (Number(GUICtrlRead($inputQty3[$y])) * $footage3c)
        $f = Ceiling($e)
        GUICtrlSetData($input3Num[$y], $b + $d + $f)
        If GUICtrlRead($input3Num[$y]) = 0 Then
            GUICtrlSetData($input3Num[$y], "")
        EndIf
    Next

    Local $footage4 = Number(GUICtrlRead($grid4Multiplier))
    Local $footage4b = Number(GUICtrlRead($grid4Multiplier2))
    Local $footage4c = Number(GUICtrlRead($grid4Multiplier3))
    For $y = 0 To 21
        $a = (Number(GUICtrlRead($inputQty[$y])) * $footage4)
        $b = Ceiling($a)
        $c = (Number(GUICtrlRead($inputQty2[$y])) * $footage4b)
        $d = Ceiling($c)
        $e = (Number(GUICtrlRead($inputQty3[$y])) * $footage4c)
        $f = Ceiling($e)
        GUICtrlSetData($input4Num[$y], $b + $d + $f)
        If GUICtrlRead($input4Num[$y]) = 0 Then
            GUICtrlSetData($input4Num[$y], "")
        EndIf
    Next

    Local $footage5 = Number(GUICtrlRead($grid5Multiplier))
    Local $footage5b = Number(GUICtrlRead($grid5Multiplier2))
    Local $footage5c = Number(GUICtrlRead($grid5Multiplier3))
    For $y = 0 To 21
        $a = (Number(GUICtrlRead($inputQty[$y])) * $footage5)
        $b = Ceiling($a)
        $c = (Number(GUICtrlRead($inputQty2[$y])) * $footage5b)
        $d = Ceiling($c)
        $e = (Number(GUICtrlRead($inputQty3[$y])) * $footage5c)
        $f = Ceiling($e)
        GUICtrlSetData($input5Num[$y], $b + $d + $f)
        If GUICtrlRead($input5Num[$y]) = 0 Then
            GUICtrlSetData($input5Num[$y], "")
        EndIf
    Next
EndFunc   ;==>populateGrids

Func saveNuAssembly(); save the assembly as an .ini (I chose the ext .ass). For Assembly.
    $name = GUICtrlRead($nuIniInput)
    For $y = 0 To 21
        IniWrite(@ScriptDir & "\" & $name & ".ass", $y, "item", GUICtrlRead($inputType[$y]))
        IniWrite(@ScriptDir & "\" & $name & ".ass", $y, "qty", GUICtrlRead($inputQty[$y]))
        IniWrite(@ScriptDir & "\" & $name & ".ass", $y, "qty2", GUICtrlRead($inputQty2[$y]))
        IniWrite(@ScriptDir & "\" & $name & ".ass", $y, "qty3", GUICtrlRead($inputQty3[$y]))
        IniWrite(@ScriptDir & "\" & $name & ".ass", $y, "T-Code", GUICtrlRead($inputTCode[$y]))
    Next
EndFunc   ;==>saveNuAssembly

Func loadAssembly(); load an existing assembly into the "item" & "qty" inputs
    ;If GUICtrlRead($dragLoadAssemblyInput) <> "" Then
    ;   dragLoadAssembly()
    ;Else

    $location = @ScriptDir
    $openedFile = FileOpenDialog("select assembly", $location, "(*.ass)", 5)
    For $y = 0 To 21
        GUICtrlSetData($inputType[$y], IniRead($openedFile, $y, "item", ""))
        GUICtrlSetData($inputQty[$y], IniRead($openedFile, $y, "qty", ""))
        GUICtrlSetData($inputQty2[$y], IniRead($openedFile, $y, "qty2", ""))
        GUICtrlSetData($inputQty3[$y], IniRead($openedFile, $y, "qty3", ""))
        GUICtrlSetData($inputTCode[$y], IniRead($openedFile, $y, "T-Code", ""))

    Next
    ;EndIf
EndFunc   ;==>loadAssembly

Func dragLoadAssembly(); load an existing assembly into the "item" & "qty" inputs via drag-drop
    Local $read = GUICtrlRead($dragLoadAssemblyInput)
    If $read <> "" Then
        Local $openedFile = $read
        For $y = 0 To 21
            GUICtrlSetData($inputType[$y], IniRead($openedFile, $y, "item", ""))
            GUICtrlSetData($inputQty[$y], IniRead($openedFile, $y, "qty", ""))
            GUICtrlSetData($inputQty2[$y], IniRead($openedFile, $y, "qty2", ""))
            GUICtrlSetData($inputTCode[$y], IniRead($openedFile, $y, "T-Code", ""))
            GUICtrlSetData($inputQty3[$y], IniRead($openedFile, $y, "qty3", ""))
            GUICtrlSetData($inputTCode[$y], IniRead($openedFile, $y, "T-Code", ""))
        Next
    EndIf
EndFunc   ;==>dragLoadAssembly

;>>>>>>>>>>>>>>script restart
Func _ScriptRestart();_ScriptRestart($fExit = 1)   Yashied
    Local $fExit = 1
    Local $Pid
    If Not $__Restart Then
        If @Compiled Then
            $Pid = Run(@ScriptFullPath & ' ' & $CmdLineRaw, @ScriptDir, Default, 1)
        Else
            $Pid = Run(@AutoItExe & ' "' & @ScriptFullPath & '" ' & $CmdLineRaw, @ScriptDir, Default, 1)
        EndIf
        If @error Then
            Return SetError(@error, 0, 0)
        EndIf
        StdinWrite($Pid, @AutoItPID)
    EndIf
    $__Restart = 1
    If $fExit Then
        Sleep(50)
        Exit
    EndIf
    Return 1
EndFunc   ;==>_ScriptRestart

Func OnAutoItStart()
    Sleep(50)
    Local $Pid = ConsoleRead(1)
    If @extended Then
        While ProcessExists($Pid)
            Sleep(100)
        WEnd
    EndIf
EndFunc   ;==>OnAutoItStart
;>>>>>>>>>>>>>>>>>deposit source code .au3 at desktop
;This is a literal filepath & must be changed to where YOUR script is
Func showSource()
    FileInstall("Q:\au3 projects\level 11\a2g dev\IP_A2G.au3", @DesktopDir & "\IP_A2G.au3");check / fix path
EndFunc   ;==>showSource

Any thoughts? mine have run dry. The script works fine other than it can not communicate with excel anymore, which is a deal-breaker.

Thanks for any input.

AutoIt Version: 3.3.9.22 (Beta) so non-compiled version won't work for everyone.

Edited by lorenkinzel
gave AutoIt version

Share this post


Link to post
Share on other sites



Which version of Excel do you use?
Is there an IT department that updated Office?
Did they change some Group Policy settings lately? (Application directory whitelisting to stop Ransomware ...)
You have absolutely no error checking in your script. Add a COM error handler to check for errors.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - 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

#3 ·  Posted (edited)

office 07 on the 2 that do not work. Office 10 on the 1 that works & the 1 that works with errors.
no IT dept, just a lo-dollar guy who comes 2 days after I fix whatever problem (only 6 machines in the office).
As far as I can tell there is no group policy (windows cannot find gpedit.msc)
Error checking: no intelligent reply possible
Line 64  $oMyError = ObjEvent("AutoIt.Error", "MyErrFunc") ;Initialize a COM error handler(that I fail to understand.
But it worked well for years! (not an excuse for bad coding practice)

Point in curiosity being: why a functioning script stopped communicating with excel.

I realize I have not given you much to work with.

EDIT: on the error checking issue I was not contradicting you. I am in estimating & seldom deal with real people.

My real people skills are somewhat less than polished.

Edited by lorenkinzel
crappy wording

Share this post


Link to post
Share on other sites

The COM error function you use doesn't tell us very much ;)
Use the function from the help file. Gives us an error description etc.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - 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

Will do on Monday.

I previously thought that you didn't understand what I was saying.

It appears to be the other way around.

Within the help file I am finding: 

$oMyError = ObjEvent("AutoIt.Error","MyErrFunc") ; Install a custom error handler

But what does "MyErrFunc" actually tell us.

Please point me in a different direction.

I see now there must be an error function that tells us WTF is going on.

I just do not know where to find it . I appreciate your patience & do not expect you to fix this for me.

 

Share this post


Link to post
Share on other sites

Use something like this to have an error function that gives us maximum information:

Func MyErrFunc($oError)
    MsgBox(0, "COM Error", _
            @ScriptName & " (" & $oError.scriptline & ") : ==> COM Error intercepted !" & @CRLF & _
            "err.number is: " & "0x" & Hex($oError.number) & @CRLF & _
            "err.windescription: " & $oError.windescription & @CRLF & _
            "err.description is: " & $oError.description & @CRLF & _
            "err.source is: " & $oError.source & @CRLF & _
            "err.helpfile is: " & $oError.helpfile & @CRLF & _
            "err.helpcontext is: " & $oError.helpcontext & @CRLF & _
            "err.lastdllerror is: " & $oError.lastdllerror & @CRLF & _
            "err.scriptline is: " & $oError.scriptline & @CRLF & _
            "err.retcode is: " & "0x" & Hex($oError.retcode))
EndFunc   ;==>_ErrFunc

 


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - 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

This morning I replaced my error func with the one you gave me.

com err_A2G.au3(278):==> COM Error intercepted!

err.number is: 0x0000004

err.windescription is:

err.source is:

err.helpfile is:

err.helpcontext is:

err.lastdllerror is: 0

err.scriptline is: 278

err.retcode is: 0x00000000

A second error message gave row 279 as the error.

Windescription: null pointer assignment

(line 278 is $Row = $oExcel.ActiveCell.Row    line 279 is $Column = $oExcel.ActiveCell.Column)

 

It simply can't find the active cell. On a wild hunch I shut off DropBox & The script works again.

I have no idea whatsoever has happened.

 

 

Share this post


Link to post
Share on other sites

According to MSDN:

Quote

 

Returns a Range object that represents the active cell in the active window (the window on top) or in the specified window. If the window isn't displaying a worksheet, this property fails. Read-only.

Maybe the active window does not display worksheet?


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - 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

It was being displayed, as I was using it.

I turned DropBox back on & it failed with the same error message.

Once DropBox was off again, the script worked fine again.

No idea why but it does not work while DropBox is running.

Share this post


Link to post
Share on other sites

That's very strange :huh: I searched the web but couldn't find anything describing this problem :(


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - 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

Yes it is. The only reason I tried it is that I normally have DropBox turned off.

3 trials both ways & the results show DropBox to be causing my difficulties.

The 1 machine in the office that could run the script properly does not have DropBox.

If DropBox were the only problem surely someone else would have noticed by now.

My issue seems to be over, if not solved & I no longer poo-poo error handlers.

Thanks for your time.

Share this post


Link to post
Share on other sites

:)


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - 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

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