Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Update a ListObject from a another Excel sheet
I need a bit of code please to update a ListObject from a secondary
Excel data source. The Target (the ListObject) stucture is: WORKBOOK: SPQuery.xls SHEET: "CAPGCFXT" LISTOBJECT:"List1" A B C D E ID SCR Descr From Date 1 22136 Demand Mike 4/29/2005 2 21517 Nortel Oliv 4/4/2005 3 15654 Douwee Tony 9/18/2006 The Source structure is: WORKBOOK: PCVSExtract.xls SHEET: "Sheet1" A B C D SCR Descr From Date 23564 Anonan Beth 9/12/2006 22136 Demand John 4/29/2005 21517 Nortel Oliv 4/4/2005 What I need it to do is: - Remove list items from the target if they are not in the source - Add new list items to the Target from the source - Update cell values in the Target based on the source (if necessary) Using the above example the following would happen in the target: - Row 4 (15654) would be removed from the target Presumably using some code like: ListObjects("List1").ListRows(3).Delete - A new row would be added based on the contents of Row 2 (23564) Presumably using some code like: ListObjects("List1").ListRows.Add - Row 1 (22136) would see the value in column D updated from "Mike" to "John" Some pointers: - The lists are not sorted to begin with. - The ID Column is read-only since it's a ListObject - The SCR column contains unique record identifiers - There is a total of 13 columns to deal with excluding the ID column - The order of the columns in the source and target are the same (barring the absence of an ID column in the Target) In the above example the following would happen in the target: - Row 4 (15654) would be removed from the target Presumably using some code like: ListObjects("List1").ListRows(3).Delete - A new row would be added based on the contents of Row 2 (23564) Presumably using some code like: ListObjects("List1").ListRows.Add - Row 1 (22136) would see the value in column D updated from "Mike" to "John" Thanks a bunch! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to update an excel sheet dynamically from another excel sheet? | Excel Discussion (Misc queries) | |||
Excel 2003 ListObject question | Excel Programming | |||
ListObject in Excel 2000 ? | Excel Discussion (Misc queries) | |||
reference vsto Excel ListObject in Vb.Net 2005 project/app | Excel Programming | |||
saving excel sheet from ASP update | Excel Programming |