CODEWITHSUNDEEP
vbaexcelms-office
Toscino
Toscino

Reputation: 1

Need to Update External References in an Excel Sheet while a Macro is Running

I have an excel macro that sets Cells to an external location.

Range(NamedReference) = "='http://webaddress/ExcelSheet.xlsx'!NamedReference

Other cells use that location to calculate new values.

"A1" = NamedReference + 1

The problem is that I need to read the new calculated values back into the macro to export data, but the external link has not yet been calculated to any value. It is a #NAME? until the macro is done running. Is there any way to force excel to get those values during the macro run time?

I have tried a variety of things including

Calculate
CalculateFull

Any help would be appreciated. My current solution is to just close the macro on error and have the user re run the macro, but it is really kludgey.

**Edit: Forgot equals sign in formula

Upvotes: 0

Views: 2606

Answers (1)

chris neilsen
chris neilsen

Reputation: 53126

You could try

ActiveWorkbook.UpdateLink Name:=ActiveWorkbook.LinkSources

See on MSDN

Upvotes: 1

Related Questions