cfexecute command fails to execute the vbscript.

New Here ,
Jul 19, 2017 Jul 19, 2017

Copy link to clipboard

Copied

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#">

Views

916

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guide ,
Jul 19, 2017 Jul 19, 2017

Copy link to clipboard

Copied

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?

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Jul 19, 2017 Jul 19, 2017

Copy link to clipboard

Copied

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.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Engaged ,
Jul 19, 2017 Jul 19, 2017

Copy link to clipboard

Copied

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.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Jul 19, 2017 Jul 19, 2017

Copy link to clipboard

Copied

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.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Jul 20, 2017 Jul 20, 2017

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Engaged ,
Jul 20, 2017 Jul 20, 2017

Copy link to clipboard

Copied

LATEST

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>

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines