BookmarkSubscribeRSS Feed
khalidamin
Obsidian | Level 7

Hi,

 

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.

 

data _null_;
/* Copy into Picture */
%if &type0=C %then %do;
*formats the data in excel.;
file Excel;
put '[workbook.activate("sheet1")]';
put '[select("'"&range0."'")]';
put '[copy]';
*pastes the data into word.;
file WORD;
put '[EditGoTo.Destination = "'"&bookmark0."'"]';
put '[EditPasteSpecial.DataType:="PICT"]';
put '[CharLeft 1,1]';
*if &color0.=1 then put '[FormatPic]';
*put '[FormatPicture.Scalex="'"%left(&width0.)"'", .ScaleY="'"%left(&height0.)"'"]';
%end;
 

I would appreciate help and support from the community.

 

Thank you.

 

Khalid

5 REPLIES 5
RW9
Diamond | Level 26 RW9
Diamond | Level 26

hi,

 

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.

khalidamin
Obsidian | Level 7
I don't know the VBA actually... trying to get help from Google. Thank you for your suggestions though.
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

Reeza
Super User

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.

http://stackoverflow.com/questions/22871774/copy-and-paste-an-excel-chart-with-a-vba-macro-in-word

 

My guess would be playing with the DATATYPE option will be what changes your type.

 

 

 

khalidamin
Obsidian | Level 7
Hi Reeza,

Thank you for your suggestions. I played with the DATATYPE options, but no luck. Thank you once again.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 756 views
  • 1 like
  • 3 in conversation