_Excel_RangeWrite Formula with "" and '' included

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

 

 

Edited February 16, 2022 by SkysLastChance

[water]

Do you get a valid result when you enter the formula by hand? I think parameters in Excel need to be separated by semicolon (at least in the german version).

[SkysLastChance]

It does work and I do get the desired result. Here is what is looks like exactly though.

It basically just does a simple vlookup one time for each unique look up value from the sheet 12-2021. 

Edited February 16, 2022 by SkysLastChance

[water]

According to the help file: "Strings are enclosed in double-quotes like "this". If you want a string to actually contain a double-quote use it twice ...".

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

This works for me. You might have to replace ; with , to make it work for you.

[SkysLastChance]

That did the trick. Thank you!

_Excel_RangeWrite($oWorkBook, Default, '=IF(D2=D1,"",VLOOKUP(D2,''J:\Temporary Files\FolderName\FileName.xlsx''!$A:$B,2,0))', "W2", False)

 

 

[water]