I have a text table with matching item codes that i use to lookup the values from spreadsheet. It might have only a few matching records in the spreadsheet. (eg 31901 & 31903)
I want it to loop thru the excel spreadsheet and only copy the rows to a new table. Basically i want to create a count sheet from the item codes that are in the text file.
A B C D E F
Item code - Loc - Lot - Stat - Qty - Ord
31900 - 33 - kj - A - 410 - 25
31901 - 34 - mt - A - 700 - 5
31902 - 35 - zs - A - 600 - 10
31903 - 36 - we - Q - 800 - 45
31904 - 37 - jh - A - 675 - 30
I have found some code but is not complete. I can see that it is finding the records but stuck on how to copy rows to new spreadsheet.
Code: Select all
List := "list.txt", FilePath := "Inventory Cycle-Count.xlsx"
fileread, list, %list%
StringReplace, list,list,`r`n,`,, All
Xl := ComObjCreate("Excel.Application") ;create a handle
Xl.Workbooks.Open(a_scriptdir "\" FilePath,0,0) ;open xl file invisible
lstrw := xl.Sheets(1).UsedRange.rows.count ;find last row of used range
xl.Sheets(1).range("D34:R"lstrw).copy ;copy first 2 columns.
loop, parse, clipboard,`n,`r
{
RC%a_index% := StrSplit(a_loopfield, A_Tab) ;...and put in array
item := RC%a_index%.1
if item in %list% ;if one of the items matches the list
BList .= RC%a_index%.1 "`n" ;save it to Blist var
}
sort, Blist ;sort the list (optional)
MsgBox, %Blist% ;show result
Clipboard := Blist "`r" Blist1