Alternatively – the above from Eric@SAS (no disrespect intended) would mean setting up 1000 linked tables? - JohnH if you are using DDE, did you know that you can reproduce every excel function from DDE (bar one)? Thus starting from a new blank spreadsheet you can build your workbook from scratch with all the formatting that is required. The one function that does not work is renaming a worksheet (see below for my solution). And by all the formatting I mean fonts, text sizes, numeric field formats, underlining, bolds, you name it, you got it.
I had an exercise (not as big as yours), but one that involved manual formatting of data, which typically took around one day to complete. I had to use the group function on around 50 fields and 600 rows, grouping columns by year / quarter / month / day and the rows by a predetermined hierarchy, across 22 worksheets. OK it took me a week to set up the program, but then the program took an impressive 20 seconds to build the workbook – an obvious time saver for the future and its really impressive when running - reminds me of that robot in I Robot - what's his name? - drawing the picture.
I couldn't use a template which drew the data across as the columns were expanding to the right with time from a fixed point.
Look for DDE on SAS help online – there are lots of papers showing the techniques involved.
I got around the problem of renaming the worksheets creating 22 CSV files from SAS; opening a new blank workbook and using the move/copy command (DDE call from SAS), the move all the data into the workbook. The sheet comes across with the name of the CSV file (shortened to 32 chars). It is interesting and helpful that once you have moved the only sheet out of a file opened in Excel, Excel automatically closes the file without saving, and without annoying dialog question boxes either! You can turn the dialog box off as well with an error function call. If you’re interested in some sample code, I’ll only be able to provide tomorrow – I got it at home.
... View more
