Search code examples
excelvba

How to refresh cells using VBA?

I've attempted various solutions from Stack Overflow (How to refresh ALL cell through VBA, Getting Excel to refresh data on sheet from within VBA) to refresh cells in Excel VBA without success in Excel 2010.

These methods include toggling the calculation state of the active sheet (ActiveSheet.EnableCalculation = False followed by ActiveSheet.EnableCalculation = True), and using Application.Calculate and Application.CalculateFull.

Unfortunately, none have worked. Interestingly, manually refreshing a cell via right-click works fine. However, trying to programmatically refresh a cell with Sheets("Name_of_sheet").Range("D424").Refresh triggers exception 438.

Questions:

  1. How can I adapt the VBA script to work across Excel 2003, 2007, and 2010?
  2. How can I programmatically select a source file for refreshing data?

Edit:

  • My main goal is to replicate the manual process of right-clicking a cell in "worksheet 3" to refresh it, specifically automating the selection of a .txt file as the source.
  • The Excel file I'm working with was created a decade ago. In Excel 2010, manual cell refresh works by right-clicking and choosing a .txt file. I'm looking to automate this in VBA.
Solution

  • You could try using Application.Calculation

    Application.Calculation = xlCalculationManual
Application.Calculation = xlCalculationAutomatic