Taking data from one spreadsheet and moving to another

I think this might be a little trickier than simply V lookup. Data arrives to me in an excel spreadsheet from a location within my organization. It includes the month, year and location name. I have a system which creates xml import spreadsheets (from directly within it). Within the system, I generate an xml import spreadsheet (with the location, month and year preselected). How can I get the data from the location xls I received into the xml spreasheet using macro/code? Is there any middleware software that can do this or functionality in excel to do this? Thanks for any help. Apologies for lack of clarity of the issue!

Can you provide an example of how you would make this xml spreadsheet from the xls file? Do you manually enter the data currently?