Problem calculating workbook via VB Script

[EDN Admin]

Hi All,
Ive just created a VBscript in order to launch a specific Excel file and then a specific Macro (writeToFile). This macro allows me to create a PDF file with the data displayed within the spreadsheet.
I have to update the workbook before launching the writeToFile macro in order to have the latest data written in the PDF file.
Therefore, this my VB Script :
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~<br/>
Dim objXLApp<br/>
Set objXLApp = CreateObject("Excel.Application")
DEFINE PATH<br/>
fileName = "file.xlsm"<br/>
currentDirectory = left(WScript.ScriptFullName,(Len(WScript.ScriptFullName))-(len(WScript.ScriptName)))
OPEN WORKBOOK<br/>
objXLApp.Workbooks.Open currentDirectory & fileName<br/>
objXLApp.visible = true<br/>
objXLApp.DisplayAlerts = False
CALCULATE ALL SHEETS IN WORKBOOK<br/>
objXLApp.Run "file.xlsm!update"<br/>
<br/>
LAUNCH MACRO TO WRITE IN PDF FILE<br/>
objXLApp.Run "file.xlsm!writeToWord"
CLOSE WORKBOOK<br/>
objXLApp.Workbooks(1).Save<br/>
objXLApp.Workbooks(1).Close<br/>
objXLApp.Quit<br/>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
This is how I update my workbook:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~<br/>
Public Sub update()<br/>
<br/>
Dim sht As Worksheet<br/>
Application.Calculation = xlCalculationManual
For Each sht In ThisWorkbook.Worksheets<br/>
sht.Calculate<br/>
Next sht
End Sub<br/>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
However, when I run the script, after running the update macro, the cells that contain a formula in my workbook write #NAMES? instead of updated values.
What is strange is that when I run the update macro myself via the VBA editor, the update is done correctly.
Could anyone help me on this?<br/>
<br/>
Francisco

View the full article