We help IT Professionals succeed at work.

# ColdFusion SpreadsheetSetCellValue for Excel's formula RATE not working in all cases

on
Medium Priority
165 Views
Excel's formula RATE is used to get the interest rate per period of an annuity

=RATE (nper, pmt, pv, [fv], [type], [guess])
nper - The total number of payment periods.
pmt - The payment made each period.fv - [optional] The future value, or desired cash balance after last payment. Default is 0.
type - [optional] When payments are due. 0 = end of period. 1 = beginning of period. Default is 0.
guess - [optional] Your guess on the rate. Default is 10%.
pv - The present value, or total value of all loan payments now.

I am trying to mimic the RATE formula in ColdFusion and have been successful until a certain point (see example below).

For nPER > 43 my Coldfusion function returns the wrong result.

Excel result : =RATE(44,223.51,-5000) =  0.03476
Coldfusion result : 0.0135061661

Any help is greatly appreciated

``````<cfloop index="nIdx" from="1" to="99">

<cfinvoke   method="getAPR2" returnvariable="nAPR">
<cfinvokeargument name="parmLoan"  value="5000">
<cfinvokeargument name="parmDuration"  value="#nIdx#">
<cfinvokeargument name="parmMonthlyPayment"  value="223.51">
</cfinvoke>

<cfdump var = "#nIdx#"> .... <cfdump var = "#nApr#"><br>

</cfloop>

<!---------------------------------------------------------------
getAPR2 - 2019-09-01 using Central Bank's document
----------------------------------------------------------------->
<cffunction name="getAPR2" returntype ="numeric">
<cfargument name="parmLoan" required="true" type="numeric">
<cfargument name="parmDuration" required="true" type="numeric">	<!--- In months --->
<cfargument name="parmMonthlyPayment" required="true" type="numeric">

<cfscript>
</cfscript>

<cfset var MYVAR = structNew()>

<cfreturn MYVAR.nAPR>
</cffunction>
``````
Comment
Watch Question

## View Solution Only

CERTIFIED EXPERT
Most Valuable Expert 2015

Commented:
I don't have an answer, other than to say you may be running into some RATE() function bugs in POI.  As it doesn't look like it's been fixed, you'd need to roll your own or find an alternative.  Unfortunately I don't know RATE() well enough to suggest one.

Commented:
Since the bug has not been fixed, I have been trying the below as an alternative
1.      Generate Excel file
2.      Use VB script to convert Excel into CSV

Unfortunately I have not been successful yet as CFEXECUTE to run the VB script does not work.

Generate the Excel file
``````<cfscript>
</cfscript>

``````

B9.bat â€“ runs as expected from command line
``````cvtExcelToCSvPROMPT C:\ColdFusion11\cfusion\wwwroot\Test\newSpreadsheet.xls C:\ColdFusion11\cfusion\wwwroot\Test\newSpreadsheet.csv
``````

cvtExcelToCSvPROMPT.vbs
``````if WScript.Arguments.Count < 2 Then
WScript.Echo "Please specify the source and the destination files. Usage: ExcelToCsv <xls/xlsx source file> <csv destination file>"
Wscript.Quit
End If

csv_format = 6

Set objFSO = CreateObject("Scripting.FileSystemObject")

src_file = objFSO.GetAbsolutePathName(Wscript.Arguments.Item(0))
dest_file = objFSO.GetAbsolutePathName(WScript.Arguments.Item(1))

Dim oExcel
Set oExcel = CreateObject("Excel.Application")

Dim oBook
Set oBook = oExcel.Workbooks.Open(src_file)

oBook.SaveAs dest_file, csv_format

oBook.Close False
oExcel.Quit
``````

Convert XLS to CSV .CFM
``````<cfexecute variable="gm"
errorVariable="errorOut"
name="c:\windows\system32\cmd.exe"
timeout="30"
arguments="/c  C:\ColdFusion11\cfusion\wwwroot\Test\b9.bat" />
``````
CERTIFIED EXPERT
Most Valuable Expert 2015

Commented:
With a small change to the VBS script, this worked under CF2016

``````...
Dim oExcel
Set oExcel = CreateObject("Excel.Application")
...
``````

Run with cscript.exe

//NoLogo - Specifies that the Windows Script Host banner is not displayed before the script runs.
/b - Specifies batch mode, which does not display alerts, scripting errors, or input prompts.

``````<cfexecute name = "C:\Windows\System32\CScript.exe"
arguments = "//NoLogo /b c:\path\cvtExcelToCSvPROMPT.vbs C:\path\test.xls C:\path\spreadsheet.csv"
variable = "savedOutput"
timeout = "3600"
errorVariable="err">
</cfexecute>
``````

Commented:
Tried the solution on Coldfusion11 and Coldfusion2018 without luck.

Can you think of some other alternatives on how I can get the desired result using Excel formula ?

I ran out of options
CERTIFIED EXPERT
Most Valuable Expert 2015
Commented:
Edit: Although server side automation isn't generally recommended, the only things I can think of require Excel, which isn't currently working for you.

I re-checked and it worked under CF2018 too. So there must be something different in our environments.  cfexecute woes are usually due to permissions problems.  My environment

- Excel 32 bit installed on server machine
- CF service is running under Local System account
- Allow service to interact with Desktop is UN-checked.
- Win 7 64 bit

I found a few articles which mention issues with Excel:

Even though the script or program is executing under a user account, when Excel is launched through COM automation it is executed under the Local System user identity. This identity does not have a user profile, and this causes Excel to fail because it is designed to assume that it is running in an interactive user session.

The workaround it to create the missing "Desktop" folder

If the script is written using VBScript, JScript, or another Windows Scripting Host language, create the folder "C:\Windows\System32\config\systemprofile\Desktop". Do this regardless of whether the operating system is 32-bit or 64-bit, because these scripts always run as 32-bit scripts.

The Desktop folder does exist on the test machine (64 bit).  When I deleted that folder, the code stopped working.  So that may be why it works for me. Check if the folder exists in your environment, and if not create it.

On 32-bit platforms, create the folder "C:\Windows\System32\config\systemprofile\Desktop".
On 64-bit platforms, create the folder "C:\Windows\SysWOW64\config\systemprofile\Desktop".

Commented:
Hello _agx_

I trid all you suggested to no avail. CFEXECUTE indeed has it woes. Thank you for the time invested in assisting me
CERTIFIED EXPERT
Most Valuable Expert 2015

Commented:
Sorry, you weren't able to get it working.  Figuring out the correct permissions is sometimes an extended process ....

When you successfully ran it from the command line, you were probably logged in under your own windows account, which has greater permissions.  The CF service typically runs under the "Local System".  As a test,  temporarily change the "Log on" account used by the CF service

Services > (ColdFusion Service Name) > Log On Tab > (Enter the windows credentials used for the CLI test)
Check "Allow service to interact with desktop"

Restart the CF Service, and run the test code again. If it succeeds, that'll at least confirm it's a permissions problem.

When finished, don't forget to reset the CF Service back to the original Log On account, and uncheck "Allow service to interact with desktop"

Commented:
I changed the services to windows credential. Now I am getting the below message. As you said, it is a permission issue

The following information is meant for the website developer for debugging purposes.
Error Occurred While Processing Request
Timeout period expired without completion of C:/Windows/System32/CScript.exe
CERTIFIED EXPERT
Most Valuable Expert 2015

Commented:
Check the Task Manager. Is there an instance of cscript.exe running?

Commented:
N0, there is no instance of csscript.exe running
CERTIFIED EXPERT
Most Valuable Expert 2015

Commented:
Which o/s? Also, is CF running in a sandbox or any anti-virus software running that might interfere?

Try a simple helloworld example with cscript. Does it still timeout?

helloWorld.vbs
``````WScript.Echo "Hello world!"
``````
CF
``````<cfexecute name = "C:\Windows\System32\CScript.exe"
arguments = "//NoLogo C:\ColdFusion11\cfusion\wwwroot\vbScriptTest\helloWorld.vbs"
variable = "savedOutput"
timeout = "30"
errorVariable="err">
</cfexecute>
<cfoutput>
<!--- not sure if CF11 supports isNull, so using alternative --->
<cfif structKeyExists(variables, "savedOutput")>
savedOutput = #savedOutput#
</cfif>
<cfif structKeyExists(variables, "err")>
err = #err#
</cfif>
</cfoutput>
``````

Commented:
This is the output I got ---> savedOutput = Hello world! err =

O/S = WIndows 10
Only anti-virus is windows defender
CERTIFIED EXPERT
Most Valuable Expert 2015

Commented:
Two other things you could try for grins.  FWIW, I didn't need to do either, but .. I suspect we're not using the same o/s. Since the test is quick, it's worth trying.

1) A few threads mentioned they created the "Desktop" folder in both the 32bit/64 bit locations. Try that and re-run the test code.

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

2) Again, temporarily using the windows credentials that ran cscript.exe, enable the "Allow service to interact with desktop" setting. Restart CF and re-run the test code.
CERTIFIED EXPERT
Most Valuable Expert 2015

Commented:

>> This is the output I got ---> savedOutput = Hello world! err =

Okay, then the problem isn't cscript.exe. What happens if you change the HelloWorld.vbs file to this?

``````WScript.Echo "Preparing to open Excel..."

Dim oExcel
Set oExcel = CreateObject("Excel.Application")
oExcel.Quit

WScript.Echo "Finished. Closed Excel"
``````

Commented:
Got the same result

savedOutput = Hello world! err =

Commented:
This is the new result ---> time out !!!! See below

The following information is meant for the website developer for debugging purposes.
Error Occurred While Processing Request
Timeout period expired without completion of C:/Windows/System32/CScript.exe
CERTIFIED EXPERT
Most Valuable Expert 2015

Commented:
Ahh, good. So we know the problem is opening Excel.  What version of Excel and bitness?

Also, is there another CF machine you can test this from, to rule out Win10 as the culprit?

Edit: Are there any errors in the CF logs or Windows Event logs?

Commented:
Message in EXCEPTION.log
=================================
"Error","http-bio-8500-exec-5","09/06/19","14:23:43",,"Timeout period expired without completion of C:\Windows\System32\CScript.exe The specific sequence of files included or processed is: C:\ColdFusion11\cfusion\wwwroot\vbScriptTest\helloWorld.cfm, line: 5 "
coldfusion.tagext.lang.ExecuteTag\$TimeoutException: Timeout period expired without completion of C:\Windows\System32\CScript.exe
at coldfusion.tagext.lang.ExecuteTag.doStartTag(ExecuteTag.java:177)
at cfhelloWorld2ecfm1080557222.runPage(C:\ColdFusion11\cfusion\wwwroot\vbScriptTest\helloWorld.cfm:5)

Message in coldfusion-out.log
=============================
Sep 6, 2019 14:23:43 PM Error [http-bio-8500-exec-5] - Timeout period expired without completion of C:\Windows\System32\CScript.exe The specific sequence of files included or processed is: C:\ColdFusion11\cfusion\wwwroot\vbScriptTest\helloWorld.cfm, line: 5

MEssage in application.log
==========================
"Error","http-bio-8500-exec-5","09/06/19","14:23:43",,"Timeout period expired without completion of C:\Windows\System32\CScript.exe The specific sequence of files included or processed is: C:\ColdFusion11\cfusion\wwwroot\vbScriptTest\helloWorld.cfm, line: 5 "

Result on another server: CF2018 environment, Windows server 2016, no Office365 but LibreOffice365
==================================================================================================
savedOutput = Preparing to open Excel... Finished. Closed Excel err =

Note
====
On my PC, running Windows10, I am using Office365
CERTIFIED EXPERT
Most Valuable Expert 2015

Commented:
> CF2018 environment, Windows server 2016 no Office365 but LibreOffice365

Interesting, I'm surprised it works with Libre.  Was the test using "Local System" or your windows account?

If you try it with the original script, does it work (or is that the machine you said failed with CF2018)?

Commented:
On the server, only the XLS file is being created.

Maybe I am overlooking someithing. Can you take a peek please

Directyory on server
=====================
C:\ColdFusion2018\cfusion\wwwroot\vbScriptTest

.cfm
======
<cfscript>
</cfscript>

<cfexecute name = "C:\Windows\System32\CScript.exe"
variable = "savedOutput"
timeout = "10"
errorVariable="err">
</cfexecute>

cvtExcelToCSvPROMPT.vbs
=======================
if WScript.Arguments.Count < 2 Then
WScript.Echo "Please specify the source and the destination files. Usage: ExcelToCsv <xls/xlsx source file> <csv destination file>"
Wscript.Quit
End If

csv_format = 6

Set objFSO = CreateObject("Scripting.FileSystemObject")

src_file = objFSO.GetAbsolutePathName(Wscript.Arguments.Item(0))
dest_file = objFSO.GetAbsolutePathName(WScript.Arguments.Item(1))

Dim oExcel
Set oExcel = CreateObject("Excel.Application")

Dim oBook
Set oBook = oExcel.Workbooks.Open(src_file)

oBook.SaveAs dest_file, csv_format

oBook.Close False
oExcel.Quit
CERTIFIED EXPERT
Most Valuable Expert 2015

Commented:

Directory structure:
C:\ColdFusion2018\cfusion\wwwroot\vbScriptTest

Code:
<cfexecute
arguments = "//NoLogo  C:\ColdFusion11\cfusion\wwwroot\Test\cvtExcelToCSvPROMPT.vbs C:\ColdFusion11\cfusion\wwwroot
...

Some of the paths are different.

To keep things simple, I skipped the spreadsheet functions and just used a static xls file.  Everything's kept within this single directory

C:\ColdFusion11\cfusion\wwwroot\vbScriptTest
- cvtExcelToCSvPROMPT.vbs
- test.xls
- test.cfm

Code:
<cfexecute name = "C:\Windows\System32\CScript.exe"
arguments = "/C //NoLogo /b C:\ColdFusion11\cfusion\wwwroot\vbScriptTest\cvtExcelToCSvPROMPT.vbs C:\ColdFusion11\cfusion\wwwroot\vbScriptTest\test.xls C:\ColdFusion11\cfusion\wwwroot\vbScriptTest\spreadsheet.csv"
timeout = "30"
variable = "savedOutput"
errorVariable="err">
</cfexecute>
<cfoutput>
<cfif structKeyExists(variables, "savedOutput")>
savedOutput = #savedOutput#
</cfif>
<cfif structKeyExists(variables, "err")>
err = #err#
</cfif>
</cfoutput>

Commented:
Guess I have been looking too long at the code - thanks for the correction

Result
======
CSV is not created
savedOutput = err =

.cfm
====

<cfscript>
theFile=GetDirectoryFromPath(GetCurrentTemplatePath()) & "test.xls";
</cfscript>

<cfexecute name = "C:\Windows\System32\CScript.exe"
arguments = "//NoLogo /b C:\ColdFusion11\cfusion\wwwroot\vbScriptTest\cvtExcelToCSvPROMPT.vbs C:\ColdFusion11\cfusion\wwwroot\vbScriptTest\test.xls C:\ColdFusion11\cfusion\wwwroot\vbScriptTest\spreadsheet.csv"
timeout = "30"
variable = "savedOutput"
errorVariable="err">
</cfexecute>
<cfoutput>
<cfif structKeyExists(variables, "savedOutput")>
savedOutput = #savedOutput#
</cfif>
<cfif structKeyExists(variables, "err")>
err = #err#
</cfif>
</cfoutput>

cvtExcelToCSvPROMPT.vbs
====
if WScript.Arguments.Count < 2 Then
WScript.Echo "Please specify the source and the destination files. Usage: ExcelToCsv <xls/xlsx source file> <csv destination file>"
Wscript.Quit
End If

csv_format = 6

Set objFSO = CreateObject("Scripting.FileSystemObject")

src_file = objFSO.GetAbsolutePathName(Wscript.Arguments.Item(0))
dest_file = objFSO.GetAbsolutePathName(WScript.Arguments.Item(1))

Dim oExcel
Set oExcel = CreateObject("Excel.Application")

Dim oBook
Set oBook = oExcel.Workbooks.Open(src_file)

oBook.SaveAs dest_file, csv_format

oBook.Close False
oExcel.Quit
CERTIFIED EXPERT
Most Valuable Expert 2015

Commented:
>>  C:\ColdFusion11\cfusion\

Oh, I thought this was under CF2018?

Just to be sure we're not getting tripped up by bad paths or something extraneous,  create a folder with only these 3 files:

1. test.cfm
2. test.xls  (use the file you already created)
3. cvtExcelToCSvPROMPT.vbs

test.cfm - uses dynamic paths (and unique CSV file name)

``````<cfset dir = getDirectoryFromPath(getCurrentTemplatePath())>
<cfset args = "//NoLogo #dir#cvtExcelToCSvPROMPT.vbs #dir#test.xls #dir##createUUID()#.csv">

<cfexecute name = "C:\Windows\System32\CScript.exe"
arguments = "#args#"
variable = "savedOutput"
timeout = "30"
errorVariable="err">
</cfexecute>

<cfoutput>

<h4>Directory</h4> #dir#
<h4>args</h4> #args#
<h4>variable</h4> <pre>#savedOutput#</pre>
<h4>errorVariable</h4> #err#
</cfoutput>
``````

``````csv_format = 6

Set objFSO = CreateObject("Scripting.FileSystemObject")

src_file = objFSO.GetAbsolutePathName(Wscript.Arguments.Item(0))
dest_file = objFSO.GetAbsolutePathName(WScript.Arguments.Item(1))

Dim oExcel
Set oExcel = CreateObject("Excel.Application")
WScript.Echo "Err.Number "& Err.Number & Chr(10)
WScript.Echo "Err.Description "& Err.Description & Chr(10)

Dim oBook
Set oBook = oExcel.Workbooks.Open(src_file)

oBook.SaveAs dest_file, csv_format

oBook.Close False
oExcel.Quit
``````

Commented:
Dear _agx_, sorry for not having answered earlier. Your solution did work. Thank you very much for saving the day. Appreciate it !!!!
CERTIFIED EXPERT
Most Valuable Expert 2015

Commented:
Great! Glad we finally got it working!