[prev in list] [next in list] [prev in thread] [next in thread] 

List:       sas-l
Subject:    Re: Convert Excel file created with tagsets.excelxp to xls/xlsx
From:       Savian <savian.net () GMAIL ! COM>
Date:       2009-10-31 2:59:33
Message-ID: 946ed6f3-1def-4217-bbc4-1d04c0dacf74 () r24g2000prf ! googlegroups ! com
[Download RAW message or body]

On Oct 30, 3:02 am, Alex <alexander.k...@iea-dpc.de> wrote:
> On Oct 29, 2:19 am, j...@STANFORDALUMNI.ORG (Jack Hamilton) wrote:
>
>
>
>
>
> > Why not do it in Excel?  It's simple to write a VBA routine that
> > converts all the XML files in a directory into XLS format.
>
> > --
> > Jack Hamilton
> > j...@alumni.stanford.org
> > Caelum non animum mutant qui trans mare currunt.
>
> > On Oct 27, 2009, at 2:14 am, Alex wrote:
>
> > > Hi all,
> > > I have xml files created with ODS tagsets.excelxp and would like to
> > > convert them from plain xml to xls and/or xlsx. I can open them in
> > > Excel and Save as..., but need to do it automatically from a SAS
> > > program. The sheets in the files are not just one rectangular data set
> > > each, but two or more data sets with different columns. Also the
> > > sheets contain a lot of formatting. So just importing the xml and
> > > exporting toxls/xlsxwouldn't work, I guess.
> > > Any ideas?
> > > Thanks,
> > > Alex
>
> Thanks Alan and Jack!
> While I had almost begun to write a C# program as Alan suggested, a
> colleague threw this DDE solution at me. Works fine and is easier for
> me to implement as a .Net app or an Excel macro:
>
> %* open the xml in Excel ;
> x "D:\temp\myfile.xml";
>
> * give Excel 5 seconds to start;
> data _null_;
>      x=sleep(5);
> run;
>
> %* submit dde commands for saving as regular xls and closing the file;
> filename xml2xls dde 'Excel|system';
> data _null_;
>      file xml2xls;
>      __DDE_CMD = '[SAVE.AS("'||"D:\temp\myfile.xls"||'", 1)]';
>      put __DDE_CMD ;
>      put '[CLOSE]';
> run;- Hide quoted text -
>
> - Show quoted text -

Certainly you can use DDE but perhaps you shouldn't. Oftentimes we
feel pressured to just make it work and move on to the next thing but
an alternative codebase would probably suit you much better for future
needs than DDE.

Use DDE if you have to but consider doing it using a .NET library.
SaviCellsPro, for example, does not require Excel to even be on the
machine and does not use Excel to create the workbooks.

There are so many issues with COM on Excel that it should cause a
feeling of pause whenever it is considered.

Alan
[prev in list] [next in list] [prev in thread] [next in thread]