Help on how to generate output as excel file

[ballardw]

If you don't have an example of the second file then you may spend a lot of effort matching things up unless there is an actual Excel function that will report on which cells have the links. (Not available the last time I was working on such a thing).

It may be worth going to the users and asking what additions or changes they might like to this report result and use that as an opportunity to update the whole process. The whole links between documents that may be renamed, deleted, not copied with a project makes this approach difficult to maintain, as you have discovered. Since you deliver a PDF to the end users you might be able to create that directly using ODS PDF.

[lola]

Correct, I don't have that second file and not sure what it should be/look like...got it.

Agree with going to users, have made numerous changes already, and since I have to redo 100 tables, thought I would stop and take the time to more efficiently do this part, while I wait for the data to run yet again (g!). You're right also about this whole archaic linking stuff. But pdf is only half of the solution, need to publish an excel file too.

Thanks so much for this informed reply!

[RW9]

Blimey this post was a way back ;O)

What it sounds like your problem is, and its something facing everyone, is the use of Excel for purposes it was never really intended.  I.e. it is not a data transfer format, nor a database, nor a reporting utility, or a data entry utility, or a programming environment - all of which I have seen in the past.  Specific software is designed to do specific tasks.  So and Oracle database is designed to do databasing, JReview, SAS Web Report Studio are designed to provide review outputs.

Now you have inherited an Excel file, but are missing parts.  Off the bat you are heading into problems.  Me, I would go back and ask the customer what they want, write down a functional design specification and follow Software Development Lifecycle principals.  If they have to have Excel, and most higher levels need many Excel files to make their existence feel worthwhile, then decide on what technology you will use.  ODS tagsets.excelxp is pretty easy to use, and can produce nice formatted reports, but lacks a lot of the functionality e.g. linking.  Libname to Excel I personally haven't used much.  DDE whilst still works, is old and may not be supported.  But you are not limited there, you could write VBA macros witihin your Excel template file, or in a separate one, and write the code in there to pull the data from saved CSV files into your Excel file.  So rather than pushing from SAS, pull from Excel.  In SAS you would only need to export the data to CSV, all other processing is done in VBA.  Or maybe you can convince the customer that having some sort of web report setup is a good idea, they wouldn't be limited to what is designed then, but then there is a cost and resource.  Etc. many options, but you really have to start with "what is it you, as a customer, want" question and build specs out from there.

[lola]

RW9, agree. I understand your analysis and support your conclusion. I continue to find myself in a reactive mode to what is here and now, with no time for proactive data management. Its seems to fall to too few resources continually. I readily admit I will have to finish this annual project with this tool, but in the same breadth must find a long-term solution for next years data which will begin immediately following this deliverable. Thank you for your insight!

[Kurt_Bremser]

I strongly second 's opinion. When confronted with a mess like this, go back to square one and define the requirements (or have them defined). From that, select the right toolset to solve the issue.

Questions for the customer:

- what insight do you want to gain?

- who will need to have access to the report/data/analysis?

- what tools are available to those persons?

Very often I have solved the "I want an Excel like that" request by supplying a simple HTML page that's updated daily/weekly/monthly and posted on the data warehouse's internal web server. Because they wouldn't do anything but attach the .xls to a mail and send it to several people.

[lola]

Kurt, I like the HTML page idea you've utilized and believe I need to continue to explore that and similar data presentation/delivery methods. Our challenges are even larger than that, as a federal data provider/manager, data is plentiful and too many reports produced because they have always been produced and there are not enough resources to do the job much less to make it efficient, but change happens once piece at a time...

Thanks for replying and providing more thought for my challenge.