topic Re: Excel to word macro in SAS Programming

Excel to word macro

khalidamin — Tue, 26 Jan 2016 15:04:21 GMT

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

Re: Excel to word macro

RW9 — Tue, 26 Jan 2016 15:12:33 GMT

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-vba

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.

Re: Excel to word macro

khalidamin — Tue, 26 Jan 2016 16:04:37 GMT

I don't know the VBA actually... trying to get help from Google. Thank you for your suggestions though.

Re: Excel to word macro

RW9 — Tue, 26 Jan 2016 16:16:57 GMT

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.

Re: Excel to word macro

Reeza — Tue, 26 Jan 2016 16:39:08 GMT

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.

Re: Excel to word macro

khalidamin — Wed, 27 Jan 2016 16:27:05 GMT

Hi Reeza,

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