Remove auto pagebreak ODS excel

[polpel]

Hello,

I'm facing a problem and haven't found a single solution after many hours of intense research...

Basically I've got data that I'm exporting to an .xls file which I later on use in another VBA tool. The thing is the VBA tool parses my file according to line numbers from my XLS file. When I PROC PRINT, there is constantly an auto pagebreak after each table that is created, which is translated by an empty line in Excel, which creates errors in my "parser".

I've heard about an option to remove these pagebreaks in ODS HTML but it doesn't work in ODS EXCEL...

Help very very much needed...

Regards

[ballardw]

XLS? Really?

Or what the VBA is doing with the output in general.

You should share the code you are using from SAS to create the output. That way we might be able to provide some hints but without some idea of the options used to create said files we are flying pretty blind.

Note that any approach that involves "lying" to your computer, such as naming a file with an extension like "xls" when the file is actually something else such as "XML" or "html" is almost always going to create interesting interactions with programs expecting the correct structure.

[polpel]

ODS EXCEL file = "path/to/file.xls";
ODS EXCEL options (sheet_name="data_sheet" sheet_interval="none" index="yes" flow="tables");
ODS SELECT ALL;

DATA test_data;
 input name $ age weight;
 cards;
peter 12 56
jack 14 49
paul 15 84
;
RUN;

ods text="***tab1_open";
proc print data=test_data noobs;
QUIT;
ods text="***tab1_close";

here's some code you could use. It's not very special. But if you execute it and export the result to SAS you'll realise that there's an empty line between the last observation of the table and the "***tab1_close" line. That's what I'm trying to get rid of.

[ballardw]

I'm pretty sure that is mostly intentional from the SAS developers so that output tables can't be accidentally confused.

A custom ODS style might be possible but I haven't a clue what might work with Excel.

I would think it would be relatively easy to have a file parser ignore or skip blank lines but I haven't written any VB code more than 10 years and that was not with Excel.

[polpel]

Thing is I'm not allowed to change the VBA tool, I don't have the rights, I'm just creating the input file that is compatible with the tool. I would share it but I don't have the rights