Executing Excel Macro From ColdFusion Server

Question

I have a ColdFusion Web application which lets the user upload an Excel Workbook on the server.

I would like to execute a macro within that Excel file, once the file has been uploaded.

Anyone has an idea on how I could manage to do that??

Note: I can use Java objects too, I am already using HSSF POI to access the Sheets' content.

Answer 1

I found out a way to do such thing:

1) Write a VBS Script file on the server like so:

    <cfset contentVBS = 
            "Dim objXL" & Chr(13) & Chr(10) &
            "Set objXL = CreateObject(" & Chr(34) & "Excel.Application" & Chr(34) & ")" & Chr(13) & Chr(10) &
            "With objXL" & Chr(13) & Chr(10) &
            "   .Workbooks.Open (" & Chr(34) & VARIABLES.Instance.filePath & VARIABLES.Instance.fileName & Chr(34) & ")" & Chr(13) & Chr(10) &
            "   .Application.Run " & Chr(34) & VARIABLES.Instance.macroName & Chr(34) & Chr(13) & Chr(10) &
            "   .Application.Quit" & Chr(13) & Chr(10) &
            "End With" & Chr(13) & Chr(10) &
            "Set objXL = Nothing" & Chr(13) & Chr(10)
    />

    <cffile
        action="write"
        charset="utf-8"
        file="#VARIABLES.Instance.filePath##VARIABLES.Instance.script_vbs_name#"
        output="#contentVBS#"
        addnewline="no"
    >

Where VARIABLES.Instance.filePath is the path to my Excel file, VARIABLES.Instance.fileName is the name of my Excel file and , VARIABLES.Instance.macroNameis the name of the macro I want to launch.

2) Execute the vbs file using cscript.exe:

    <cfexecute name = "C:\WINDOWS\system32\cscript.exe"
        arguments = "#VARIABLES.Instance.filePath##VARIABLES.Instance.script_vbs_name#">
    </cfexecute>

Where VARIABLES.Instance.script_vbs_name is the name of the VBS script file I just written.

Note: The macro generates a file on the server so I needed to specify to the ColdFusion Server read/write access to the folder.