How to refresh cells using VBA?

Question

I am using all the solutions that appear in:

How to refresh ALL cell through VBA

Getting Excel to refresh data on sheet from within VBA

ActiveSheet.EnableCalculation = False  
ActiveSheet.EnableCalculation = True

or

Application.Calculate

or

Application.CalculateFull

None of them works in Excel 2010. When I go to the cell and right click refresh it works. How can I refresh within VBA?

Sheets("Name_of_sheet").Range("D424").Refresh raises an

exception 438

Questions:

1. How can I make the script support Excel 2003, 2007, 2010?
2. How can I choose the source file to refresh from using VBA?

EDIT:

1. I want to simulate a right mouse click and choose refresh in the menu in worksheet 3. That is the entire story.

2. I work on an Excel file created 10 years ago. When opening in Excel 2010, I can go to a cell and right click on it and choose refresh and then choose the .txt file to refresh from. I am trying to do it automatically within VBA.

Answer 1

I finally used mouse events and keystrokes to do it:

Sheets("worksheet34").Select
Range("D15").Select
Application.WindowState = xlMaximized
SetCursorPos 200, 600 'set mouse position at 200, 600
Call mouse_event(MOUSEEVENTF_RIGHTDOWN, 0, 0, 0, 0) 'click left mouse
Application.SendKeys ("R")

Answer 2

For an individual cell you can use:

Range("D13").Calculate

OR

Cells(13, "D").Calculate

Answer 3

just a reminder;

be careful when using

Application.Calculation = xlCalculationManual
Application.Calculation = xlCalculationAutomatic

this sets the entire excel application to calculate formula's either automatically or manually. If you use

Application.Calculation = xlCalculationManual

you'll notice your automatic formulas no longer work.

cheers

Answer 4

You could try using Application.Calculation

Application.Calculation = xlCalculationManual
Application.Calculation = xlCalculationAutomatic