Cell value when reference is used

[busy_bee]

One of the cells(Row:5,Column:6) in my Excel Sheet1 has a reference value in Sheet2. ='Sheet2'!$C14

My VBScript code is as follows.

Set ws1 = wb1.Worksheets("Sheet1")
string1 = ws1.Cells(5,6).Value
WScript.Echo string1

string1 returns a value of 0 instead of the final value based on the reference formula. I am attempting to assign the final value retrieved from Sheet2!C14 to string1.

I would appreciate help in this task.

 

[xwb]

1) Is the excel sheet still open?
2) Are you closing the workbook and quitting excel at the end of the script
3) Check taskmgr - are there any copies of excel still running. If so kill them first

Now check the excel sheet - does it have what you expect?
Close the sheet
run the script - what do you get?

 

[busy_bee]

My VBScript works correctly on my local machine but not on a remote server. I closed all instances of Excel by accessing the Task Manager on the remote server but my code still does not work as expected.

Do you have any suggestions for settings on the remote server that is causing this issue?

 

[combo]

Check wb1.FullName to see if you work with the workbook you expect.

Is wb1 visible? If not, make it visible (wb1.Parent.Visible=True) and check if it is not a security issue.

combo

 

[busy_bee]

I found a solution by converting formulas to absolute values before assigning the value to string1. My new code is as follows.

Set ws1 = wb1.Worksheets("Sheet1")
Set rng1 = ws1.Range("F5").Value
If rng1.HasFormula Then
	rng1.Formula = Application.ConvertFormula(rng1.Formula,1,1,1)
End If
string1 = rng1.Value
WScript.Echo string1

I hope my code works effectively in future situations if an Excel file has R1C1 style formulas instead of A1 style formulas.