Excel with Autoit v3

[tetsajin]

Hi, I'm fairly new to auto it and I was wondering if there was a way to automate writing a formula into excel but have the cell number change depending on the list. What I want to do is, grab a table from a website and then filter that data for a particular key word. From there, I would input the formula "=LEFT(H1,FIND("/",H1)-1)" to find the string left of the character "/". In Excel I would just drag down the cell and it'll move on to the H2 cell and so on... I can't seem to get it to work within autoit.  I'm also unsure how to make autoit know when to end the "drag down". For now, I thought hardcoding the maximum the data can become would work... Any help would be appreciated.

[Juvigy]

It can be done. Show us what you have so far. Please use 'code tags'.

[Subz]

Example:

#include <Excel.au3>
;~ Example.xlsx Spreadsheet layout
;~ A | B | C
;~ 1 | 9 |
;~ 2 | 8 |
;~ 3 | 7 |
;~ 4 | 6 |
;~ 5 | 5 |
;~ 6 | 4 |
;~ 7 | 3 |
;~ 8 | 2 |
;~ 9 | 1 |

Local $oExcel = _Excel_Open()
Local $oWorkbook = _Excel_BookOpen($oExcel, @ScriptDir & "\Example.xlsx")
;~ Write Formula to C1 = A1 + B1
_Excel_RangeWrite($oWorkbook, Default, "=A1+B1", "C1", False)
;~ Copy and Paste Formula from C1 to C2:C9
_Excel_RangeCopyPaste($oWorkbook.ActiveSheet, "C1", "C2:C9", False, $xlPasteFormulas)

 

[tetsajin]

Local $arr[50]
For $i = 0 to 50 Step +
$arr[$i] = "=LEFT(H" $i ",FIND("/",H" $i ")-1)"
Next

Local $arr[50]
$arr[0] = "=LEFT(H1,FIND("/",H1)-1)"

Thanks for answering so quick! I really only have tried two different ways...
For the first one, I tried to do a loop but I get an error, "Badly formatted variable or macro" and I read somewhere that you can't use a for loop to modify arrays so I gave up on that approach... I also tried to hardcode it but when I do that, my array displays as -1.#IND so I'm unsure where that went wrong.

[water]

I think it should be (untested):

$arr[$i] = '=LEFT(H' & $i & ',FIND("/",H' & $i & ')-1)'

Edit: Comma ist correct, semicolon is a language dependant setting.

Edited March 6, 2018 by water

[Subz]

As Water mentioned use single quotes around double quotes and also need to use $i = 1 To 50 example:

#include <Array.au3>
Local $arr[50]
$arr[0] = UBound($arr) - 1
For $i = 1 To UBound($arr) - 1
    $arr[$i] = '=LEFT(H' & $i & ',FIND("/",H' & $i & ')-1)'
Next
_ArrayDisplay($arr)

 

[water]

Shouldn't there be a ; (instead of ,) as parameter separator in Excel functions?

Edit: Comma ist correct, semicolon is a language dependant setting.

Edited March 6, 2018 by water

[Subz]

Not that I'm aware of, (,) is separator in Excel Left function i.e. Left(text, [num chars])

 

Edited March 6, 2018 by Subz

[water]

I'm running Office 2017 on a german system and the function only works with ";" - so it seems language dependant.

[Subz]

Hi Water, it is a regional setting, I believe, if you look at List separator you'll probably find your seeing a semi-colon, which should mean your csv files are being separated by semi-colon rather than comma as well.

 

[water]

Correct
That's an OS setting. I have updated my post accordingly.

[tetsajin]

Thank you guys so much! It worked like a charm