04-28-2015 02:38 PM
One of my colleagues has built an effective and good-looking monthly report with graphs, dashboard, etc... in Excel. I learned how to update the data each month using PROC SQL, DATA steps, and the SAS EXCEL libname engine. I found all that I needed to figure it out here: http://www2.sas.com/proceedings/sugi31/024-31.pdf. The only trouble is that there are over 500 customers that each get a report that is representative of their data, customers are always leaving or coming on board, and that I have to have a separate template file for each of these customers that must be updated.I have a macro that will be running one iteration per each customer for however many customers there are. I would like to know of a way to have just one MASTER template file (.XLSM) that can be copied/cloned and renamed at the beginning of a macro with the customer's name in the new file name. As of right now, I have over 500 of the same files with different names for each customer so that the EXCEL engine can be used to replace each file's old data with the new data. This is really inefficient. There's got to be a way of just renaming a file, any file really (even an XLSM), without overwriting the original file name. Any Help would be greatly appreciated!