06-20-2016 05:45 PM
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!
06-20-2016 05:55 PM
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.
06-20-2016 06:01 PM
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.
06-20-2016 06:06 PM
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?
06-20-2016 06:10 PM
Here is a sample VBA code I am working on( Code does not work).
'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")" ' /
'XL.DisplayAlerts = True' /
'SET objWorkbook1 = Nothing' /
'SET XL = Nothing' /;
/* Execute VBA */
filename vbs "c:\test\irformatter.vbs";
filename vbs clear;
06-21-2016 04:33 AM
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.