01-26-2016 10:04 AM
I am not sure if this is the right group to post this problem. I am using a macro to generate Word report from Excel. The Excel file contains many graphs. When the Word report is generated, all the graphs convert into image and won't allow me to formate the graphs in Word. I am pasting few lines of the macro if someone can help me regarding the issue.
/* Copy into Picture */
%if &type0=C %then %do;
*formats the data in excel.;
*pastes the data into word.;
put '[EditGoTo.Destination = "'"&bookmark0."'"]';
put '[CharLeft 1,1]';
*if &color0.=1 then put '[FormatPic]';
*put '[FormatPicture.Scalex="'"%left(&width0.)"'", .ScaleY="'"%left(&height0.)"'"]';
I would appreciate help and support from the community.
01-26-2016 10:12 AM
Not really, this is a forum for the SAS product. I would ask however, why are you using SAS to write Excel commands to create a Word file? This seems a bit convoluted. I would suggest you would be better off not using SAS (which just adds another layer in), and use VBA within Excel, although why you don't go directly from source to output format is also another question.
In VBA (doesn't have to be associated with the source datafile), you could just do, something like:http://www.thespreadsheetguru.com/blog/2014/5/22/copy-paste-an-excel-table-into-microsoft-word-with-...
First search off Google.
Simply put, use the right technology for the right task, or in this case, go back to the souce and generate outputs from that.
01-26-2016 11:16 AM
You do know that the SAS code you have posted, is just putting out VBA code to the Excel object?
If you don't know VBA, it is very simple, you can record macros within Excel which will generate code for you as you do the process. Plus there is vast amounts of code out there on Google.
01-26-2016 11:33 AM - edited 01-26-2016 11:39 AM
There isn't a simple native way to get an Excel file to Word and likely maintain your charts as Excel objects. You'll probably find a faster solution if you try to modify the graphs in Excel and then create your Word file from that.
The only method I can think of is a macro that would copy and paste all objects into a Word template. It could be done, but I imagine it would be a lot of work, to get the positioning and formatting correct.
EDIT: It does look like that's what your code is attempting. Your problem now is modifying the graph section to embed the graph rather than an image. This would actually be a VB problem and you're better off asking in those forums. SAS just happens to be an middleman in the process here.
Here's one example.
My guess would be playing with the DATATYPE option will be what changes your type.