Force the cells to finish calculation before appending their values (Application.CalculationState = xlDone doesn't work)

Question

I have a VB script which will open an excel and run a macro within the excel.

This Macro_A within the excel will:

1. Application.Calculate (I have also tried adding something here where it’s a loop to wait until application.Calculate is finished)
2. If Application.CalculationState = xlDone -> Take the values of cells (the cells contain formula which will get values via AMQ publisher / JMS)
3. Write it to the txt file

Something strange is happening when Macro_A is called

What it should be doing is:

Get the values and append them to the txt file

However, what happened is that:

• Even if I have the if statement: If Application.CalculationState = xlDone Then (take the values and append them to txt) and the wait until application calculation is done snippet of code:

Do While Not Application.CalculationState = xlDone
    If Application.CalculationState = xlPending Then Application.Calculate
    Application.Wait (Now + TimeValue("00:00:02"))
Loop

• When the function is called, there’s a type mismatch error because the cells are still ‘#NA”. However, when I end the VBscript and run the code in excel directly, it is working fine because calculation is done (there are number values so there's no type mismatch error anymore)

Is there a way for me to get rid of the #NA and extract the values of the calculated numbers

Any help will be greatly appreciated, thanks in advance