cfexecute command fails to execute the vbscript.

[Guest]

I am trying to format an excel sheet using vbscript.  This script works fine when i execute it in the server.  But when i call this script from my CF component file. This script call does not show any error or success. 

Environment:  ColdFusion 11, Windows 7 , 64 bit Machine.

After checking some threads on office automation I found this .

We have to add the Desktop folder in the systemprofile and system32 folder to open file by Excel if you have Windows Service or IIS on Windows 7 x64 (dev machine) and Server 2008 x64.

So I added Desktop folder under these 2 directories -

C:\Windows\System32\config\systemprofile
C:\Windows\SysWOW64\config\systemprofile

I tried this as well.  Still no success.  Any suggestions??

VBScript Call from ColdFusion:

---------------------------------------

<cfexecute name = "C:\Windows\System32\cscript.exe" arguments  = "//Nologo #GV_SCRIPTPATH#" timeout="50000"  variable = "savedOutput"></cfexecute>

<cflog text="#savedOutput#">

[Carl Von Stetten]

I can't say why the VBScript command doesn't work, but have you considered using the ColdFusion built-in spreadsheet tools or Apache POI to do your formatting?  Is there something it can't do that VBScript can?

[Guest]

My requirement is to generate an excel sheet with dynamic columns and rows. CF Spreadsheet in built functions does not have some options like vertical column orientation.  VBscript i have created does that for me quickly.  It is very effective and faster.

[James Moberg]

Which user account is ColdFusion running under?  Does it have the necessary rights to run the script?

As a work around, there's a commercial third-party C++ CFX tag called CFX_EXEC that will enable you to run any script using any user/pass/domain and/or working directory.  I've used it in the past when I needed elevated (or simply different) permissions in order to access something.

[Guest]

Currently my ColdFusion service is running under my user account.  My account has access to run the vbscript. 

Let me try with CFX_EXEC and see if it works.  I will update you.  Thanks for looking into it.

[Guest]

I tried the CFX_EXEC trail version. It does not throw any error message but I am still unsuccessful with my output.  I am expecting a file to be generated as an output after running this vbscript. is there anything wrong with my cfx call. ?

ColdFusion Call to VBSCRIPT:

<CFX_EXEC CMD="C:\Windows\System32\wscript.exe C:\ColdFusion2016\cfusion\wwwroot\CCM_WEB\CCM_FILES\DOWNLOADS\SCRIPT_FILE.vbs" NAME="OUT"> 

MY VBSCRIPT:

Set objFSO = CreateObject("Scripting.FileSystemObject")

dim CurrentDirectory

CurrentDirectory = objFSO.GetAbsolutePathName(".")

objStartFolder = CurrentDirectory & "\XLS"

objEndFolder = CurrentDirectory & "\XLSX"

If NOT objFSO.FolderExists(objStartFolder) Then

objFSO.CreateFolder(objStartFolder)

WScript.quit

End If

If NOT objFSO.FolderExists(objEndFolder) Then

objFSO.CreateFolder(objEndFolder)

End If

Set objFolder = objFSO.GetFolder(objStartFolder)

Set colFiles = objFolder.Files

For Each objFile in colFiles

Dim src_file_str,dest_file_str,file_extn

src_file_str = objFile.Name

file_name = Mid(src_file_str,1,LEN(src_file_str)-4)

file_extn = Mid(src_file_str,LEN(src_file_str)-2,LEN(src_file_str))

If file_extn = "xls" then

src_file_str = objStartFolder & "\" & src_file_str

dest_file_str = objEndFolder & "\" & file_name & ".xlsx"

Open_HTML_Save_XLSX src_file_str,dest_file_str

End If

Next

Sub Open_HTML_Save_XLSX(srcPath,destPath)

Set objExcel = CreateObject("Excel.Application")

Set objWorkbook = objExcel.Workbooks.Open(srcPath)

objWorkbook.Application.DisplayAlerts = False

objExcel.ActiveWorkbook.saveAs(destPath),51 

objWorkbook.Close True

objExcel.Quit

If objFSO.FileExists(srcPath) Then

objFSO.DeleteFile srcPath

End If

OpenExcelFile destPath

End Sub

'Opens the Excel file in Excel

Sub OpenExcelFile(excelPath)

Set objExcel = CreateObject("Excel.Application")

Set objWorkbook = objExcel.Workbooks.Open(excelPath)

objExcel.Visible = false

Set objWorksheet = objWorkbook.Worksheets(1)

objWorksheet.rows(2).WrapText = False

objWorksheet.rows(2).Orientation = 90

objWorksheet.rows(3).WrapText = False

objWorksheet.rows(3).Orientation = 90

Set objRange = objWorksheet.UsedRange

objRange.EntireColumn.Autofit()

objWorksheet.rows(2).AutoFit()

objWorksheet.rows(3).AutoFit()

objWorkbook.Save

objWorkbook.Close

objExcel.Quit

End Sub

[James Moberg]

Are you able to use a more simple VBscript and have it create file, log data or return a simple value?  (In case the issue is with calling VBscript.)  And then move the sample VBscript to a different sub-directory (in case it's an issue with permissions.)

You may have to use the shell "CMD.exe" and then "stream the commands.  Here's an example from CFX_EXEC documentation.

<cfset stream="dir c:\#Chr(13)##Chr(10)#exit#Chr(13)##Chr(10)#">

<cfx_exec CMD="cmd.exe" NAME="out" INPUT=#stream#>

<cfoutput>#out#</cfoutput>