topic Re: DDE commands for Excel in Microsoft Integration with SAS

DDE commands for Excel

GarryL — Thu, 13 Jul 2017 23:39:56 GMT

Hi Everybody,

I am looking for the commands (I think they are X4ML) for the DDE process in SAS.

Is there any documentation that contains this information? There was a link from another thread, but the link went down.

I am particularly looking for:

put '[<what.command()>]'; -----> to put rows 1-20 into a group. In other words, I want all records from row 1-20 in a collapsible group.

Thanks,

Garry

Re: DDE commands for Excel

Reeza — Thu, 13 Jul 2017 23:46:37 GMT

collapsible groups didn't exist when those commands were written (Excel 4.0) so I doubt you're going to find the command you want. I would probably try some of the VBA commands if you know that syntax. Not sure, haven't tried it, but am definitely curious if you get it to work 🙂

@GarryL wrote:

Hi Everybody,

I am looking for the commands (I think they are X4ML) for the DDE process in SAS.

Is there any documentation that contains this information? There was a link from another thread, but the link went down.

I am particularly looking for:

put '[<what.command()>]'; -----> to put rows 1-20 into a group. In other words, I want all records from row 1-20 in a collapsible group.

Thanks,

Garry

Re: DDE commands for Excel

art297 — Thu, 13 Jul 2017 23:46:20 GMT

Do a google search for: dde Koen Vyverman

Most things you want to know are somewhere in his papers.

You can download the exe file that actually contains all of the documentation at: https://support.microsoft.com/en-us/help/128185/macrofun.exe-file-available-on-online-services

Art, CEO, AnalystFinder.com

Re: DDE commands for Excel

Reeza — Thu, 13 Jul 2017 23:47:48 GMT

@art297 those files couldn't be installed on my Windows 7 machine without some additional workarounds this time. Had to switch modes or something. DDE is definitely going to be done soon...

Re: DDE commands for Excel

art297 — Fri, 14 Jul 2017 00:10:34 GMT

Should have been readable by Windows 7 or 8. However, if not (or you're on Window's 10), you can still read it by downloading the reader: https://appuals.com/how-to-read-hlp-files-in-windows-10/

Art, CEO, AnalystFinder.com

Re: DDE commands for Excel

RW9 — Fri, 14 Jul 2017 10:49:40 GMT

Do be aware that DDE is ancient, MS stopped supporting it well over a decade ago, hence none of the updates since 200x have been implemented. Its really not recommended to use this any further. There are manya ways to get data out to Excel if you must use the app, including tagsets - which create XML Excel can read, CSV, libname excel, proc export, dump data to CSV from SAS and use VBA to read it in, SAS Office AddIn etc. If you want collapsible groups and such very Excel specific things, then you would likely be better off createing a CSV from SAS, then processing the data into a template using VBA.

Re: DDE commands for Excel

GarryL — Sat, 15 Jul 2017 05:16:05 GMT

Thanks everybody for the input. It is all great information. What would be the most efficient way to output a SAS dataset into Excel and format it / group the common rows? I'm guessing I would have to use VBA, I'm trying to use as few tools as possible because documentation starts to get really complicated when there are multiple languages involved for a job.

Thanks in advanve everybody.

Garry

Re: DDE commands for Excel

RW9 — Mon, 17 Jul 2017 08:08:22 GMT

The problem is the use of Group Rollups in Excel, which is competely Excel specific, nothing else uses it. Therefore, to my mind, the best way to do it is via VBA. Record a macro of you doing this manually once: View->Macros->Record Macro. This will give you the code to do this, thn just add a line which opens a CSV, processes the data into the format required and saves out the file.

Re: DDE commands for Excel

itchyeyeballs — Tue, 15 Aug 2017 11:25:09 GMT

Assuming you dont have the Office Addin I'd try to do everything natively in Excel, you may be able to skip VBA altogether if you have access to a version of Excel with PowerPivot?

You can get SAS ODBC drivers from here - https://support.sas.com/software/products/odbc/

You could then use powerquery to set up an automated import into Excel and use Powerpivot to present the data from the data model

Re: DDE commands for Excel

Reeza — Tue, 15 Aug 2017 14:45:05 GMT

Use ODS Excel to create the majority of the file. It can't do the grouping yet but it will do formatting and/or custom highlighting as needed.