Search the Community

Hello folks, Fairly new to AutoIt, but I've done a few scripts. However, this is my first dealing with Excel. I'm getting an AutoIt error when writing a formula ($add1) to Excel via _ExcelWriteFormula. As for the cell references in this formula; the column number would be static, but the row number is stored in a variable ($iNextRow). I write an array just before the formula and it works fine. Also, MsgBox will display the correct formula stored in $add1. Any thoughts? Thanks in advance, guys! #include <Excel.au3> #include <EditConstants.au3> #include <Misc.au3> #include <Date.au3> #include <GUIConstantsEx.au3> #include <Array.au3> Global $sFilePath = @ScriptDir & "\Shipment Log.xlsx" Global $oExcel = _ExcelBookOpen($sFilePath, 0) #region ### START Koda GUI section ### Form= $Form1 = GUICreate("Shipment Log", 300, 300) $Input1 = GUICtrlCreateInput(_NOWDATE(), 10, 30, 75, 21) $Input2 = GUICtrlCreateInput("", 10, 80, 270, 21) $Input3 = GUICtrlCreateInput("", 10, 130, 270, 21) $Input4 = GUICtrlCreateInput("", 10, 180, 270, 21) $Input5 = GUICtrlCreateInput("", 10, 230, 270, 21) $Label1 = GUICtrlCreateLabel("Today's Date:", 10, 10, 100, 17) $Label2 = GUICtrlCreateLabel("Location:", 10, 60, 55, 17) $Label3 = GUICtrlCreateLabel("Contact(s):", 10, 110, 55, 17) $Label4 = GUICtrlCreateLabel("Item Description(s):", 10, 160, 100, 17) $Label5 = GUICtrlCreateLabel("Item QTY(s):", 10, 210, 75, 17) $Button1 = GUICtrlCreateButton("Submit", 10, 260, 120, 30) GUISetState() #endregion ### END Koda GUI section ### While 1 $nMsg = GUIGetMsg() Switch $nMsg Case $GUI_EVENT_CLOSE _ExcelBookClose($oExcel, 0) Exit Case $Button1 Local $fVisible Local $avData = _ExcelReadArray($oExcel, 1, 1, 1000, 1, 1) Local $iLastUsed = 0, $iNextRow = 0 _WriteOrderID() Local $add1 = "=VLOOKUP($C" & $iNextRow & ",LocationAddresses!$A$2:$G$65,2,FALSE)" ;Variable with Excel formula I'm trying to write Local $aArray[6] = [$iNextRow, GUICtrlRead($Input1), GUICtrlRead($Input2), GUICtrlRead($Input3), GUICtrlRead($Input4), GUICtrlRead($Input5)] _ExcelWriteArray($oExcel, $iNextRow, 1, $aArray) MsgBox(0,"",$add1) MsgBox(0,"",$iNextRow) _ExcelWriteFormula($oExcel, $add1, $iNextRow, 7) ;ERROR LINE _ExcelBookClose($oExcel, 1) MsgBox(0, @ScriptName, "Record Saved.") GUICtrlSetData($Input2, "") GUICtrlSetData($Input3, "") GUICtrlSetData($Input4, "") GUICtrlSetData($Input5, "") EndSwitch WEnd Func _WriteOrderID() For $n = UBound($avData) - 1 To 1 Step -1 If StringStripWS($avData[$n], 8) <> "" Then $iLastUsed = $n ExitLoop EndIf Next If $iLastUsed Then $iNextRow = $iLastUsed + 1 Else $iNextRow = 1 EndIf $sData = _ExcelReadCell($oExcel, $iLastUsed) + 1 EndFunc