Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Cell value when reference is used

Cell value when reference is used

Cell value when reference is used

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.

CODE --> VBScript

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.

RE: Cell value when reference is used

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?

RE: Cell value when reference is used

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?

RE: Cell value when reference is used

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.


RE: Cell value when reference is used

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

CODE --> VBScript

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close