help with AHK and EXCEL

[gavva]

I need help with multiple thinks in AHK. It's been quite while since I do my last script in AHK so I ask for help to get at least some right direction how to proceed or if somebody will be so nice to help me with whole task it will be very kind of him.

In Excel I have some informations about products and sum of numbers which comes from cell note. Therefore I need to do all calculation by hands. Example of excel note is below in code. So I need simple regular expression that ignore all data which don't have pcs on end (number) and count them together for example that will be 27500.

So could be somebody be so kind and at least get me right direction....

Code: Select all

plant A
17.5.          8000pcs
platn 9
15.8.          2500pcs   
plant s1
15.8.          1500pcs
plant 23
15.8.          1000ks
factory z22
15.8.             500pcs

factory  ABC
15.8.         15000pcs

[MannyKSoSo]

Is all the pcs data in there own column? If so that makes it easy to grab the data. What you can do is something like this

Code: Select all

X1 := ComObjCreate("Excel.Application")
Xl_Workbook := Xl.Workbooks.Open("File.xls")
Xl.Visible := False ; Make True if you want to see it)
Xl.Range("B").Copy ;This should copy the entire B column
Xl_Workbook.Save()
Xl.CutCopyMode := False
Xl.Workbooks.Close

Then it should be easy to loop through all the data you have. (All the data you have is in clipboard so you can just either break it up via line breaks or write it to a file and read it in a reading loop).

[gavva]

I translate the note badly from my language the problem is that all text is in comments of cell in excel. So I need to parse text from Excel note on active cell

[MannyKSoSo]

So there is a way to separate the two and make the comment onto one line

That video can explain it better than I can in words, but it will extract the comments. I'm sure there is an ComObject that can do this through autohotkey.
https://autohotkey.com/board/topic/6903 ... for-excel/ This is a good link for learning the ComObjects that work in excel

Code: Select all

Xl.Range("A2").Value := "hello world!" ;set cell 'A1' to a string

This specific one will most likely be your baseline for code, just switch the hello world with "=Comment("""A1""")" and it should run the Comment function so you can extract the text

[awel20]

Code: Select all

Haystack =
(LTrim
    plant A
    17.5.          8000pcs
    platn 9
    15.8.          2500pcs   
    plant s1
    15.8.          1500pcs
    plant 23
    15.8.          1000ks
    factory z22
    15.8.             500pcs

    factory  ABC
    15.8.         15000pcs
)


F8::
    StartingPosition := 1, Total := 0
    while FoundPos := RegExMatch(Haystack, "(\d+)pcs", UnquotedOutputVar, StartingPosition)
    {
        ; MsgBox % UnquotedOutputVar1
        Total += UnquotedOutputVar1
        StartingPosition := FoundPos + StrLen(UnquotedOutputVar)
    }
    MsgBox % Total
return


F9::
    App := ComObjActive("Excel.Application")
    ActiveCellCommentText := App.ActiveCell.Comment.Text
    App := ""
    MsgBox % ActiveCellCommentText
return

[gavva]

So I update this to take comments from Excel cell and with regular expression take all numbers which ends with pcs and SUM them . So MsgBox will bring SUM and script will put SUM to clipboard. THX for very usefull help.

Code: Select all

^+E::
    App := ComObjActive("Excel.Application")
    ActiveCellCommentText := App.ActiveCell.Comment.Text
    App := ""
    Haystack := ActiveCellCommentText
    ; MsgBox % ActiveCellCommentText
    
    StartingPosition := 1, Total := 0
    while FoundPos := RegExMatch(Haystack, "(\d+)pcs", UnquotedOutputVar, StartingPosition)
    {
        ; MsgBox % UnquotedOutputVar1
        Total += UnquotedOutputVar1
        StartingPosition := FoundPos + StrLen(UnquotedOutputVar)
    }
    MsgBox, Sum is %Total%
    Clipboard := Total
return