SAS Office Analytics, SAS Add-In for Microsoft Office, and other integrations

Sample code to refresh an excel sheet using vba from SAS

Reply
Frequent Contributor
Posts: 111

Sample code to refresh an excel sheet using vba from SAS

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!

Grand Advisor
Posts: 16,343

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

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. 

Frequent Contributor
Posts: 111

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

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.

Grand Advisor
Posts: 16,343

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

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?

Frequent Contributor
Posts: 111

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

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;

Grand Advisor
Posts: 16,343

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

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

Frequent Contributor
Posts: 111

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

Thanks for the clarification Reeza!

Grand Advisor
Posts: 16,343

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

Grand Advisor
Posts: 16,343

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

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

X 'path to vbs code';
Esteemed Advisor
Esteemed Advisor
Posts: 6,704

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

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.

Post a Question
Discussion Stats
  • 9 replies
  • 437 views
  • 1 like
  • 3 in conversation