topic Re: Using DDE to open and close xlsX - disable compatibility pop up in SAS Procedures

Using DDE to open and close xlsX - disable compatibility pop up

andrewjmdata — Fri, 12 Jun 2015 09:54:42 GMT

Hi, Wrote some code that opens and xlsx and saves as xls as our version of sas doesn't support "new" versions of Excel.

Only issue I have is that a pop up appears asking user to confirm compatibility issues.

Is there a way to suppress this so the code can run without user having to click a button?

Thanks

Andy

Re: Using DDE to open and close xlsX - disable compatibility pop up

RW9 — Fri, 12 Jun 2015 12:04:21 GMT

Hi,

No, that would be an Excel "feature". I would recommend not using DDE, it is very old now and doesn't support any functionality since early Excel versions. Is there a reason you cannot write out to tagset.excelxp? That creates XML which Excel then interprets, gives most functionality. Also, I always recommend using a tool other than Excel anyways. Whatever task it is your doing will be far better suited using tools designed for the task.

Re: Using DDE to open and close xlsX - disable compatibility pop up

andrewjmdata — Fri, 12 Jun 2015 12:22:59 GMT

Hi Thanks for the reply, I'll look into tagset.excelp, can you suggest an good info sources?

My aim is to get xlsx files into sas. The data is sent as xlsx, I have no control of it.

I take your point on the excel "feature". A pain.

Re: Using DDE to open and close xlsX - disable compatibility pop up

ChrisHemedinger — Fri, 12 Jun 2015 12:42:13 GMT

If you have SAS/ACCESS to PC Files, look at these techniques:

PROC IMPORT with DBMS=XLSX (SAS 9.3 and later)
LIBNAME XLSX to read your XLSX file just like a collection of data sets (SAS 9.4)

If you have an older version of SAS that doesn't support XLSX directly, you could try using ODBC as described in this SAS note. This requires SAS/ACCESS to ODBC.

Chris

Re: Using DDE to open and close xlsX - disable compatibility pop up

andrewjmdata — Fri, 12 Jun 2015 12:46:28 GMT

We have sas 9.1 I'll have a look at the ODBC route.

Thanks

Re: Using DDE to open and close xlsX - disable compatibility pop up

LinusH — Fri, 12 Jun 2015 12:54:58 GMT

"our version of sas" - consider upgrading...

Re: Using DDE to open and close xlsX - disable compatibility pop up

andrewjmdata — Fri, 12 Jun 2015 13:02:33 GMT

If only, I am a small contractor minnow in a sea of very large UK bank:smileymischief:

Re: Using DDE to open and close xlsX - disable compatibility pop up

RW9 — Fri, 12 Jun 2015 13:06:41 GMT

Now I am a bit confused, are you just reading the file in? If so then convert the file to CSV and use a datastep import - full control that way. Proc import which has been suggested is just a guessing tool, you will have problems. If the data is sent as XLSX, then that is your real problem. Any proper data user will not send data as XLSX, seriously if that is their only pplication, use someone else, but even then, saving the file as CSV rather than XLSX would be preferable. ODBC is an option, the thing you will face, and this is the case with anything in relation to Excel is that Excel is *not* a database or data transfer format. I.e. the data is unstructured. Each cell has its own format/attributes, this view doesn't correlate to data processing/basing techinques. You can of course work around this slightly by having and import document which states columns/datatypes etc. Then if the file doesn't match them its not your problem.

Re: Using DDE to open and close xlsX - disable compatibility pop up

andrewjmdata — Fri, 12 Jun 2015 13:12:45 GMT

I have to work with what I have ie xlsx. Saving to CSV misses the point. I want the whole process to be as seamless as possible.

Am pretty sure the ODBC route will work, that should get me data straight into SAS.

Thanks

Re: Using DDE to open and close xlsX - disable compatibility pop up

ChrisHemedinger — Fri, 12 Jun 2015 13:20:13 GMT

I can sympathize. We can't always control the format of the data feeds we consume. We have a couple of sayings in the US related to being a small cog in a large machine. One is about "the tail wagging the dog". And the second is "some days you're the bug, and some days you're the windshield."

Re: Using DDE to open and close xlsX - disable compatibility pop up

andrewjmdata — Fri, 12 Jun 2015 14:02:17 GMT

This works perfectly... (http://support.sas.com/kb/32/455.html)

libname boo oledb init_string="Provider=Microsoft.ACE.OLEDB.12.0; data source=C:\excelfiles\air.xlsx; extended Properties=Excel 12.0";

data new;

set boo.'air3'n;

run;

Thanks folks!