Running SAS Enterprise Guide Job on windows Server

Question

I have written a piece of code in SAS EG that simply opens an Excel workbook. The Excel workbook contains VBA code that is executed upon the "Workbook Open" Event. All the code pretty much does, is it refreshes all data connections the first time it's opened every day.

When I run the SAS programme manually, it works exactly as planned. It Opens the Excel file which in turn triggers the VBA Macro. When I schedule the SAS EG job on my Server however, the Job runs but nothing happens to my Excel file. I am also not presented with any errors in my SAS code or on My 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 running just fine. Is there something I need to change in order for this to work as expected?

Answer 1

I agree with Joe's comment on DDE that it wouldn't work, but what you're doing through an X command is making a system call as if it were on the command line, not 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've also just noticed your example path to the .xslm uses "\\", if the actual path starts like this then that's likely your problem. You can't use UNC paths in standard system calls like this (some command line programs allow them). You'd need to map the file server and share to a drive first. Let me know if this is the case I can help you to if you need it.