Running SAS Enterprise Guide Running on a Windows Server

Question

I wrote a code snippet in SAS EG that just opens an Excel workbook. The Excel workbook contains VBA code that executes when the "Workbook Open" event occurs. All code pretty much does this, it updates all data connections the first time it is opened every day.

When I run the SAS program manually, it works exactly as planned. It opens an Excel file, which in turn runs a VBA macro. When I plan to run SAS EG on my server, but the Work is done, but nothing happens with my Excel file. I also do not get any errors in my SAS code or in the Windows Scheduler Log.

Here is my SAS code:

options noxwait noxsync; x '"C:\Program Files\Microsoft Office\Office15\excel.exe" "\\route\to\file\excel_macro_playground.xlsm"'; run; 

Here is my VBA:

 Private Sub Workbook_Open() Dim wsSheet As Worksheet On Error Resume Next Set wsSheet = Sheets("book_helper") On Error GoTo 0 If wsSheet Is Nothing Then Sheets.Add.Name = "book_helper" ActiveWorkbook.RefreshAll Sheets("book_helper").Range("A1").Value = Date Sheets("book_helper").Visible = xlVeryHidden Application.DisplayAlerts = False ThisWorkbook.Save Application.DisplayAlerts = True Application.Quit Else If Sheets("book_helper").Range("A1").Value < Date Or Sheets("book_helper").Range("A1").Value = "" Then ActiveWorkbook.RefreshAll Sheets("book_helper").Range("A1").Value = Date Sheets("book_helper").Visible = xlVeryHidden 'ActiveWorkbook.Close savechanges:=True 'Application.Quit Application.DisplayAlerts = False ThisWorkbook.Save Application.DisplayAlerts = True Application.Quit End If End If End Sub 

And then, of course, I use the SAS EG Scheduling tool to set up the job. All my other jobs are working fine. Is there something I need to change to make it work as expected?

Answer 1

I agree with Joe's comment on DDE that it will not work, but what you do with the X command causes a system call as if it were on the command line, not in DDE. If you want to debug system calls, try using the FileName Pipe syntax to see the command output in the SAS log.

In your case ...

 FileName myCall Pipe '"C:\Program Files\Microsoft Office\Office15\excel.exe" "\\route\to\file\excel_macro_playground.xlsm"'; Data _NULL_; InFile myCall length=lineLength; Input line $varying256. lineLength; Put line; Run; 

I also noticed that your sample path to .xslm uses "\\", if the actual path starts like this, then this is most likely your problem. You cannot use UNC paths in standard system calls like this (some command line programs allow them). First you need to map the file server to disk sharing. Let me know if this is so, I can help you if you need it.