topic Can the excel_enhance macro insert graphics in multiple sheets? in ODS and Base Reporting

Can the excel_enhance macro insert graphics in multiple sheets?

AdrianGriffin — Mon, 05 Jun 2017 21:02:20 GMT

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 :placement 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);

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

Cynthia_sas — Mon, 05 Jun 2017 21:57:13 GMT

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

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

AdrianGriffin — Mon, 05 Jun 2017 22:01:52 GMT

Cynthia:
It's Chevell's macro, included in the SGF2016_SAS177.ZIP file that I downloaded from your ftp site.

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

Cynthia_sas — Mon, 05 Jun 2017 22:03:15 GMT

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

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

Reeza — Mon, 05 Jun 2017 22:15:01 GMT

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.

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

AdrianGriffin — Mon, 05 Jun 2017 22:30:26 GMT

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.

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

ballardw — Mon, 05 Jun 2017 22:38:26 GMT

@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-data-AKA-generate/ta-p/258712 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.

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

Reeza — Mon, 05 Jun 2017 22:48:28 GMT

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

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

AdrianGriffin — Mon, 05 Jun 2017 22:52:26 GMT

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.

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

AdrianGriffin — Mon, 05 Jun 2017 23:04:44 GMT

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.