Search the Community

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.

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.

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

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)

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

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

Hi Everyone, I am a beginner and I am currently learning and practicing what Autoit can do, so kindly pardon if it sound's silly. What my program does ----> I had written a program where I have a FOR (i=0 to n) loop which is running for n times. Inside the FOR loop, contents of array is written into excel using _Excel_RangeWrite . _Excel_RangeWrite($oExcelDoc, $oExcelDoc.Activesheet, $arrayname, "A1") Problem ------> During every loop run the contents of column A is only altered What i intend to do ------> For every loop run (i=0,1,2,3...) I want to write the array contents into respective next adjacent excel columns i.e) For i=0 loop, every array content should be written in A Column of excel For i = 1 loop, every array content should be written in B Column of excel. Can anyone give me an idea of how can i do this? Thanks

Hello dear forum user! I try to make an algorithm that reads values from 2 predefined columns until it founds 2 empty cells in that column, if that happens, It should write a SUM function in to the cell after the last read value. It should do this until it founds 3 empty cells. (I order my excel before this part, so these criteriums should work, and they do while I'm testing). Now, I just found a little logical bump on the $SUMsor variable, but I corrected it. Since MsgBox shows me that the readings are good, and the string it should write are also good, and no error flags are detected, I'm not sure what to do next. I'm pretty sure it is attached to the right excel, since every other things works, and it also reads in from the excel It should. Here is my code snippet: ;CONSTRUCTION----------------------------------- ;TO DO: ;Range insert to SULY and HUF, put it in Bold $CellaOlvasoSzamlalo = $CellNumb + 1 Do Local $SUMSor = $CellNumb + 1 ;will be used to determine the "top of the area which it should SUM, CellNumb is the row where the headers/titels were found Local $SULYolvaso1 = _Excel_RangeRead($ExcelObject, Default, $SULYoszlop & $CellaOlvasoSzamlalo) ;Read MsgBox($MB_SYSTEMMODAL, "Értesítés", "$SULYolvaso1:" & $SULYolvaso1 & "") ;Check what is read Local $SULYolvaso2 = _Excel_RangeRead($ExcelObject, Default, $SULYoszlop & $CellaOlvasoSzamlalo + 1) MsgBox($MB_SYSTEMMODAL, "Értesítés", "$SULYolvaso2:" & $SULYolvaso2 & "") Local $SULYolvaso3 = _Excel_RangeRead($ExcelObject, Default, $SULYoszlop & $CellaOlvasoSzamlalo + 2) MsgBox($MB_SYSTEMMODAL, "Értesítés", "$SULYolvaso3:" & $SULYolvaso3 & "") Select Case $SULYolvaso2 = "" and $SULYolvaso3 = "" _Excel_RangeWrite($ExcelObject, $ExcelObject.Activesheet, "=SUM(" & $SULYoszlop & $SUMSor & ":" & $SULYoszlop & $CellaOlvasoSzamlalo & ")") 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, "Értesítés", "Suly oszlop: " & "=SUM(" & $SULYoszlop & $SUMSor & ":" & $SULYoszlop & $CellaOlvasoSzamlalo & ")") _Excel_RangeWrite($ExcelObject, $ExcelObject.Activesheet, "=SUM(" & $sHUFBeszurOszlop & $SUMSor & ":" & $sHUFBeszurOszlop & $CellaOlvasoSzamlalo & ")") 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, "Értesítés", "Suly oszlop: " & "=SUM(" & $sHUFBeszurOszlop & $SUMSor & ":" & $sHUFBeszurOszlop & $CellaOlvasoSzamlalo & ")") $SUMSor = $CellaOlvasoSzamlalo + 3 EndSelect $CellaOlvasoSzamlalo = $CellaOlvasoSzamlalo + 1 Until $SULYolvaso1 = "" and $SULYolvaso2 = "" and $SULYolvaso3 = "" ;$ExcelObject.Activesheet.Range(String($sHUFBeszurOszlop) & $CellaOlvasoSzamlalo).Font.Bold = True ;----------------------------------------------- Also I would like to write it in bold, I have en example commented out at the end, but is there a way with range insert to insert in Bold? Thank you for your help and insight! and have a nice day

Hello First, I thank you for all the help I received here with Excel UDFs and COM objects in the last few days/week. I would like to "pimp" my output excel a bit, and for that I would like to write some results Bold. How do I do that? I tried a few things but most return errors, or doesn't do a thing seemingly. My code right now, this does run, but doesn't make the inserted results bold. (by inserted I mean what I write with _Excel_RangeWrite) _Excel_RangeWrite($ExcelObject, $ExcelObject.Activesheet, "=" & $OSSZEGoszlop & $CellaOlvasoSzamlalo & "*" & String($ArfolyamArray[$DateArrayTimeIndex]) & "", String($sHUFBeszurOszlop) & $CellaOlvasoSzamlalo) $ExcelObject.Activesheet.Range(String($sHUFBeszurOszlop) & $CellaOlvasoSzamlalo).Bold = True Thank you for the help! Edit: I leave this here, added the bold tag, maybe someone find it usefull