Excel 2007 (xlsx) and data range problem

[nmduarte]

Hello,

I am trying to export my data to xlsx file.

Since the data has more than 65k records, and I just want one excel sheet, I have to use this format.

I have a fancy excel template where I would like to put my SAS data using libname engine. This template also has a range defined on it, so SAS can know where to put the data.

So, using the same template as an xls file and then using the xlsx template,I will have different results.

In the first case(xls), everything goes as is should: my first row(the fancy headers) still there, and the data goes exactly where I want it to go.

In the second case(xlsx), it seems that SAS ignores the range, since it starts writing the data in the first line of the excel (and erases my fancy headers).

code:

libname excLib excel 'd:\test.xlsx';

proc datasets lib=excLib nolist;

    delete dataRange;

run;

data excLib.dataRange;

    set myData;

run;

libname excLib clear;

***************************

So, if I use the same code and save test.xlsx as test.xls it will work as I want.

I have searched for a explanation for this, but I can't find it. Can anyone help me, please?

Using SAS 9.2.

I must add that this only happens when I am running the code on the server (where I do not have excel installed). If I run it on my machine, where I have excel installed, it will work for both xls and xlsx.

Thank you

[DukAp]

I am having the exact same problem, which is really annoying, since I have a lot of ready reports in xlsx format and I cannot import any data into them at the proper ranges. I also run my code on a SAS 9.2 Server

Is there any solution out there for this?

Thanks