Find cell, copy adjacent cell ComObj

[Trigg]

I want to convert old invoice numbers (in column A) to new ones with a MASSIVE range of rows. Then after I find the cell with the old invoice number the converted number is adjacent to it (column B).

Code: Select all

Claim := clipboard               ; Saving my clipboard as a variable

XL := ComObjGet("C:\Users\james\Desktop\Book1.xlsx")
XL.Sheets("AC to IC Claim Numbers").Select

XL.Range["A:A"].Find(Claim).Row := Claim                         ; I'm trying to activate "sheet2" and find my variable
; This line is getting an error ^

Conversion := cell.offset(0, 1).text

MsgBox %Conversion%

[kon]

Remove this line: XL.Sheets("AC to IC Claim Numbers").Select
Then:
MyCell := XL.Sheets("sheet2").Range("A:A").Find(Claim) You can use the exact sheet name, ex: .Sheets("AC to IC Claim Numbers") or .Sheets("sheet2"), or you can use the sheet number, ex: .Sheets(2)
and then
Conversion := MyCell.offset(0, 1).text


Edit:
Conversion := XL.Sheets("Sheet2").Range("A:A").Find(Claim).Offset(0, 1).Text

[Trigg]

Code: Select all

clipboard = %clipboard%       ; get rid of extra spaces
Claim := clipboard            ; converting clipboard as a var


XL := ComObjCreate("Excel.Application") 
XL.Workbooks.Open("C:\Users\James\Desktop\Conversion.xlsx") ;open an existing file
XL.Visible := True

MyCell := XL.Sheets(1).Range("A:A").Find(Claim)   ; Find the claim number

If ErrorLevel      ; if claim number is not found
	{
	XL.Quit()
	}
Else               ; if claim number is found
	{
	Conversion := MyCell.offset(0, 1).text          ; set adjacent cell as new variable

	clipboard = %Conversion%                            ; convert clipboard to new variable

	XL.Quit()
	}

I'm trying to figure out if the claim number has not been converted to quit excel. But my script is still converting the clipboard to my Conversion variable.

[IMEime]

I'm not sure though, is "ErrorLevel" correct one ? How about "MyCell" instead.

[kon]

Code: Select all

; Constants
xlValues := -4163
xlWhole := 1

; Remove spaces, tabs, carriage returns, and linefeeds from either end of the clipboard.
Claim := Trim(Clipboard, A_Space A_Tab "`r`n")

XL := ComObjCreate("Excel.Application")
XL.Workbooks.Open("C:\Users\James\Desktop\Conversion.xlsx") ;open an existing file
XL.Visible := True

; It's possible to specify more options for find. See https://msdn.microsoft.com/en-us/library/office/ff839746.aspx
; Also for the Find methods: LookIn, LookAt, SearchOrder, and MatchByte are saved each time you use it. From the docs: 
; "To avoid problems, set these arguments explicitly each time you use this method."
;                                   Find(What, After, LookIn, LookAt, SearchOrder, SearchDirection, MatchCase, MatchByte, SearchFormat)
MyCell := XL.Sheets(1).Range("A:A").Find(Claim,, xlValues, xlWhole)   ; Find the claim number
if (MyCell)
{
    Clipboard := MyCell.Offset(0, 1).Text
}
XL.Quit()
return

Untested, but try something like this.
Use the Excel object browser to search for the values of constants (like xlWhole ).
HTH

[Trigg]

I'm not sure though, is "ErrorLevel" correct one ? How about "MyCell" instead.

Code: Select all

clipboard = %clipboard%       ; get rid of extra spaces
Claim := clipboard            ; converting clipboard as a var


XL := ComObjGet("C:\Users\James\Desktop\Book1.xlsx") 

MyCell := XL.Sheets(2).Range("A:A").Find(Claim)
If (MyCell = "")
Return
Else
{
Conversion := MyCell.offset(0, 1).Copy()
}

This works. If MyCell var does not have value then the script ends. Thank you.

[IMEime]

Good

You could use just

Code: Select all

If myVar ; only this case

And If you want quit.

Code: Select all

myObj.Quit  ; also, it's my two step habit just for Excel. I do not know quit function in this case.
myObj =