OK, I uninstalled Office 2003 and then it worked with data null's original code.

However, I run my SAS-L through Outlook Express (but my regular mail through Outlook- avoids out of the office messages), and somehow uninstalling Office 2003 changed my dictionary spell-check on Outlook Expess to only French!

Je peu parle français un souplement....

-Mary

----- Original Message ----- From: Mary To: data _null_, ; SAS-L@LISTSERV.UGA.EDU Sent: Friday, July 25, 2008 11:40 AM Subject: Re: Office automation, save all sheets as txt.

Yes, that did work! So I think the key is that if you have both Excel 2003 and 2007 loaded on your machine, then you may need to open Excel 2007 first and then run the below code. You can test that it's working on 2007 by creating a test file with more than 256 columns.

The original code should have worked if a machine ONLY has Excel 2007, but not 2003, loaded on it. If someone has that setup, could they please check that that's the case, in using: put 'Set objExcel = CreateObject("Excel.Application")'; does not need to have Excel 2007 open? The Excel application is supposed to default to the last one installed if there are multiple versions, but it wasn't in my case; not sure why.

Thanks so much, data null- this will be a great help, and is better than trying to use Proc Import to do it in that Proc Import evaluates the type of the fields, whereas Visual Basic does not.

Code below (needs to have Excel 2007 opened first, but not the file being exported).

-Mary

****

data pgm=work.sheets2txt; run;

data _null_ / pgm=work.sheets2txt; /* gather info */ length workbook txtout script filevar command $256; workbook = pathname('WORKBOOK'); txtout = pathname('TXTOUT'); script = catx('\',pathname('WORK'),'SHEETS2TXT.vbs'); filevar = script;

/* write the script */ file dummy1 filevar=filevar; put 'Const ' workbook=$quote256.; put 'Const ' txtout=$quote256.; put +3 'Set objExcel = GetObject(, "Excel.Application")'; put +3 'With objExcel'; put +3 '.Visible = True'; put +3 '.DisplayAlerts = False'; put +3 'Set objWorkbook = .Workbooks.Open(workbook)'; put +3 'i = 0'; put +3 'Set colSheets = .WorkSheets'; put +3 'For Each objSheet In colSheets'; put +6 'i = i + 1'; put +6 'WScript.echo i & " " & objsheet.name '; put +6 'objSheet.SaveAs txtout & "\" & objSheet.name & ".txt",21'; put +6 'Next'; put +3 '.Application.Quit'; put +3 'End With';

/* close the script file by opening another, not used */ filevar = catx('\',pathname('WORK'),'DUMMY.vbs');; file dummy1 filevar=filevar;

/* look at the script, not necessary but may be useful */ infile dummy2 filevar=script end=eof; do until(eof); input; putlog _infile_; end;

/* call the script */ command = catx(' ','cscript //NoLogo',quote(strip(script))); infile dummy3 pipe filevar=command end=eof; do until(eof); input; putlog _infile_; end; stop; run;

filename workbook "C:\Work_Activities\testfiles\test.xlsx"; filename txtout "C:\Work_Activities\testfiles\";

data pgm=work.sheets2txt; run;

filename workbook clear; filename txtout clear;

**** ----- Original Message ----- From: data _null_, To: Mary Sent: Friday, July 25, 2008 11:03 AM Subject: Re: Office automation, save all sheets as txt.

I don't know about that. This might be a work around that you could try....

Change the line with this statement...

Set objExcel = CreateObject("Excel.Application") to Set objExcel = GetObject(, "Excel.Application")

Then before you run the script start Excel2007. GetObject says use the application that is already is running. You will get error if excel is not running.

It might work.

On 7/25/08, Mary <mlhoward@avalon.net> wrote: > > It said to try to register Excel 2007 as the application. I tried it; it > did not change the result. Excel 2007 is my default already when I > double-click on an xls file. > > -Mary > ----- Original Message ----- > From: data _null_, > To: Mary > Sent: Friday, July 25, 2008 9:55 AM > Subject: Re: Office automation, save all sheets as txt. > > http://tinyurl.com/5phxj8 > > I haven't read it but I think it should help. > > On 7/25/08, Mary <mlhoward@avalon.net> wrote: > > > > Yes, that's the problem, I'm playing with it now; I'm pretty sure what's > > happening is that it is opening 2003 first, then attempting to open the > > file, so the file is converted to 2003 and chopped to 256 fields at that > > point, before it ever gets to the saving as text part. > > > > Thus what we are looking for is to how to change this line: > > > > put 'Set objExcel = CreateObject("Excel.Application")'; > > to open Excel 2007 rather than Excel 2003. > > > > -Mary > > ----- Original Message ----- > > From: data _null_, > > To: Mary > > Sent: Friday, July 25, 2008 9:19 AM > > Subject: Re: Office automation, save all sheets as txt. > > > > I can't test this I don't have office 2007 > > > > put 'Set objExcel = CreateObject("Excel.Application")'; > > > > This is the line that starts EXCEL. Does it start the 2007 version? > > To find out comment out the line > > > > put +3 '.Application.Quit'; > > > > This will leave excel open. And you can confirm version. > > > > Other than that I don't know. > > > > > > On 7/25/08, Mary <mlhoward@avalon.net> wrote: > > > > > > I did also try it on an Excel Office 2007 file (really, a very important > > > thing). > > > > > > It did work, but I saw "file conversion in place" when I did it and it > > > truncated fields past 256 fields; bummer. > > > > > > CHALLENGE: could we get this to work with Excel Office 2007 files that > > are > > > longer than 256 columns? > > > > > > -Mary > > > ----- Original Message ----- > > > From: Mary > > > To: data _null_, ; Arthur Tabachneck > > > Cc: SAS-L@listserv.uga.edu > > > Sent: Friday, July 25, 2008 8:49 AM > > > Subject: Re: Office automation, save all sheets as txt. > > > > > > The lucky number is indeed 21- it worked! What a wonderful utility! > > > > > > -Mary > > > ----- Original Message ----- > > > From: data _null_, > > > To: Arthur Tabachneck ; Mary > > > Cc: SAS-L@listserv.uga.edu > > > Sent: Thursday, July 24, 2008 5:57 PM > > > Subject: Re: Office automation, save all sheets as txt. > > > > > > xlTextMSDOS = 21 > > > > > > I believe the lucky number is 21 > > > > > > On 7/24/08, Arthur Tabachneck <art297@netscape.net> wrote: > > > > Mary, > > > > > > > > You could simply change the line: > > > > > > > > put +6 'objSheet.SaveAs txtout & "\" & objSheet.name & ".txt",6'; > > > > > > > > to > > > > > > > > put +6 'objSheet.SaveAs txtout & "\" & objSheet.name & > ".txt",-4158'; > > > > > > > > No guarantee, but that might just work. > > > > > > > > Art > > > > -------- > > > > On Thu, 24 Jul 2008 17:19:46 -0500, Mary <mlhoward@AVALON.NET> wrote: > > > > > > > > >Yes, it does work on my data! > > > > > > > > > >It is producing comma delimited files, however, which doesn't work > for > > me > > > > since my data consists of progress notes with commas. How would I > > change > > > > it to create tab delimited files? > > > > > > > > > >-Mary > > > > > ----- Original Message ----- > > > > > From: data _null_, > > > > > To: SAS-L@LISTSERV.UGA.EDU > > > > > Sent: Thursday, July 24, 2008 4:52 PM > > > > > Subject: Office automation, save all sheets as txt. > > > > > > > > > > > > > > > Here is may take on the SAVEAS TXT application. > > > > > > > > > > I have been fiddling with these VBSCRIPT programs for a while now, > > but > > > > > I've have never liked the steps involved. 1) write the script 2) > > call > > > > > it. Two data steps. Plus I usually want to look at it so I need > > > > > another data step. FILEVAR to the rescue. > > > > > > > > > > This example does all three operations in one data step. This > makes > > > > > nice tidy package than can become a stored data step program, PGM=. > > > > > Using file references to communicate with it and you get something > > > > > like this.... > > > > > > > > > > /*input, path to workbook*/ > > > > > filename workbook > > > > > "C:\Documents and Settings\&sysuserid\My Documents\Book1.xls"; > > > > > > > > > > /*output, just the folder name*/ > > > > > filename txtout > > > > > "C:\Documents and Settings\&sysuserid\My Documents"; > > > > > > > > > > data pgm=work.sheets2txt; > > > > > run; > > > > > > > > > > > > > > > I'm still struggling with the scripting, can anyone recommend some > > > > > good references. > > > > > > > > > > /********* Program **********/ > > > > > > > > > > data _null_ / pgm=work.sheets2txt; > > > > > /* gather info */ > > > > > length workbook txtout script filevar command $256; > > > > > workbook = pathname('WORKBOOK'); > > > > > txtout = pathname('TXTOUT'); > > > > > script = > > > catx('\',pathname('WORK'),'SHEETS2TXT.vbs'); > > > > > filevar = script; > > > > > > > > > > /* write the script */ > > > > > file dummy1 filevar=filevar; > > > > > put 'Const ' workbook=$quote256.; > > > > > put 'Const ' txtout=$quote256.; > > > > > put 'Set objExcel = > > > CreateObject("Excel.Application")'; > > > > > put 'With objExcel'; > > > > > put +3 '.Visible = True'; > > > > > put +3 '.DisplayAlerts = False'; > > > > > put +3 'Set objWorkbook = .Workbooks.Open(workbook)'; > > > > > put +3 'i = 0'; > > > > > put +3 'Set colSheets = .WorkSheets'; > > > > > put +3 'For Each objSheet In colSheets'; > > > > > put +6 'i = i + 1'; > > > > > put +6 'WScript.echo i & " " & objsheet.name '; > > > > > put +6 'objSheet.SaveAs txtout & "\" & objSheet.name & > > ".txt",6'; > > > > > put +6 'Next'; > > > > > put +3 '.Application.Quit'; > > > > > put +3 'End With'; > > > > > > > > > > /* close the script file by opening another, not used */ > > > > > filevar = > > catx('\',pathname('WORK'),'DUMMY.vbs');; > > > > > file dummy1 filevar=filevar; > > > > > > > > > > /* look at the script, not necessary but may be useful */ > > > > > infile dummy2 filevar=script end=eof; > > > > > do until(eof); > > > > > input; > > > > > putlog _infile_; > > > > > end; > > > > > > > > > > /* call the script */ > > > > > command = catx(' ','cscript > > > //NoLogo',quote(strip(script))); > > > > > infile dummy3 pipe filevar=command end=eof; > > > > > do until(eof); > > > > > input; > > > > > putlog _infile_; > > > > > end; > > > > > stop; > > > > > run; > > > > > > > > > > > > > > > > > > > > /*input*/ > > > > > filename workbook "C:\Documents and Settings\&sysuserid\My > > > > Documents\Book1.xls"; > > > > > /*output*/ > > > > > filename txtout "C:\Documents and Settings\&sysuserid\My > > Documents"; > > > > > > > > > > data pgm=work.sheets2txt; > > > > > run; > > > > > > > > > > filename workbook clear; > > > > > filename txtout clear; > > > >