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!
Excel Com & loop search Topic is solved
Re: Excel Com & loop search Topic is solved
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?
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: 1908
- Joined: 30 Sep 2013, 22:25
Re: Excel Com & loop search
This is not customized to your exact columns but if demonstates the basic idea.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!
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
Hotkey Help - Help Dialog for Currently Running AHK Scripts
AHK Startup - Consolidate Multiply AHK Scripts with one Tray Icon
Hotstring Manager - Create and Manage Hotstrings
[Class] WinHook - Create Window Shell Hooks and Window Event Hooks
AHK Startup - Consolidate Multiply AHK Scripts with one Tray Icon
Hotstring Manager - Create and Manage Hotstrings
[Class] WinHook - Create Window Shell Hooks and Window Event Hooks
Re: Excel Com & loop search
Oh ok I got it
Thanks!
Thanks!
Re: Excel Com & loop search
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
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: 1908
- Joined: 30 Sep 2013, 22:25
Re: Excel Com & loop search
Mike__ wrote:how I can add the following to this code?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
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
Hotkey Help - Help Dialog for Currently Running AHK Scripts
AHK Startup - Consolidate Multiply AHK Scripts with one Tray Icon
Hotstring Manager - Create and Manage Hotstrings
[Class] WinHook - Create Window Shell Hooks and Window Event Hooks
AHK Startup - Consolidate Multiply AHK Scripts with one Tray Icon
Hotstring Manager - Create and Manage Hotstrings
[Class] WinHook - Create Window Shell Hooks and Window Event Hooks
Who is online
Users browsing this forum: chubbychub and 37 guests