topic Re: Deleting formats in an Excel worksheet. in Microsoft Integration with SAS

Deleting formats in an Excel worksheet.

deleted_user — Fri, 20 Mar 2009 10:30:21 GMT

I want to transfer values, with DDE, from a SAS table to a formatted Excel worksheet. I want to do some new formatting with code.

My values are transferred, but the format doesn't change.

Shouldn't the existing format be overwritten with the new format I am coding, or do I first have to use DELETE.FORMAT?

Current area: A1:E20.

My formatting code:

put '[select("r1c1:r20c5")]';
put '[alignment(4)]'; /* Meant to be right justified. */
put '[format.number("#,##0")]';

I also tried with put '[delete.format]';, placed before the second code line above. I didn't work either.

Any suggestions?

Susan

Re: Deleting formats in an Excel worksheet.

data_null__ — Fri, 20 Mar 2009 11:16:14 GMT

I copied your statements to my "write with DDE and do formatting" test program and it work as I expected. If my numbers are less that 1000 nothing looks different except there are no decimal places printed.

You can confirm the function by examination of the menu Format Cells>Number

I don't think delete.format does what you what. If you want to remove formatting from cell(s) use CLEAR(2)

Re: Deleting formats in an Excel worksheet.

deleted_user — Fri, 20 Mar 2009 13:12:46 GMT

With put '[format.number("#,##0")]'; I was expecting to have the value 12798 shown as 12 798. Instead I got 12798,0. (I'm living in Europe, I want an empty space between 2 and 7.)

If I do it manually in an Excel sheet and record a macro, then the macro text contains the format "#,##0".

And the alignment(4) didn't have any effect. I can't understand what's wrong!

Another problem: If I have transferred 0 (zero) to cells that I want empty( I have reasons to do like that) and then want blank signs in those cells I tried with

put '[select("r1c1:r1c5")]';
put ' ' ;

It didn't work. How can it be done?

In an another, earlier answer you had some code with "link command" twice, which was for starting Excel. It shouldn't be necessary to start Excel more than once, or have missed something fundamental?

Susan

Re: Deleting formats in an Excel worksheet.

data_null__ — Fri, 20 Mar 2009 13:39:38 GMT

> With put '[format.number("#,##0")]'; I was
> expecting to have the value 12798 shown as
> 12 798. Instead I got 12798,0. (I'm
> living in Europe, I want an empty space between 2 and
> 7.)

I used this to produce the format you want.
put '[format.number("# ##0")]';