Sample code to refresh an excel sheet using vba from SAS

[renjithr]

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!

[Reeza]

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. 

[renjithr]

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.

[Reeza]

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?

[renjithr]

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;

[Reeza]

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

[renjithr]

Thanks for the clarification Reeza!

[Reeza]

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

[Reeza]

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

X 'path to vbs code';

[RW9]

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.