Get Excel row number to a variable Topic is solved

[dharmang1910]

Hello,
I have excel sheet with four column in it. First is unique customer ID, second is token number, third is price and fourth is product code.
I want to find row number by finding token number by entering token number in inputbox. For example if 99 token number is in B5 cell then i want to search
row number 5 and assign 5 to variable called "nn". After putting 5 into "nn" variable i can used this variable in my script and can complete my whole script, but i can not figure out how can find row number by searching with inputbox and put found row number into a variable. Here is my whole script with missing part of get row number. Can anybody help me to complete the missing part of script.

Code: Select all

F10::
FilePath = %A_Desktop%\Order List.xls
oWorkbook := ComObjGet(FilePath)


InputBox, tn, Token to find, please enter the Token No. to find Row No.

[Missing part :
Finding entered token number (which are assigned to "tn" variable) and get the row number of Column "B" and assign to "nn" variable
:Missing Part]

n1 := oWorkbook.Worksheets(1).Range("A" nn).text ;get the customer unique ID from a cell with column "A" and row "nn"
n2 := oWorkbook.Worksheets(1).Range("B" nn).text
n3 := oWorkbook.Worksheets(1).Range("C" nn).text
n4 := oWorkbook.Worksheets(1).Range("D" nn).text
ControlSetText, Edit1, % n1, Test Software ; put the cell ("A" nn) value to test software's textbox.
ControlSetText, Edit2, % n2, Test Software
ControlSetText, Edit3, % n3, Test Software
ControlSetText, Edit4, % n4, Test Software
return

[kon]

Hi

Try this:

Code: Select all

F10::
    InputBox, tn, Token to find, please enter the Token No. to find Row No.
    if (ErrorLevel != 0) || (tn = "")  ; If no result from InputBox
        return
    wbk := ComObjGet(A_Desktop "\Order List.xls")
    colB := wbk.Sheets(1).Range("B:B")  ; colB now contains a Range object. The Range contains all cells in column B.
    foundCell := colB.Find(tn, colB.Cells(colB.Cells.Count))  ; Find. Start at first cell in column.
    nn := foundCell.Row
    valA := foundCell.Offset(0, -1).Text
    valB := foundCell.Text
    valC := foundCell.Offset(0,  1).Text
    valD := foundCell.Offset(0,  2).Text
    wbk.Close()
    
    ; Clear variables containing objects. (Watch your Task Manager; if 
    ; these objects aren't freed, the EXCEL.EXE process stays open.)
    wbk := "", colB := "", foundCell := ""  
    MsgBox, % nn "`n" valA "`n" valB "`n" valC "`n" valD  ; Results
return

Reference:

• MS Office COM Basics (Tutorial)
• Range.Find Method
• Find value in Range, Sheet or Sheets with VBA
• Range.Offset Property

HTH

[dharmang1910]

Hi

Try this:

Code: Select all

F10::
    InputBox, tn, Token to find, please enter the Token No. to find Row No.
    if (ErrorLevel != 0) || (tn = "")  ; If no result from InputBox
        return
    wbk := ComObjGet(A_Desktop "\Order List.xls")
    colB := wbk.Sheets(1).Range("B:B")  ; colB now contains a Range object. The Range contains all cells in column B.
    foundCell := colB.Find(tn, colB.Cells(colB.Cells.Count))  ; Find. Start at first cell in column.
    nn := foundCell.Row
    valA := foundCell.Offset(0, -1).Text
    valB := foundCell.Text
    valC := foundCell.Offset(0,  1).Text
    valD := foundCell.Offset(0,  2).Text
    wbk.Close()
    
    ; Clear variables containing objects. (Watch your Task Manager; if 
    ; these objects aren't freed, the EXCEL.EXE process stays open.)
    wbk := "", colB := "", foundCell := ""  
    MsgBox, % nn "`n" valA "`n" valB "`n" valC "`n" valD  ; Results
return

Reference:

• MS Office COM Basics (Tutorial)
• Range.Find Method
• Find value in Range, Sheet or Sheets with VBA
• Range.Offset Property

HTH

Hello Kon,
I have tried your script and worked perfectly. You have described every important steps very carefully. This have helped me a lot and now i can complete my script.
Thank you very much for your nice help.

[dharmang1910]

Hello kon,
I have faced a different problem. If token number is of one digit or two digit, then this script select other token number contain same one digit or two digit number.
Is there any possibility that made this search case sensitive..?? And i also want to know that is excel vba or other language are working with AHK.?

[kon]

Have a look at the 'range.find' link I posted above.
expression .Find(What, After, LookIn, LookAt, SearchOrder, SearchDirection, MatchCase, MatchByte, SearchFormat)
You can use the 'LookAt' to match all or part of a value. And use 'MatchCase' to make the search case sensitive.

And i also want to know that is excel vba or other language are working with AHK.?

Not sure what you mean here. Maybe have a look at the 'MS Office COM Basics' link I provided above.

[Tup]

Hello Kon and dharmang 1910,
I need to do about the same thing, only in LibreOffice-Calc, not in Excel.
I am very interested in the Find-function. I hope Calc has the same option (through VBA)
I think it's wiser to start a fresh thread, I'll give it about the same subject-title

[dharmang1910]

Have a look at the 'range.find' link I posted above.
expression .Find(What, After, LookIn, LookAt, SearchOrder, SearchDirection, MatchCase, MatchByte, SearchFormat)
You can use the 'LookAt' to match all or part of a value. And use 'MatchCase' to make the search case sensitive.

And i also want to know that is excel vba or other language are working with AHK.?

Not sure what you mean here. Maybe have a look at the 'MS Office COM Basics' link I provided above.

Thank you kon for your reply. I have modified find method as per your suggestion but i got error i think there is some expression error. Please correct it

Code: Select all

foundCell := colB.Find(tn, colB.Cells(colB.Cells.Count), , LookAt := xlWhole)

[dharmang1910]

Hello Kon and dharmang 1910,
I need to do about the same thing, only in LibreOffice-Calc, not in Excel.
I am very interested in the Find-function. I hope Calc has the same option (through VBA)
I think it's wiser to start a fresh thread, I'll give it about the same subject-title

I don't think libre office have native vba support, it has different basic language which is identical to visual basic.

[kon]

AHK does not know xlWhole.
Try:

Code: Select all

foundCell := colB.Find(tn, colB.Cells(colB.Cells.Count), , 1)  ; xlWhole = 1

[dharmang1910]

AHK does not know xlWhole.
Try:

Code: Select all

foundCell := colB.Find(tn, colB.Cells(colB.Cells.Count), , 1)  ; xlWhole = 1

Thanx kon , i will try . Where can i find this type of changes for ahk like you have suggest me to use 1 instead of xlwhole? And if want to use match case option in this, what is appropriate expression for it.?
Sorry for such questions but i can not figure out how to use ahk commands and excel vba together.

[kon]

No problem

If you are not already familiar with AHK Expressions and Objects, as mentioned in section "1 Introduction" of the MS Office COM Basics tutorial, you should read about them in the AHK help file. That provides a good foundation.

There are also some tricks needed to "translate" from one language to another. Sections "5 Miscellaneous" and "6 Links" are particularly relevant. Searching online, as mentioned in section 5.6, is always an option if you get stuck.

For MatchCase:

Code: Select all

foundCell := colB.Find(tn, colB.Cells(colB.Cells.Count),, 1,,, -1)  ; xlWhole = 1, true = -1, false = 0