Excel Com & loop search

Get help with using AutoHotkey and its commands and hotkeys
Mike__

Excel Com & loop search

24 Jul 2018, 15:03

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
Posts: 25
Joined: 20 Mar 2018, 19:33

Re: Excel Com & loop search

25 Jul 2018, 14:41

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
Posts: 1361
Joined: 30 Sep 2013, 22:25

Re: Excel Com & loop search

25 Jul 2018, 16:22

Mike__ wrote: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
Hotkey Help - Help Dialog for Currently Running AHK Scripts

AHK Startup - Consolidate Multiply AHK Scripts with one Tray Icon

[Function] Timer - Create and Manage Timers
Mike__
Posts: 11
Joined: 25 Jul 2018, 14:36

Re: Excel Com & loop search

25 Jul 2018, 20:58

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
Posts: 1361
Joined: 30 Sep 2013, 22:25

Re: Excel Com & loop search

26 Jul 2018, 14:56

Mike__ wrote:

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
Hotkey Help - Help Dialog for Currently Running AHK Scripts

AHK Startup - Consolidate Multiply AHK Scripts with one Tray Icon

[Function] Timer - Create and Manage Timers

Return to “Ask For Help”

Who is online

Users browsing this forum: flyingDman, Google [Bot], Gully, RickC and 54 guests