Jump to content

_Excel_RangeWrite Formula with variable

SkysLastChance
 Share Followers 0
Go to solution Solved by SkysLastChance,

Recommended Posts

SkysLastChance

SkysLastChance 35

Posted
Posted

I have been trying to figure this out longer then I would like to admit. I am betting that I am missing something super simple.  

#include <Excel.au3>

Local $oExcel = _Excel_Open()
Local $oWorkbook = _Excel_BookNew($oExcel)

$vPath = "'J:\Temporary Files\FolderName\FileName.xlsx''!$A:$B,2,0))'"

 _Excel_RangeWrite($oWorkBook, Default, '=IF(D2=D1,"",VLOOKUP(D2,''J:\Temporary Files\FolderName\FileName.xlsx''!$A:$B,2,0))', "A1", False) ;Line 1
 _Excel_RangeWrite($oWorkBook, Default, '=IF(D2=D1,"",VLOOKUP(D2,' & $vPath, "A2", False) ;Line 2
 
ConsoleWrite("'=IF(D2=D1,"",VLOOKUP(D2,''J:\Temporary Files\FolderName\FileName.xlsx''!$A:$B,2,0))'" & @CRLF)
ConsoleWrite("'=IF(D2=D1,"",VLOOKUP(D2,'" & $vPath & @CRLF)

Why is line 1 writing but line 2 is not? 

Any suggestion to lead me in the right direction is appreciated. 

 

Life's simple. You make choices and you don't look back.

Link to post
Share on other sites
  • Solution
SkysLastChance

SkysLastChance 35

Posted
  • Author
  • Solution
Posted

Figured it out. 

#include <Excel.au3>

Local $oExcel = _Excel_Open()
Local $oWorkbook = _Excel_BookNew($oExcel)

$vPath = "'J:\Temporary Files\FolderName\FileName.xlsx'!$A:$B,2,0))"

_Excel_RangeWrite($oWorkBook, Default, '=IF(D2=D1,"",VLOOKUP(D2,''J:\Temporary Files\FolderName\FileName.xlsx''!$A:$B,2,0))', "A3", False) ;Line 1
_Excel_RangeWrite($oWorkBook, Default, '=IF(D2=D1,"",VLOOKUP(D2,' & $vPath, "A4", False) ;Line 2

 

Life's simple. You make choices and you don't look back.

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
 Share
Followers 0
Go to topic listing

  • Recently Browsing   0 members

    No registered users viewing this page.

  • Similar Content

    • goku200
      Seperating filename into coumns in Excel
      By goku200
      I have an Autoit script that lists files from a folder into an array list. Is there a way to separate the filenames by an underscore and include the id, version, name and date into separate columns in Excel.
      Example of filename:
      12345_v1.0_TEST Name [12345]_01.01.2022.html
      12345 would be in one column
      v1.0 would be in another column
      TEST Name [12345] would be in another column
      01.01.2022 would be in another column
      .html would be in another column
      Note: filenames always change each day.
      Here is my code that lists the files into column C and then writes the column Headers into Column D, E, F, G. Just need some help with separating them into columns by the _ delimiter
       
    • SkysLastChance
      _Excel_RangeWrite Formula with "" and '' included
      By SkysLastChance
      I am having a issue of whenever I try to _Excel_RangeWrite a formula that references another workbook I am getting an error @4 and @extended -2147352567

      #include <Excel.au3> #include <MsgBoxConstants.au3> Local $oExcel = _Excel_Open() Local $oWorkbook = _Excel_BookNew($oExcel) _Excel_RangeWrite($oWorkBook,Default,"=IF(D2=D1,"",VLOOKUP(D2,'J:\Temporary Files\FolderName\FileName.xlsx'!$A:$B,2,0))","W2",False) If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeWrite Example 1", "Error writing to worksheet." & @CRLF & "@error = " & @error & ", @extended = " & @extended) MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeWrite Example 1", "String successfully written.") I am not a 100% sure, but I am guessing this is because I have "" and '' in the formula. However, I am not sure how this can best be resolved. 
       
       
    • SkysLastChance
      _Excel_RangeWrite Not working
      By SkysLastChance
      Stuck here scratching my head again.
      I am trying to write a formula    =IF(AND(X2="X",Y2=""),1,"")   to the B2 cell in excel. 
      However, my code does not put anything in.  I also don't get any errors. 
      #include <Excel.au3> Local $oWorkbook $oExcel = _Excel_Open() $sWorkbook = @ScriptDir & "\VitalSite" & " " & @Mon & "_" & @MDAY & "_" & @Year & ".xlsx" $oWorkbook = _Excel_BookOpen($oExcel,$sWorkbook) _Excel_RangeWrite($oWorkbook,Default, '=IF(AND(X' & 2 & '="X",Y' & 2 & '=""),1,"Hello"', "B" & 2)  
       
    • Rskm
      excel printing
      By Rskm
      hi, i am using commands like the below to print values to excel, the array has 20 values, do i have to manually type as a1, b1, c1, d1.... till t1 or is there a short cut(loop) by which i can get the 20 elements printed in a particular row(here row 1) of excel?
      _Excel_RangeWrite($oWorkbook, 1, $array[1], "a1")
      _Excel_RangeWrite($oWorkbook, 1, $array[2], "b1")
      _Excel_RangeWrite($oWorkbook, 1, $array[3], "c1")
       
      thanks
    • JonnyQuy
      Need help _Excel_RangeWrite
      By JonnyQuy
      I need to save the text with excel, each save is 1 row and 2 columns, i did and it does not work please help me Local $oExcel = _Excel_Open() If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeWrite Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended) Local $oWorkbook = _Excel_BookNew($oExcel) If @error Then MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeWrite Example", "Error creating the new workbook." & @CRLF & "@error = " & @error & ", @extended = " & @extended) _Excel_Close($oExcel) Exit EndIf For $i = 1 To 10 Local $aArray2D[1][2] = [[$i],[2222]] _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $aArray2D, "A"&$i&"") Next error_excel.bmp
×
×
  • Create New...