I've dome this in the past:
1. Create an Excel file to be used as a template.
2. In a stored procedure, loop through the list of locations.
3. Inside the location loop, use xp_cmdshell to copy/rename the template file to the desired name.
4. Also inside the location loop, use xp_cmd shell to execute DTS package to export data to file you just created. Pass the filename in as a global variable.
5. In your DTS Package, you should start of with a Dynamic Properties task that changes the Excel connection object to the filename passed in as a global variable.
You may not need the template Excel file in your situation. It was helpful in mince becuase I could apply formatting in advance without a great deal of VBA programming.