topic Re: Sample code to refresh an excel sheet using vba from SAS in Microsoft Integration with SAS

Sample code to refresh an excel sheet using vba from SAS

renjithr — Mon, 20 Jun 2016 21:45:36 GMT

Hi,

Could you please provide a sample vba code that refreshes an excel sheet (with pivot table) from PC SAS?

I searched but no luck , so thought of posting here.

Thanks in advance!

Re: Sample code to refresh an excel sheet using vba from SAS

Reeza — Mon, 20 Jun 2016 21:55:24 GMT

This is a SAS forum. VBA code would come from a Microsoft forum, wouldn't it?

How does SAS fit into this process? Your question is unclear.

Re: Sample code to refresh an excel sheet using vba from SAS

renjithr — Mon, 20 Jun 2016 22:01:26 GMT

Sorry for being unclear.

I have a sas program that creates an excel spreadhseet and lately I have been creating a pivot table in the excel using the data that my SAS program produced. I came to know that using some vba code in SAS, the refresh of the pivot table can be automated via SAS/VBA.

Hope I made it clear this time.

Re: Sample code to refresh an excel sheet using vba from SAS

Reeza — Mon, 20 Jun 2016 22:06:14 GMT

I've only seen VBA called from DDE. Can you execute X commands?

Are you exporting to the same workbook?

Do you have your VBA code or need that code developed as well?

Re: Sample code to refresh an excel sheet using vba from SAS

Reeza — Mon, 20 Jun 2016 22:08:38 GMT

https://communities.sas.com/t5/SAS-Procedures/Updating-Extrenal-Excel-Pivot-Table-in-SAS/m-p/167515

Re: Sample code to refresh an excel sheet using vba from SAS

renjithr — Mon, 20 Jun 2016 22:10:48 GMT

Hi Reeza,

Here is a sample VBA code I am working on( Code does not work).

data _null_;
file "c:\test\ir_formatter.vbs";
put

'Dim XL' /
'SET XL = CreateObject("Excel.Application")' /
'XL.Visible = False' /
'XL.DisplayAlerts = False' /
'SET objWorkbook1 = XL.Workbooks.Open("("C:\test\book2.xlsm")")' /
'XL.RUN "("'C:\test\book2.xlsm.'!sheet1.helloworld")" ' /
'objWorkbook1.save' /
'XL.ActiveWorkbook.Close' /
'XL.DisplayAlerts = True' /
'SET objWorkbook1 = Nothing' /
'SET XL = Nothing' /;
run;

/* Execute VBA */
x 'c:\test\ir_formatter.vbs';
filename vbs "c:\test\irformatter.vbs";
data _null_;
rc=fdelete('vbs');
run;
filename vbs clear;

Re: Sample code to refresh an excel sheet using vba from SAS

Reeza — Mon, 20 Jun 2016 22:13:15 GMT

VBS is not quite the same as VBA... Just a friendly note.

Re: Sample code to refresh an excel sheet using vba from SAS

renjithr — Mon, 20 Jun 2016 22:16:34 GMT

Thanks for the clarification Reeza!

Re: Sample code to refresh an excel sheet using vba from SAS

Reeza — Mon, 20 Jun 2016 22:21:23 GMT

If you get a VBS working and you can use X command you can execute via

X 'path to vbs code';

Re: Sample code to refresh an excel sheet using vba from SAS

RW9 — Tue, 21 Jun 2016 08:33:38 GMT

Why not do it the other way round. Your obviously fixed on using Excel, so why not use that as the driver? Export the data from SAS as plain CSV file, then have the Excel file load the CSV data and populate its sheets appropriately usng VBA. Simple and less messing around trying to force one application from another.