DATA Step, Macro, Functions and more

Need to combine two excel files into one excel file with two diffrent tabs

Reply
Frequent Contributor
Posts: 76

Need to combine two excel files into one excel file with two diffrent tabs

Hi All,

I have two different excel files .

one excel file is creating automatically by automated programme(automatically sending email to concerned persons)  another one is a static file (the values from this files never change). so I want to combine this excel files into one excel file with two different tabs before sending to automated email report .

Thanks,

Super User
Posts: 10,588

Re: Need to combine two excel files into one excel file with two diffrent tabs

As all the action is happening in Excel, use the proper tool, which is VBA.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Super User
Super User
Posts: 9,855

Re: Need to combine two excel files into one excel file with two diffrent tabs

"the values from this files never change" - if this is true, then you do not need the second file at all.  The program which creates the first output can also create the second output in the same step, hence problem solved:

data fixed;
  a=1; b=2;
run;

ods tagsets.excelxp...;
ods tagsets.excelxp options(sheet_name="First");
proc print data=have;
run;
ods tagsets.excelxp options(sheet_name="Second");
proc print data=fixed;
run;
...
Frequent Contributor
Posts: 76

Re: Need to combine two excel files into one excel file with two diffrent tabs

one file values will be changed because it is a monthly report and another one doesn't change. so I need to add these files together
Super User
Super User
Posts: 9,855

Re: Need to combine two excel files into one excel file with two diffrent tabs

The point is, if the second file never changes then drop it totally and add it into the generation of the other output file, there is no adding of files needed, your just adding another step in then.  The monthly report should be updated to output the default tab also, one minor change in one place and problem solved.

Frequent Contributor
Posts: 76

Re: Need to combine two excel files into one excel file with two diffrent tabs

1.automatic report.xls (this will be changed always because it is creating by using proc report and sending automatic email to the users)
2.values.xls.(this won't change and this is in local drive and this is also a report but created by me with colors and etc.).
so I need to combine these excel files and needs to be send automated mails to users.
Super User
Super User
Posts: 9,855

Re: Need to combine two excel files into one excel file with two diffrent tabs

[ Edited ]

1.automatic report.xls (this will be changed always because it is creating by using proc report ** at which point values tab can also be written out as it is fixed and never changes ** and sending automatic email to the users)
2.values.xls.(this won't change and this is in local drive and this is also a report but created by me with colors and etc.).
so I need to combine these excel files and needs to be send automated mails to users.

 

Alternatively:

1) create first report

2) create second report

3) have an excel template with vba code which loads both and outputs a new combined file

4) then run code to send file to users

Thus 4 times the number of steps, twice the technologies used, at least one change to existing process (unless the original file is still to go out at that point also), and the whole Excel part which will likely fall over when not in a perfect environment (i.e. move to linux which doesn't have Excel).

Ask a Question
Discussion stats
  • 6 replies
  • 90 views
  • 0 likes
  • 3 in conversation