Jump to content
Sign in to follow this  
pdixon724

Error writing a formula to Excel

Recommended Posts

pdixon724

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

Share this post


Link to post
Share on other sites
JoHanatCent

Maybe just try:

_ExcelWriteCell($oExcel, $add1, $iNextRow, 7)


Share this post


Link to post
Share on other sites
water

What do you mean by "I'm getting an AutoIt error when writing a formula"?

Please post the error message so we can try to help you.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-10-19 - Version 1.4.10.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - 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
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
pdixon724

I apologize. Here is the AutoIt error:

Line 485 (File "C:\Program Files\AutoIt3\Include\Excel.au3"):

$oExcel.Activesheet.Cells($sRangeOrRow,

$iColumn).FormulaR1C1 = $aFormula

$oExcel.Activesheet.Cells($aRangeOrRow,

$iColumn).FormulaR1C1 = $sFormula^ ERROR

Error: The requested action with this object has failed.

Share this post


Link to post
Share on other sites
water

Can you post the values of $sRangeOrRow and $iColumn please?


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-10-19 - Version 1.4.10.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - 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
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
Reg2Post

I think your issues are on this line:

Local $add1 = "=VLOOKUP($C" & $iNextRow & ",LocationAddresses!$A$2:$G$65,2,FALSE)"

You have not declared the variable $C and "LocationAddresses!" is not needed.

You may want to just try putting the formula into the _ExcelWriteFormula function.

EDIT: Just realized your $C is for a location. If that is the case, you will get an error for trying to find the value of $C0, since you set the value of $iNextRow to 0.

Edited by Reg2Post

Share this post


Link to post
Share on other sites
pdixon724

$sRangeOrRow = 3

$iColumn = 7

Share this post


Link to post
Share on other sites
water

Does it work if you use

$oExcel.Activesheet.Cells($sRangeOrRow, $iColumn).Formula = $aFormula
Edited by water

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-10-19 - Version 1.4.10.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - 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
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
pdixon724

Does it work if you use

$oExcel.Activesheet.Cells($sRangeOrRow, $iColumn).Formula = $aFormula

Indeed it does. Well, I assume you meant to say "$sFormula" at the end there. That works like a champ. Thanks!!

Share this post


Link to post
Share on other sites
water

I took $aFormula from your post #4. Looks like a typo. I'm glad it's working now!


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-10-19 - Version 1.4.10.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - 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
 
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
Sign in to follow this  

  • Similar Content

    • Skeletor
      By Skeletor
      Hi Guys,
      Can anyone point me in the right direction. I'm trying to accomplished conditional formatting with arrows, but the code I have is wrong... 
      .Range("=$A3:$A4000").FormatConditions.Add = (xlCellValue, xlGreater, "=$A:$A") .IconSet(xl3Arrows) ;ActiveWorkbook.iconsets I also tried it like this:
      .Range("=$A3:$A4000").FormatConditions _ .Add(xlCellValue, xlGreater, "=$A:$A") .IconSet(xl3Arrows) ;ActiveWorkbook.iconsets  
    • Skeletor
      By Skeletor
      Hi Guys,
      How would you use _Excel_FilterSet to filter the excel sheet from largest number to smallest number?
      _Excel_FilterSet($oWorkbook, "Sheet1", "A2:I2", 1, ">20", 1, "<40") ; OR _Excel_FilterSet($oWorkbook, "Sheet1", "A2:I2", 1, ">", 1, "<") Excel Sheet attached. Trying to filter on the last column "I2"
      Inventory.xlsx
    • Skeletor
      By Skeletor
      Hi All,
      Has anyone come across this before?
      Code below:
      $SheetList = _Excel_SheetList($oWorkbook) _FileWriteFromArray("C:\" & $SheetListOutput, $SheetList, 1) Result:
      ResultABC| ResultDEF| ResultGHI| ResultJKL| ResultMNO| It created these "|" vertical bars?
    • Skeletor
      By Skeletor
      Hi All,

      I would like to know how you would take a FileLineRead and insert it into an array which then inserts it into Excel?
      One thing to know is the files content is broken up, so I only use half of the content within $FileRead1.
      So its imperative that the $value1, $value2, etc variables be used. 
      Code below:
      $FileRead1 = FileReadLine("C:\temp\sample.txt",1) For $count = 1 To _FileCountLines($FileRead1) Step 1 $string = FileReadLine($FileRead1, $count) $input = StringSplit($string, ",", 1) $value1 = $input[1] $value2 = $input[2] $value3 = $input[3] $value4 = $input[4] _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $value1, "A1") _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $value2, "B1") _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $value3, "C1") _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $value4, "D1") Next  
    • AnonymousX
      By AnonymousX
      Hello,
      I'm trying to write a script that moves copies excel cells into an array. I'll than manipulate the values and send array into another program. 
      I don't want range to be specific to a workbook, or sheet, or set of cells.
      I want user to be able to highlight desired cells and to copy either normally ("Ctrl+C") or by a hotkey ("Alt+C"). 
      Could someone help me with this?
      Thank you,
      I've tried to write the framework: (edited)
      #include <MsgBoxConstants.au3> #include <Array.au3> #include <Excel.au3> HotKeySet("!v", "Pastedata") While True Sleep(1000) WEnd func Makearray() local $bArray ;User has cells already copied ;Convert clipboard into an array ;I don;t know how excel stores data to clipboard so don;t know how to bring it into array _Arraydisplay($bArray) MsgBox(0,0,$bArray) return $bArray endfunc func Pastedata() Local $aArray MsgBox(0,0,"wait",1) ;make array based on assumption user has already copied a range to clipboard $aArray = Makearray() ;paste code ;don;t worry about this I got the rest endfunc  
×