Excel Com & loop search Topic is solved

[Mike__]

Hi!

I am not good with Excel com and it would be nice if someone is able to solve my problem.

I have 2 Excel files; 1 & 2

I need to search in Excel 1 in column A & O, Excel 2 Column A and paste value that match in Excel 2 cell column G.
If Excel 1 cell in column O is same value as Excel 2 cell in column A, copy cell in column A of Excel 1 and paste in Excel 2 column G.

IE: Excel 1, column O2 is 0341 and column A2 is 12. Excel 2 cell A25 is 0341 then paste 12 in column G25.

Row 1 is skip because it is the headers.

Thanks!

[maitresin]

Ok I have something like this but...
How to handle 2 excel file at same time?
How to make the Excel2value variable?
How to make the value found paste in the correct cells in column G of Excel2 file?

I did try but I am not enought confortable with the below Excel com.... any help please?

Code: Select all

   PathExcel1 = %A_Scriptdir%\Excel1.xlsx
 PathExcel2 = %A_Scriptdir%\Excel2.xlsx
 Excel2value := ; line per line what Excel2 file contain in the column A
 
    Xl := ComObjCreate("Excel.Application")
    Xl_Workbook := Xl.Workbooks.Open(PathExcel1) ; handle Excel1 file
    Xl.Sheets("Sheet1").Select
    x := xl.Range("O:O").Find(Excel2value).row  ; search the value of Excel2 range A:A
    found := xl.Range("B" x).text ; if value is found, store that value in found variable
    Xl.Range("G").Value:= Found ; paste variable found into Excel2 column G 
    Xl_Workbook.Save()
    Xl_Workbook.Close() 
    Xl.quit()

[FanaticGuru]

Hi!

I am not good with Excel com and it would be nice if someone is able to solve my problem.

I have 2 Excel files; 1 & 2

I need to search in Excel 1 in column A & O, Excel 2 Column A and paste value that match in Excel 2 cell column G.
If Excel 1 cell in column O is same value as Excel 2 cell in column A, copy cell in column A of Excel 1 and paste in Excel 2 column G.

IE: Excel 1, column O2 is 0341 and column A2 is 12. Excel 2 cell A25 is 0341 then paste 12 in column G25.

Row 1 is skip because it is the headers.

Thanks!

This is not customized to your exact columns but if demonstates the basic idea.

Code: Select all

Xl := ComObjCreate("Excel.Application")	; Open Excel
Xl.Visible := true		; Make Open Excel Visible
Wb1 := Xl.Workbooks.Open(A_Desktop "\Test\One.xlsx")	; Open Excel Workbook
Wb2 := Xl.Workbooks.Open(A_Desktop "\Test\Two.xlsx")	; Open Another Excel Workbook
MsgBox
LastCell := Wb1.ActiveSheet.Columns("A").Find("*",,,,,2)	; Find Last Cell Used in First Workbook
for Cell in Wb1.ActiveSheet.Range("A1", LastCell)	; Loop Through All Used Cells in Column "A" of First Workbook
	Wb2.ActiveSheet.Columns("A").Find(Cell.Value).OffSet(0,2).Value := Cell.OffSet(0,1).Value	; Find Cell in Column A and Put Value from Cell one to Right into Cell two to Right

FG

[Mike__]

Oh ok I got it

Thanks!

[Mike__]

Code: Select all

for Cell in Wb1.ActiveSheet.Range("A1", LastCell)	; Loop Through All Used Cells in Column "A" of First Workbook
	Wb2.ActiveSheet.Columns("A").Find(Cell.Value).OffSet(0,2).Value := Cell.OffSet(0,1).Value
	

how I can add the following to this code?

If cell.value not found, add the cell.value in A column ?? The cell value in Cell in Wb1.ActiveSheet.Range("A1", LastCell)

[FanaticGuru]

Code: Select all

for Cell in Wb1.ActiveSheet.Range("A1", LastCell)	; Loop Through All Used Cells in Column "A" of First Workbook
	Wb2.ActiveSheet.Columns("A").Find(Cell.Value).OffSet(0,2).Value := Cell.OffSet(0,1).Value
	

how I can add the following to this code?

If cell.value not found, add the cell.value in A column ?? The cell value in Cell in Wb1.ActiveSheet.Range("A1", LastCell)

Code: Select all

for Cell in Wb1.ActiveSheet.Range("A1", LastCell)
	if (Cell.OffSet(0,1).Value = "")
		Wb2.ActiveSheet.Columns("A").Find(Cell.Value).OffSet(0,2).Value := Cell.Value
	else
		Wb2.ActiveSheet.Columns("A").Find(Cell.Value).OffSet(0,2).Value := Cell.OffSet(0,1).Value

FG