The SAS Output Delivery System and reporting techniques

Can the excel_enhance macro insert graphics in multiple sheets?

Reply
Contributor
Posts: 32

Can the excel_enhance macro insert graphics in multiple sheets?

I'm trying to place some SAS-genreated sparklines in a multiple-sheet workbook. But the macro fails when inserting graphs in multiple sheets. Here's a small example. 

 

The code works when all the sheets are set to tom, but fails when trying to put graphs in dick and harry.

 

data graphs;
  input File :$30. Sheet :$8. Cell :$4.;
  length String $60;
  *sheet='Tom';
  string='c:\users\agriffin\g\' !! trim(file) !! '#' !! trim(sheet) !! '!' !! cell;
cards;
g8EPM2.5010.png Tom      b3
g8EPM2.5020.png Tom      b4
g8EPM2.5030.png Dick     d7
g8EPM2.5099.png Dick     d8
run;

proc sql noprint;
  select string into Smiley Tonguelacement separated by ', ' from graphs;
quit;
%put &placement;
%excel_enhance(open_workbook=c:\users\agriffin\desktop\test_book.xlsx, insert_image=%quote(&placement), file_format=xlsx);


g8EPM2.5010.pngg8EPM2.5020.pngg8EPM2.5030.pngg8EPM2.5199.png
SAS Super FREQ
Posts: 8,861

Re: Can the excel_enhance macro insert graphics in multiple sheets?

Posted in reply to AdrianGriffin
Hi:
I'm not sure HOW you are trying to insert the graphics. Are you using ODS? If so, are you using ODS HTML, ODS TAGSETS.EXCELXP or ODS EXCEL inside your %excel_enhance macro program? Or, are you using DDE? Or??? The answer to your question is inside your macro program when you invoke it and whether there are any error messages in the log. This might be something to address with Tech Support, since they could look at the code INSIDE the macro program to try to help you figure out what's going wrong.

cynthia
Contributor
Posts: 32

Re: Can the excel_enhance macro insert graphics in multiple sheets?

Posted in reply to Cynthia_sas
Cynthia:
It's Chevell's macro, included in the SGF2016_SAS177.ZIP file that I downloaded from your ftp site.
SAS Super FREQ
Posts: 8,861

Re: Can the excel_enhance macro insert graphics in multiple sheets?

Posted in reply to AdrianGriffin
Ah, does it use TAGSETS.TABLEEDITOR? I forgot that as an option? Still if you're not getting error messages in the log probably better to open a track with Tech Support.

cynthia
Super User
Posts: 19,768

Re: Can the excel_enhance macro insert graphics in multiple sheets?

Posted in reply to AdrianGriffin

In your macro call you're not specifying the cell, is that intentional? Otherwise how do you specify the multiple destinations?

Since the macro is demo'd with multiple sheets this should be possible.

Contributor
Posts: 32

Re: Can the excel_enhance macro insert graphics in multiple sheets?

The cell is specified in the insert_image argument. If you run the code and do a %put &placement, you'll see how the cell and sheet is specified.
Super User
Posts: 11,336

Re: Can the excel_enhance macro insert graphics in multiple sheets?

Posted in reply to AdrianGriffin

AdrianGriffin wrote:
The cell is specified in the insert_image argument. If you run the code and do a %put &placement, you'll see how the cell and sheet is specified.

We don't have your data.

Many users here don't want to download Excel files because of virus potential, others have such things blocked by security software. Also if you give us Excel we have to create a SAS data set and due to the non-existent constraints on Excel data cells the result we end up with may not have variables of the same type (numeric or character) and even values.

 

Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the {i} icon or attached as text to show exactly what you have and that we can test code against.

 

You can run the macro with the options MPRINT and SYMBOLGEN to see details of the code generated.

You could post the log in a code box opened with the forum {i} icon.

 

 

Contributor
Posts: 32

Re: Can the excel_enhance macro insert graphics in multiple sheets?

The excel sheet is just an empty sheet that the macro puts the graphs into. All the workings are in the SAS code that I pasted into my original message. If you don't want to download the spreadsheet, just create an empty workbook with three tabs --tom, dick, and harry.
Super User
Posts: 19,768

Re: Can the excel_enhance macro insert graphics in multiple sheets?

Posted in reply to AdrianGriffin

Ok, different question - did the demo from the paper work as expected for you?

Contributor
Posts: 32

Re: Can the excel_enhance macro insert graphics in multiple sheets?

I didn't try Chevell's example, but my code works as expected when putting all of the graphs in the same sheet. If you uncomment the sheet=tom line, everything will work.
Ask a Question
Discussion stats
  • 9 replies
  • 118 views
  • 0 likes
  • 4 in conversation