Jump to content
tetsajin

Excel with Autoit v3

Recommended Posts

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.

Share this post


Link to post
Share on other sites
Juvigy

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

Share this post


Link to post
Share on other sites
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)

 

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
water
Posted (edited)

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 by water

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites
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)

 

Share this post


Link to post
Share on other sites
water
Posted (edited)

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

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

Edited by water

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites
Subz
Posted (edited)

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

 

Edited by Subz

Share this post


Link to post
Share on other sites
water

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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites
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.

 

Regional.jpg

Share this post


Link to post
Share on other sites
water

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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites
tetsajin

Thank you guys so much! It worked like a charm

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

×