Questions about SAS DDE,
in [SAS]
|
Prev: copy external file
Next: Identity_Insert and SAS/Access
From: "Choate, Paul on 26 Nov 2006 21:16 Xamil -=20 =20 The problem is that Excel names macro and worksheets sequentially - = macro1, macro2, macro3, etc. and sheet1, sheet2, sheet3, etc. On the = second iteration your program refers to incorrect macro and sheet names. = =20 Not quite sure what all you are trying to accomplish, but here's a = couple hints. First you don't need to delete the macro1 sheet. Just = reuse it until the job is finished and then delete it.=20 =20 As for sequential numbering of sheets, when you insert a sheet, it is = active, so you don't need to refer to it by default. =20 =20 If you have sheet1-3 already, then this inserts "sheet4" and moves it to = position #1.=20 put '[workbook.insert(1)]'; put '[workbook.move(,"my_workbook.xls",1)]'; This way you don't need to worry about the sequential numbering=20 =20 =20 Use this and relative position to create and rename sheets... assuming = Macro1 already exists, then this code creates a new sheet (by default in = the first position), moves Macro1 in front of it, and renames the new = sheet using a relative reference. =20 =20 data _null_; file sas2xl; put '[workbook.insert(1)]'; put '[workbook.move(,"my_workbook.xls",1)]'; /* put '[workbook.insert(3)]';*/ put '[workbook.move("macro1","my_workbook.xls",1)]'; run; filename xlmacro dde 'excel|macro1!r1c1:r100c1' notab lrecl=3D200; data _null_; file xlmacro; put "=3Dworkbook.name(,%bquote("&shtname"))"; put '=3Dhalt(true)'; put '!dde_flush'; run; data _null_; file sas2xl; put '[workbook.next()]'; put '[run("macro1!r1c1")]'; put '[error(false)]'; run; Thus you can create as many new sheets as you want and rename them in = the same job. =20 =20 hth =20 Paul Choate (916) 654-2160 DDS Data Extraction ________________________________ From: SAS(r) Discussion on behalf of xamil Sent: Sun 11/26/2006 1:07 PM To: SAS-L(a)LISTSERV.UGA.EDU Subject: Questions about SAS DDE , inserting and renaming more than one = sheet Dear all SAS users: I am using DDE to transfer SAS output to Excel. One of the task is to insert more than one worksheet one at a time to the existing(currently running) excel workbook, and then rename the worksheet according to user's specified name. I dont want to save and reopen the workbook every time when i need to insert a new worksheet and then renaming it. Inserting and renaming the first sheet as REPORT1 works fine, but when i try to insert the 2nd worksheet and rename it to REPORT2. I will give error message and won't rename the new worksheet REPORT2. Does anyone have any idea how to solve this problem ? Many thanks in advance !! Here is my code: Assuming the workbook MY_WORKBOOK is running: /*******************************************/ %let shtname=3DReport1; options noxsync noxwait xmin; filename sas2xl dde 'excel|system'; data _null_; length fid rc start stop time 8; fid=3Dfopen('sas2xl','s'); if (fid le 0) then do; rc=3Dsystem('start excel'); start=3Ddatetime(); stop=3Dstart+10; do while (fid le 0); fid=3Dfopen('sas2xl','s'); time=3Ddatetime(); if (time ge stop) then fid=3D1; end; end; rc=3Dfclose(fid); run; data _null_; file sas2xl; put '[workbook.next()]'; put '[workbook.insert(1)]'; put '[workbook.move("sheet1","my_workbook.xls",1)]'; run; data _null_; file sas2xl; put '[workbook.next()]'; put '[workbook.insert(3)]'; put '[workbook.move("macro1","my_workbook.xls",1)]'; run; filename xlmacro dde 'excel|macro1!r1c1:r100c1' notab lrecl=3D200; data _null_; file xlmacro; put "=3Dworkbook.name(%bquote("sheet1"), %bquote("&shtname"))"; put '=3Dhalt(true)'; put '!dde_flush'; file sas2xl; put '[run("macro1!r1c1")]'; put '[error(false)]'; run; data _null_; file sas2xl; put '[workbook.delete("Macro1")]'; run; _________________________________________________________________________= ___________ Cheap talk? Check out Yahoo! Messenger's low PC-to-Phone call rates. http://voice.yahoo.com
From: xamil on 27 Nov 2006 01:44 Paul, Thanks so much for your response to my question, and I will try your codes to my program. If I have more qustions, I will definitely ask you for more hint and help. Xamil --- "Choate, Paul(a)DDS" <pchoate(a)DDS.CA.GOV> wrote: > Xamil - > > The problem is that Excel names macro and worksheets > sequentially - macro1, macro2, macro3, etc. and > sheet1, sheet2, sheet3, etc. On the second > iteration your program refers to incorrect macro and > sheet names. > > Not quite sure what all you are trying to > accomplish, but here's a couple hints. First you > don't need to delete the macro1 sheet. Just reuse > it until the job is finished and then delete it. > > As for sequential numbering of sheets, when you > insert a sheet, it is active, so you don't need to > refer to it by default. > > If you have sheet1-3 already, then this inserts > "sheet4" and moves it to position #1. > > put '[workbook.insert(1)]'; > > put '[workbook.move(,"my_workbook.xls",1)]'; > > This way you don't need to worry about the > sequential numbering > > > Use this and relative position to create and rename > sheets... assuming Macro1 already exists, then this > code creates a new sheet (by default in the first > position), moves Macro1 in front of it, and renames > the new sheet using a relative reference. > > data _null_; > > file sas2xl; > > put '[workbook.insert(1)]'; > > put '[workbook.move(,"my_workbook.xls",1)]'; > > /* put '[workbook.insert(3)]';*/ > > put '[workbook.move("macro1","my_workbook.xls",1)]'; > > run; > > filename xlmacro dde 'excel|macro1!r1c1:r100c1' > notab lrecl=200; > > data _null_; > > file xlmacro; > > put "=workbook.name(,%bquote("&shtname"))"; > > put '=halt(true)'; > > put '!dde_flush'; > > run; > > data _null_; > > file sas2xl; > > put '[workbook.next()]'; > > put '[run("macro1!r1c1")]'; > > put '[error(false)]'; > > run; > > Thus you can create as many new sheets as you want > and rename them in the same job. > > > hth > > Paul Choate > (916) 654-2160 > DDS Data Extraction > > ________________________________ > > From: SAS(r) Discussion on behalf of xamil > Sent: Sun 11/26/2006 1:07 PM > To: SAS-L(a)LISTSERV.UGA.EDU > Subject: Questions about SAS DDE , inserting and > renaming more than one sheet > > > > Dear all SAS users: > I am using DDE to transfer SAS output to Excel. One > of > the task is to insert more than one worksheet one at > a > time to the existing(currently running) excel > workbook, and then rename the worksheet according to > user's specified name. I dont want to save and > reopen > the workbook every time when i need to insert a new > worksheet and then renaming it. > Inserting and renaming the first sheet as REPORT1 > works fine, but when i try to insert the 2nd > worksheet > and rename it to REPORT2. I will give error message > and won't rename the new worksheet REPORT2. Does > anyone have any idea how to solve this problem ? > > Many thanks in advance !! > > Here is my code: > Assuming the workbook MY_WORKBOOK is running: > > /*******************************************/ > %let shtname=Report1; > > options noxsync noxwait xmin; > filename sas2xl dde 'excel|system'; > data _null_; > length fid rc start stop time 8; > fid=fopen('sas2xl','s'); > if (fid le 0) then do; > rc=system('start excel'); > start=datetime(); > stop=start+10; > do while (fid le 0); > fid=fopen('sas2xl','s'); > time=datetime(); > if (time ge stop) then fid=1; > end; > end; > rc=fclose(fid); > run; > > data _null_; > file sas2xl; > put '[workbook.next()]'; > put '[workbook.insert(1)]'; > put '[workbook.move("sheet1","my_workbook.xls",1)]'; > run; > > data _null_; > file sas2xl; > put '[workbook.next()]'; > put '[workbook.insert(3)]'; > put '[workbook.move("macro1","my_workbook.xls",1)]'; > run; > > filename xlmacro dde 'excel|macro1!r1c1:r100c1' > notab > lrecl=200; > > data _null_; > file xlmacro; > put "=workbook.name(%bquote("sheet1"), > %bquote("&shtname"))"; > put '=halt(true)'; > put '!dde_flush'; > file sas2xl; > put '[run("macro1!r1c1")]'; > put '[error(false)]'; > run; > > data _null_; > file sas2xl; > put '[workbook.delete("Macro1")]'; > run; > > > > > > ____________________________________________________________________________________ > Cheap talk? > Check out Yahoo! Messenger's low PC-to-Phone call > rates. > http://voice.yahoo.com > > > ____________________________________________________________________________________ Do you Yahoo!? Everyone is raving about the all-new Yahoo! Mail beta. http://new.mail.yahoo.com
From: xamil on 28 Nov 2006 20:50 Paul, Thanks again for your help! I have followed your guidance and codes, and it successfully renames the new sheet inserted at each run (iteration), but still with some problem: ('Fronpage' is the only sheet in JUNK.xls before I run the program.) I ran the program 3 times,with new value for the %let statement at each run, and here are what happened: In the 1st run (iteration), With %let=Alpha, the workbook displays Macro1, Alpha, Sheet1, Frontpage. In the 2nd run, with %let=Beta, the workbook displays Macro1 Beta sheet4 sheet3 Alpha Sheet1 Frontpage . In the 3rd run, with %let=Gamma, the workbook displays Macro1 Gamma Sheet6 Sheet5 Betta Sheet4 sheet3 Alpha Sheet1 Frontpage. Is it possible to prevent those sheet#n and macro1 from the display? I have tried to use this code to hide the Macro1 from the sight data _null_; file sas2xl; put '[error(false)]'; put '[Workbook.hide("macro1")]'; run; it worked for the first run and second run, but not for the 3rd run. Don't really know why ? By the way Here is what I want to accomplish: an automation process with GUI implementation in Excel template for those who dont use SAS. The clients have the choice to fix a value for one or more parameters and click 'RUN button' from the 'Fronpage' sheet,and SAS run the program in the backgroud and dump the result to a new sheet at each run. Clients wants to compare the current result with the result from previous iteration. So Clients can run the program as many times as they want. /*** Here is the code I have tried**/ options noxsync noxwait xmin; filename sas2xl dde 'excel|system'; data _null_; length fid rc start stop time 8; fid=fopen('sas2xl','s'); if (fid le 0) then do; rc=system('start excel'); start=datetime(); stop=start+10; do while (fid le 0); fid=fopen('sas2xl','s'); time=datetime(); if (time ge stop) then fid=1; end; end; rc=fclose(fid); run; %let shtname=Alpha; data _null_; file sas2xl; put '[workbook.insert(1)]'; put '[workbook.move(,"junk.xls",1)]'; run; data _null_; file sas2xl; put '[workbook.insert(1)]'; put '[workbook.move(,"junk.xls",1)]'; put '[workbook.insert(3)]'; put '[workbook.move("macro1","junk.xls",1)]'; run; filename xlmacro dde 'excel|macro1!r1c1:r100c1' notab lrecl=200; data _null_; file xlmacro; put "=workbook.name(,%bquote("&shtname"))"; put '=halt(true)'; put '!dde_flush'; run; data _null_; file sas2xl; put '[workbook.next()]'; put '[run("macro1!r1c1")]'; put '[error(false)]'; run; /* data _null_; file sas2xl; put '[error(false)]'; put '[Workbook.hide("macro1")]'; run; */ --- "Choate, Paul(a)DDS" <pchoate(a)DDS.CA.GOV> wrote: > Xamil - > > The problem is that Excel names macro and worksheets > sequentially - macro1, macro2, macro3, etc. and > sheet1, sheet2, sheet3, etc. On the second > iteration your program refers to incorrect macro and > sheet names. > > Not quite sure what all you are trying to > accomplish, but here's a couple hints. First you > don't need to delete the macro1 sheet. Just reuse > it until the job is finished and then delete it. > > As for sequential numbering of sheets, when you > insert a sheet, it is active, so you don't need to > refer to it by default. > > If you have sheet1-3 already, then this inserts > "sheet4" and moves it to position #1. > > put '[workbook.insert(1)]'; > > put '[workbook.move(,"my_workbook.xls",1)]'; > > This way you don't need to worry about the > sequential numbering > > > Use this and relative position to create and rename > sheets... assuming Macro1 already exists, then this > code creates a new sheet (by default in the first > position), moves Macro1 in front of it, and renames > the new sheet using a relative reference. > > data _null_; > > file sas2xl; > > put '[workbook.insert(1)]'; > > put '[workbook.move(,"my_workbook.xls",1)]'; > > /* put '[workbook.insert(3)]';*/ > > put '[workbook.move("macro1","my_workbook.xls",1)]'; > > run; > > filename xlmacro dde 'excel|macro1!r1c1:r100c1' > notab lrecl=200; > > data _null_; > > file xlmacro; > > put "=workbook.name(,%bquote("&shtname"))"; > > put '=halt(true)'; > > put '!dde_flush'; > > run; > > data _null_; > > file sas2xl; > > put '[workbook.next()]'; > > put '[run("macro1!r1c1")]'; > > put '[error(false)]'; > > run; > > Thus you can create as many new sheets as you want > and rename them in the same job. > > > hth > > Paul Choate > (916) 654-2160 > DDS Data Extraction > > ________________________________ > > From: SAS(r) Discussion on behalf of xamil > Sent: Sun 11/26/2006 1:07 PM > To: SAS-L(a)LISTSERV.UGA.EDU > Subject: Questions about SAS DDE , inserting and > renaming more than one sheet > > > > Dear all SAS users: > I am using DDE to transfer SAS output to Excel. One > of > the task is to insert more than one worksheet one at > a > time to the existing(currently running) excel > workbook, and then rename the worksheet according to > user's specified name. I dont want to save and > reopen > the workbook every time when i need to insert a new > worksheet and then renaming it. > Inserting and renaming the first sheet as REPORT1 > works fine, but when i try to insert the 2nd > worksheet > and rename it to REPORT2. I will give error message > and won't rename the new worksheet REPORT2. Does > anyone have any idea how to solve this problem ? > > Many thanks in advance !! > > Here is my code: > Assuming the workbook MY_WORKBOOK is running: > > /*******************************************/ > %let shtname=Report1; > > options noxsync noxwait xmin; > filename sas2xl dde 'excel|system'; > data _null_; > length fid rc start stop time 8; > fid=fopen('sas2xl','s'); > if (fid le 0) then do; > rc=system('start excel'); > start=datetime(); > stop=start+10; > do while (fid le 0); > fid=fopen('sas2xl','s'); > time=datetime(); > if (time ge stop) then fid=1; > end; > end; > rc=fclose(fid); > run; > > data _null_; > file sas2xl; > put '[workbook.next()]'; > put '[workbook.insert(1)]'; > put '[workbook.move("sheet1","my_workbook.xls",1)]'; > run; > > data _null_; > file sas2xl; > put '[workbook.next()]'; > put '[workbook.insert(3)]'; > put '[workbook.move("macro1","my_workbook.xls",1)]'; > run; > > filename xlmacro dde 'excel|macro1!r1c1:r100c1' > notab > lrecl=200; > > data _null_; > file xlmacro; > put "=workbook.name(%bquote("sheet1"), > %bquote("&shtname"))"; > put '=halt(true)'; > put '!dde_flush'; > file sas2xl; > put '[run("macro1!r1c1")]'; > put '[error(false)]'; > run; > > data _null_; > file sas2xl; > put '[workbook.delete("Macro1")]'; > run; > > > > > > ____________________________________________________________________________________ > Cheap talk? > Check out Yahoo! Messenger's low PC-to-Phone call > rates. > http://voice.yahoo.com > ____________________________________________________________________________________ Want to start your own business? Learn how on Yahoo! Small Business. http://smallbusiness.yahoo.com/r-index
From: Alan Churchill on 28 Nov 2006 21:31 Xamil, I have written on this several times. IMO, you are approaching this problem in the wrong direction. If, in fact, you are trying to use Excel to automate the reading of SAS then create the application in Excel and then call SAS. SAS is an OleDB compliant datastore so it is easy to call SAS and read in a SAS dataset. You can also submit to SAS using Integration Technologies (IOM for remote or local provider if SAS is installed locally). You are coding Excel using put statements: there are better ways. There are even better ways than the above but coding in VBA in Excel will get you in the right direction. Use a pull rather than a push approach. Alan Alan Churchill Savian "Bridging SAS and Microsoft Technologies" www.savian.net -----Original Message----- From: SAS(r) Discussion [mailto:SAS-L(a)LISTSERV.UGA.EDU] On Behalf Of xamil Sent: Tuesday, November 28, 2006 6:51 PM To: SAS-L(a)LISTSERV.UGA.EDU Subject: Re: Questions about SAS DDE, inserting and renaming more than one sheet Paul, Thanks again for your help! I have followed your guidance and codes, and it successfully renames the new sheet inserted at each run (iteration), but still with some problem: ('Fronpage' is the only sheet in JUNK.xls before I run the program.) I ran the program 3 times,with new value for the %let statement at each run, and here are what happened: In the 1st run (iteration), With %let=Alpha, the workbook displays Macro1, Alpha, Sheet1, Frontpage. In the 2nd run, with %let=Beta, the workbook displays Macro1 Beta sheet4 sheet3 Alpha Sheet1 Frontpage . In the 3rd run, with %let=Gamma, the workbook displays Macro1 Gamma Sheet6 Sheet5 Betta Sheet4 sheet3 Alpha Sheet1 Frontpage. Is it possible to prevent those sheet#n and macro1 from the display? I have tried to use this code to hide the Macro1 from the sight data _null_; file sas2xl; put '[error(false)]'; put '[Workbook.hide("macro1")]'; run; it worked for the first run and second run, but not for the 3rd run. Don't really know why ? By the way Here is what I want to accomplish: an automation process with GUI implementation in Excel template for those who dont use SAS. The clients have the choice to fix a value for one or more parameters and click 'RUN button' from the 'Fronpage' sheet,and SAS run the program in the backgroud and dump the result to a new sheet at each run. Clients wants to compare the current result with the result from previous iteration. So Clients can run the program as many times as they want. /*** Here is the code I have tried**/ options noxsync noxwait xmin; filename sas2xl dde 'excel|system'; data _null_; length fid rc start stop time 8; fid=fopen('sas2xl','s'); if (fid le 0) then do; rc=system('start excel'); start=datetime(); stop=start+10; do while (fid le 0); fid=fopen('sas2xl','s'); time=datetime(); if (time ge stop) then fid=1; end; end; rc=fclose(fid); run; %let shtname=Alpha; data _null_; file sas2xl; put '[workbook.insert(1)]'; put '[workbook.move(,"junk.xls",1)]'; run; data _null_; file sas2xl; put '[workbook.insert(1)]'; put '[workbook.move(,"junk.xls",1)]'; put '[workbook.insert(3)]'; put '[workbook.move("macro1","junk.xls",1)]'; run; filename xlmacro dde 'excel|macro1!r1c1:r100c1' notab lrecl=200; data _null_; file xlmacro; put "=workbook.name(,%bquote("&shtname"))"; put '=halt(true)'; put '!dde_flush'; run; data _null_; file sas2xl; put '[workbook.next()]'; put '[run("macro1!r1c1")]'; put '[error(false)]'; run; /* data _null_; file sas2xl; put '[error(false)]'; put '[Workbook.hide("macro1")]'; run; */ --- "Choate, Paul(a)DDS" <pchoate(a)DDS.CA.GOV> wrote: > Xamil - > > The problem is that Excel names macro and worksheets > sequentially - macro1, macro2, macro3, etc. and > sheet1, sheet2, sheet3, etc. On the second > iteration your program refers to incorrect macro and > sheet names. > > Not quite sure what all you are trying to > accomplish, but here's a couple hints. First you > don't need to delete the macro1 sheet. Just reuse > it until the job is finished and then delete it. > > As for sequential numbering of sheets, when you > insert a sheet, it is active, so you don't need to > refer to it by default. > > If you have sheet1-3 already, then this inserts > "sheet4" and moves it to position #1. > > put '[workbook.insert(1)]'; > > put '[workbook.move(,"my_workbook.xls",1)]'; > > This way you don't need to worry about the > sequential numbering > > > Use this and relative position to create and rename > sheets... assuming Macro1 already exists, then this > code creates a new sheet (by default in the first > position), moves Macro1 in front of it, and renames > the new sheet using a relative reference. > > data _null_; > > file sas2xl; > > put '[workbook.insert(1)]'; > > put '[workbook.move(,"my_workbook.xls",1)]'; > > /* put '[workbook.insert(3)]';*/ > > put '[workbook.move("macro1","my_workbook.xls",1)]'; > > run; > > filename xlmacro dde 'excel|macro1!r1c1:r100c1' > notab lrecl=200; > > data _null_; > > file xlmacro; > > put "=workbook.name(,%bquote("&shtname"))"; > > put '=halt(true)'; > > put '!dde_flush'; > > run; > > data _null_; > > file sas2xl; > > put '[workbook.next()]'; > > put '[run("macro1!r1c1")]'; > > put '[error(false)]'; > > run; > > Thus you can create as many new sheets as you want > and rename them in the same job. > > > hth > > Paul Choate > (916) 654-2160 > DDS Data Extraction > > ________________________________ > > From: SAS(r) Discussion on behalf of xamil > Sent: Sun 11/26/2006 1:07 PM > To: SAS-L(a)LISTSERV.UGA.EDU > Subject: Questions about SAS DDE , inserting and > renaming more than one sheet > > > > Dear all SAS users: > I am using DDE to transfer SAS output to Excel. One > of > the task is to insert more than one worksheet one at > a > time to the existing(currently running) excel > workbook, and then rename the worksheet according to > user's specified name. I dont want to save and > reopen > the workbook every time when i need to insert a new > worksheet and then renaming it. > Inserting and renaming the first sheet as REPORT1 > works fine, but when i try to insert the 2nd > worksheet > and rename it to REPORT2. I will give error message > and won't rename the new worksheet REPORT2. Does > anyone have any idea how to solve this problem ? > > Many thanks in advance !! > > Here is my code: > Assuming the workbook MY_WORKBOOK is running: > > /*******************************************/ > %let shtname=Report1; > > options noxsync noxwait xmin; > filename sas2xl dde 'excel|system'; > data _null_; > length fid rc start stop time 8; > fid=fopen('sas2xl','s'); > if (fid le 0) then do; > rc=system('start excel'); > start=datetime(); > stop=start+10; > do while (fid le 0); > fid=fopen('sas2xl','s'); > time=datetime(); > if (time ge stop) then fid=1; > end; > end; > rc=fclose(fid); > run; > > data _null_; > file sas2xl; > put '[workbook.next()]'; > put '[workbook.insert(1)]'; > put '[workbook.move("sheet1","my_workbook.xls",1)]'; > run; > > data _null_; > file sas2xl; > put '[workbook.next()]'; > put '[workbook.insert(3)]'; > put '[workbook.move("macro1","my_workbook.xls",1)]'; > run; > > filename xlmacro dde 'excel|macro1!r1c1:r100c1' > notab > lrecl=200; > > data _null_; > file xlmacro; > put "=workbook.name(%bquote("sheet1"), > %bquote("&shtname"))"; > put '=halt(true)'; > put '!dde_flush'; > file sas2xl; > put '[run("macro1!r1c1")]'; > put '[error(false)]'; > run; > > data _null_; > file sas2xl; > put '[workbook.delete("Macro1")]'; > run; > > > > > > ____________________________________________________________________________ ________ > Cheap talk? > Check out Yahoo! Messenger's low PC-to-Phone call > rates. > http://voice.yahoo.com > ____________________________________________________________________________ ________ Want to start your own business? Learn how on Yahoo! Small Business. http://smallbusiness.yahoo.com/r-index
From: Alan Churchill on 29 Nov 2006 09:32
You may want to check out Vince's paper on VBA and IntrNet as well: http://www2.sas.com/proceedings/sugi27/p174-27.pdf Alan Alan Churchill Savian "Bridging SAS and Microsoft Technologies" www.savian.net -----Original Message----- From: SAS(r) Discussion [mailto:SAS-L(a)LISTSERV.UGA.EDU] On Behalf Of xamil Sent: Tuesday, November 28, 2006 6:51 PM To: SAS-L(a)LISTSERV.UGA.EDU Subject: Re: Questions about SAS DDE, inserting and renaming more than one sheet Paul, Thanks again for your help! I have followed your guidance and codes, and it successfully renames the new sheet inserted at each run (iteration), but still with some problem: ('Fronpage' is the only sheet in JUNK.xls before I run the program.) I ran the program 3 times,with new value for the %let statement at each run, and here are what happened: In the 1st run (iteration), With %let=Alpha, the workbook displays Macro1, Alpha, Sheet1, Frontpage. In the 2nd run, with %let=Beta, the workbook displays Macro1 Beta sheet4 sheet3 Alpha Sheet1 Frontpage . In the 3rd run, with %let=Gamma, the workbook displays Macro1 Gamma Sheet6 Sheet5 Betta Sheet4 sheet3 Alpha Sheet1 Frontpage. Is it possible to prevent those sheet#n and macro1 from the display? I have tried to use this code to hide the Macro1 from the sight data _null_; file sas2xl; put '[error(false)]'; put '[Workbook.hide("macro1")]'; run; it worked for the first run and second run, but not for the 3rd run. Don't really know why ? By the way Here is what I want to accomplish: an automation process with GUI implementation in Excel template for those who dont use SAS. The clients have the choice to fix a value for one or more parameters and click 'RUN button' from the 'Fronpage' sheet,and SAS run the program in the backgroud and dump the result to a new sheet at each run. Clients wants to compare the current result with the result from previous iteration. So Clients can run the program as many times as they want. /*** Here is the code I have tried**/ options noxsync noxwait xmin; filename sas2xl dde 'excel|system'; data _null_; length fid rc start stop time 8; fid=fopen('sas2xl','s'); if (fid le 0) then do; rc=system('start excel'); start=datetime(); stop=start+10; do while (fid le 0); fid=fopen('sas2xl','s'); time=datetime(); if (time ge stop) then fid=1; end; end; rc=fclose(fid); run; %let shtname=Alpha; data _null_; file sas2xl; put '[workbook.insert(1)]'; put '[workbook.move(,"junk.xls",1)]'; run; data _null_; file sas2xl; put '[workbook.insert(1)]'; put '[workbook.move(,"junk.xls",1)]'; put '[workbook.insert(3)]'; put '[workbook.move("macro1","junk.xls",1)]'; run; filename xlmacro dde 'excel|macro1!r1c1:r100c1' notab lrecl=200; data _null_; file xlmacro; put "=workbook.name(,%bquote("&shtname"))"; put '=halt(true)'; put '!dde_flush'; run; data _null_; file sas2xl; put '[workbook.next()]'; put '[run("macro1!r1c1")]'; put '[error(false)]'; run; /* data _null_; file sas2xl; put '[error(false)]'; put '[Workbook.hide("macro1")]'; run; */ --- "Choate, Paul(a)DDS" <pchoate(a)DDS.CA.GOV> wrote: > Xamil - > > The problem is that Excel names macro and worksheets > sequentially - macro1, macro2, macro3, etc. and > sheet1, sheet2, sheet3, etc. On the second > iteration your program refers to incorrect macro and > sheet names. > > Not quite sure what all you are trying to > accomplish, but here's a couple hints. First you > don't need to delete the macro1 sheet. Just reuse > it until the job is finished and then delete it. > > As for sequential numbering of sheets, when you > insert a sheet, it is active, so you don't need to > refer to it by default. > > If you have sheet1-3 already, then this inserts > "sheet4" and moves it to position #1. > > put '[workbook.insert(1)]'; > > put '[workbook.move(,"my_workbook.xls",1)]'; > > This way you don't need to worry about the > sequential numbering > > > Use this and relative position to create and rename > sheets... assuming Macro1 already exists, then this > code creates a new sheet (by default in the first > position), moves Macro1 in front of it, and renames > the new sheet using a relative reference. > > data _null_; > > file sas2xl; > > put '[workbook.insert(1)]'; > > put '[workbook.move(,"my_workbook.xls",1)]'; > > /* put '[workbook.insert(3)]';*/ > > put '[workbook.move("macro1","my_workbook.xls",1)]'; > > run; > > filename xlmacro dde 'excel|macro1!r1c1:r100c1' > notab lrecl=200; > > data _null_; > > file xlmacro; > > put "=workbook.name(,%bquote("&shtname"))"; > > put '=halt(true)'; > > put '!dde_flush'; > > run; > > data _null_; > > file sas2xl; > > put '[workbook.next()]'; > > put '[run("macro1!r1c1")]'; > > put '[error(false)]'; > > run; > > Thus you can create as many new sheets as you want > and rename them in the same job. > > > hth > > Paul Choate > (916) 654-2160 > DDS Data Extraction > > ________________________________ > > From: SAS(r) Discussion on behalf of xamil > Sent: Sun 11/26/2006 1:07 PM > To: SAS-L(a)LISTSERV.UGA.EDU > Subject: Questions about SAS DDE , inserting and > renaming more than one sheet > > > > Dear all SAS users: > I am using DDE to transfer SAS output to Excel. One > of > the task is to insert more than one worksheet one at > a > time to the existing(currently running) excel > workbook, and then rename the worksheet according to > user's specified name. I dont want to save and > reopen > the workbook every time when i need to insert a new > worksheet and then renaming it. > Inserting and renaming the first sheet as REPORT1 > works fine, but when i try to insert the 2nd > worksheet > and rename it to REPORT2. I will give error message > and won't rename the new worksheet REPORT2. Does > anyone have any idea how to solve this problem ? > > Many thanks in advance !! > > Here is my code: > Assuming the workbook MY_WORKBOOK is running: > > /*******************************************/ > %let shtname=Report1; > > options noxsync noxwait xmin; > filename sas2xl dde 'excel|system'; > data _null_; > length fid rc start stop time 8; > fid=fopen('sas2xl','s'); > if (fid le 0) then do; > rc=system('start excel'); > start=datetime(); > stop=start+10; > do while (fid le 0); > fid=fopen('sas2xl','s'); > time=datetime(); > if (time ge stop) then fid=1; > end; > end; > rc=fclose(fid); > run; > > data _null_; > file sas2xl; > put '[workbook.next()]'; > put '[workbook.insert(1)]'; > put '[workbook.move("sheet1","my_workbook.xls",1)]'; > run; > > data _null_; > file sas2xl; > put '[workbook.next()]'; > put '[workbook.insert(3)]'; > put '[workbook.move("macro1","my_workbook.xls",1)]'; > run; > > filename xlmacro dde 'excel|macro1!r1c1:r100c1' > notab > lrecl=200; > > data _null_; > file xlmacro; > put "=workbook.name(%bquote("sheet1"), > %bquote("&shtname"))"; > put '=halt(true)'; > put '!dde_flush'; > file sas2xl; > put '[run("macro1!r1c1")]'; > put '[error(false)]'; > run; > > data _null_; > file sas2xl; > put '[workbook.delete("Macro1")]'; > run; > > > > > > ____________________________________________________________________________ ________ > Cheap talk? > Check out Yahoo! Messenger's low PC-to-Phone call > rates. > http://voice.yahoo.com > ____________________________________________________________________________ ________ Want to start your own business? Learn how on Yahoo! Small Business. http://smallbusiness.yahoo.com/r-index |