BookmarkSubscribeRSS Feed
renjithr
Quartz | Level 8

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!

9 REPLIES 9
Reeza
Super User

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
Quartz | Level 8

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
Super User

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
Quartz | Level 8

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
Super User

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

renjithr
Quartz | Level 8

Thanks for the clarification Reeza!

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

X 'path to vbs code';
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

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!

Discussion stats
  • 9 replies
  • 3408 views
  • 1 like
  • 3 in conversation