DDE Excel Formats 1

[andrew1972]

I am exporting a data set across several worksheets on an Excel spreadsheet using DDE.

The problem I have is that the format on some fields is incorrect, i.e. an account number coming out as a date, therefore I have to manually format the worksheets after it has been created.

Is there a way of ensuring the format stays the same or forcing the format in the export code.

The code I am running is below.

Thanks, Andrew


%MACRO OPEX();
FILENAME TEST DDE "EXCEL|SYSTEM" NOTAB;
DATA _NULL_;
FILE TEST;
RUN;
%IF &SYSERR NE 0 %THEN %DO;
OPTIONS NOXWAIT NOXSYNC;
X '"C:\Program Files\Microsoft Office\Office\EXCEL.EXE"';
DATA _NULL_;
X = SLEEP(10);
RUN;
%END;
%MEND;
%OPEX;

FILENAME TEST DDE "EXCEL|SYSTEM" NOTAB;
DATA _NULL_;
FILE TEST;
PUT '[open("S:\CH\DBMktg\_Data Management\Database Loads\Mart Reconciliation\template")]';
X = SLEEP(5);
RUN;

OPTIONS MPRINT;
%MACRO WR2EX(SHEET = );

FILENAME TEMP DDE "EXCEL|[TEMPLATE.XLS]&SHEET.!R3C2:R10000C200";
%IF "&SHEET"="HIST" %THEN %DO;
FILENAME HEAD DDE "EXCEL|[TEMPLATE.XLS]&SHEET.!R2C6:R2C200";
DATA _NULL_;
SET ACCUM;
FILE TEMP NOTAB;
PUT GROUP '09'X VAR '09'X BANDS '09'X TYPE '09'X &VARS '09'X WEEK_DIFF_CT '09'X PERCENT;
RUN;
DATA _NULL_;
FILE HEAD NOTAB;
PUT "&COLNOW" '09'x "07/04/2005";
RUN;
%END;

%ELSE %DO;
FILENAME HEAD DDE "EXCEL|[TEMPLATE.XLS]&SHEET.!R2C4:R2C200";
DATA _NULL_;
SET %IF "&SHEET" ="TOTAL" %THEN %DO; SUM;%END;
%ELSE %IF "&SHEET"="ACC_UK" %THEN %DO;SUM_UK;%END;
%ELSE %DO; ACCUM(WHERE=(GROUP="&SHEET"));%END;
FILE TEMP NOTAB;
PUT VAR '09'X BANDS '09'X &COLPRE '09'X &COLNOW '09'X WEEK_DIFF_CT '09'X PERCENT;
RUN;
DATA _NULL_;
FILE HEAD NOTAB;
PUT "&COLPRE" '09'X "&COLNOW";
RUN;
%END;
%MEND;
%WR2EX(SHEET = IRELAND);
%WR2EX(SHEET = HIST);
%WR2EX(SHEET = TOTAL);
%WR2EX(SHEET = Abbey);
%WR2EX(SHEET = Virgin);
%WR2EX(SHEET = Direct);
%WR2EX(SHEET = A %str(&) L);
%WR2EX(SHEET = ACC_UK);

 

[ChrisW75]

The problem is most likely due to Excel. There have long been problems with putting data into Excel spreadsheets, because Excel looks at the data and interprets it as it sees fit, forcing it to accept a specific format is going to be tricky.
One option is to try putting quotes around the data you are outputting, this should force excel to display all data exactly as it is. This may cause difficulties with numeric fields if you plan to do some more processing of the data in Excel.
Hopefully someone else will be able to give you a better answer, I'v enot actually tried doing this using DDE.

 

[andrew1972]

Thanks Chris, I will have a go at that