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#">
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?
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.
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.
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.
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
Copy link to clipboard
Copied
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>