update a excel sheet through another excel sheet

[aniketpathak.nift]

i have a excel file "stock update.xls"

And a "file stock.xls"...this file get updated every day

Now, i want to update each and every item of the "stock update.xls" file through "file stock.xls"

for this i want to create a macro which should open the stock.xls from its saved folder say..c:/temp/stock..

and to check the items within stock.xls and if there is any changes it should update the "stock update.xls" accordingly.

kindly help...

[mike7952]

If you can give us more details of both workbooks data structure it would help to get an answer. Ex: what column are we to match on? Ex: Is it a item number, item description? what column has the quantity to change?

[aniketpathak.nift]

well,
have to see the item code 1st column of stock.xls & to update the value i.e the quantity against each item in the "stock update.xls"

[mike7952]

Ok we now know the Item code to lookup/match is in column A, but now what column Is the quantity in? Also are both Item codes in In column A.
If you can upload both workbooks it would same you some time, and would help get an answer.

[aniketpathak.nift]

have attached the two files...
kindly help on the same..
thanks

[aniketpathak.nift]

the stock.xls file gets updated everyday...

[mike7952]

@aniketpathhak

None of the codes in stock update.xlsx are in stock.xlsx? What exactly are you wanting to due?

[aniketpathak.nift]

oops!! so sorry..
uploaded the same again with correction

[mike7952]

@aniketpathak.nift

Ok now I got the workbooks and see the are Item Numbers in each Workbook. Now Explain Exactly what is to happen. Looking at your first post its still not clear. What Workbook is to be updated? What Column is to be updated?

i have a excel file "stock update.xls"

And a "file stock.xls"...this file get updated every day

Now, i want to update each and every item of the "stock update.xls" file through "file stock.xls"

for this i want to create a macro which should open the stock.xls from its saved folder say..c:/temp/stock..

and to check the items within stock.xls and if there is any changes it should update the "stock update.xls" accordingly.

[aniketpathak.nift]

the "stock.xls" file has item code and L column has in hand value which has to be look and update in "stock update.xls" stock column(column E)...

Also the stock.xls gets update every day n gt saved in c:/temp/stock...the macro should open this file everyday and do the above check and should close it after

reading the values.

thanks for you help..

[mike7952]

@aniketpathak.nift,

Ok see if this will work for you. The UpdateStockData.xlsm Workbook will open and get the Item numbers and OnHand Amounts from stock.xlsx and then open and compare Item Numbers and STOCK in the stock update.xlsx, If OnHand <> STOCK then it will update with new value from stock.xlsx.

You FIRST will need to CHANGE the Const PATHS to your WORKBOOKS at the top of the Code before running updateStock Macro. After you change the paths click the button to test.


stock.xlsx
UpdateStockData.xlsm
stock update.xlsx

[aniketpathak.nift]

@mike
Thanks will revert if came across ne prob..
Thanks A ton

[aniketpathak.nift]

@Mike

The updation should be done in the stockupdate.xls only (macro should be assign within the stockupdate.xls only)

Inhand quantity from stock.xls should be updated in stock column in stockupdate.xls

Thanks

[aniketpathak.nift]

Attaching the two files

1-Product wise status.xls

2-Reco file.xls (which gets updated every day)

Have to update the New stock column of Product wise status.xls in sheets from 1-7

from reco file..column A (row label) is the codes which is there in Product wise status.xls.

The changes has to be done within Product wise status.xls.

As the size of reco file is large have deleted some of the rows...

Srry fr the inconvenience

thanks