DDE Excel refresh datasource

Reply
Contributor
Posts: 21

DDE Excel refresh datasource

Hallo,

What I am trying to do is to refresh a datasource in an excel workbook. It is no problem to refresh a pivottable. But now I try to refresh a datasource that queries a view in an oracle database. Result is a table in my excel sheet. I found in an excel 4.0 macro documentation the function query.refresh(). Seems to be the right function to do this. But when I try to call the function the result is an "DDE Session not ready error".

DATA _null_;

    file ddecmd;

    PUT '[workbook.activate("Quelldaten Triangel")]';

    PUT '[select("z2s1")]';

    PUT '[QUERY.REFRESH("z2s1")]';

    *PUT '[pivot.refresh()]'; 

RUN;


This is what the documention says:


Is there maybe another way to refresh the datasource or rather my table? I know that this would be easy in VBA but I should avoid using VBA.

I hope someone could help me.

Christoph

Super Contributor
Posts: 333

Re: DDE Excel refresh datasource

What version of excel are you working with? I have seen syntax changes from the old excel 4.0 help so you could be running into this if you are using newer version.

EJ

Contributor
Posts: 21

Re: DDE Excel refresh datasource

Excel 2007. But now I am confused. I thought it is just possible to use excel 4.0 syntax.

Christoph

Super Contributor
Posts: 333

Re: DDE Excel refresh datasource

Well there is two issues going on here. First the 4.0 language is old and has not been updated completely with new version, and in my experience gets worse with every new version of excel. DDE is also an old concept, while (I dont think at least) not officially deprecated by Microsoft, it kinda of falls in the same line as the excel macro language. They tend to want to push people to VB and .net for scripting.

Reference for the history of macro programming in excel:

Visual Basic and the Excel Version 4.0 Macro Language

None of this really helps with your problem. had the same thought I did below. But I suspect you dont want to deal with the macro security or having to save it as a .xlsm file. I believe there might be an option in the workbook options to have excel refresh the external data automatically each time it opens. Which may or may not be a good solution depending on your situation.

Are you pulling the data to use in sas or just using sas to update the data?

EJ

Contributor
Posts: 21

Re: DDE Excel refresh datasource

But I suspect you dont want to deal with the macro security or having to save it as a .xlsm file. I believe there might be an option in the workbook options to have excel refresh the external data automatically each time it opens. Which may or may not be a good solution depending on your situation.

That's exactly why it would be a nice solution to refresh the datasource with a DDE call.

Are you pulling the data to use in sas or just using sas to update the data?

It is a part of a SAS program that refreshes some reports. So it is just to update the data.

An alternative is to select the data in SAS and output them in my excel sheet. But I hoped that there is a more convient way.

Christoph

Super Contributor
Posts: 644

Re: DDE Excel refresh datasource

DDE is still, I think, the technology behind Microsoft Copy and Paste functions between applications or documents within Windows applications. 

Way back in the early days of Windows SAS I saw an application where the user opened a report and then clicked a button to start SAS to update the report.  I'm sure this could still be done, but it would require Excel macros and is probably not a robust solution.

One alternative worth a look is to investigate the SAS add-in for MS Windows, which might provide the kind of functionality you want without using technology that is long in the tooth.

Another alternative you might like to consider is a 3 stage solution:

  1. A SAS job scheduled to run once each time whenever the data is required to be updated (nightly, weekly, whenever, or triggered by the source)
  2. The SAS job exports to a 'holding pen' data store - a vanilla Excel workbook, or tab delimited file etc.
  3. The Report workbook is linked to the 'holding pen' source and gives the option to update when it is opened.

This sequence is likely to make fewer demands on resources and be simpler to maintain.


Richard

Super User
Posts: 17,864

Re: DDE Excel refresh datasource

A round about way is to embed a macro in the worksheet that refreshes the data source and then use DDE to open the workbook, run the macro and save the workbook. 

Ask a Question
Discussion stats
  • 6 replies
  • 1226 views
  • 0 likes
  • 4 in conversation