cnsleepy
07-11-2008, 10:03 AM
Hi
For reasons I shant bore you with, I am trying to use VBA to force a workbook link to update, without trying to recalculate my model.
I know what I am doing in Excel, but in VBA I have very little experience.
I know when I go into a cell in Excel that contains a workbook link, press F2 and then hit enter, excel updates the values of that cell. So, having recorded a macro whilst doing this, the resulting code included the line...... Range("A1").Activate.
I then tried to replicate this action across all formulas on the particular worksheet and having done a bit of research, came up with the following:
Dim theCell As Range
For Each theCell In ActiveSheet.UsedRange.Cells
If theCell.HasFormula Then
theCell.Activate
End If
Next
End Sub
But no joy. The values don't update, despite replicating what I thought Excel had done when I hit F2 and then Enter.
A little more research has led me to the fact that .Select and .Activate are the same thing when it comes to a Range Object. Does this mean that the above code is just cycling through each Formula cell on the worksheet and selecting it, rather than updating the link?
Can anybody think of any way to achieve what I'm trying to do? :banghead:
Much appreciate any help.
Thanks.
For reasons I shant bore you with, I am trying to use VBA to force a workbook link to update, without trying to recalculate my model.
I know what I am doing in Excel, but in VBA I have very little experience.
I know when I go into a cell in Excel that contains a workbook link, press F2 and then hit enter, excel updates the values of that cell. So, having recorded a macro whilst doing this, the resulting code included the line...... Range("A1").Activate.
I then tried to replicate this action across all formulas on the particular worksheet and having done a bit of research, came up with the following:
Dim theCell As Range
For Each theCell In ActiveSheet.UsedRange.Cells
If theCell.HasFormula Then
theCell.Activate
End If
Next
End Sub
But no joy. The values don't update, despite replicating what I thought Excel had done when I hit F2 and then Enter.
A little more research has led me to the fact that .Select and .Activate are the same thing when it comes to a Range Object. Does this mean that the above code is just cycling through each Formula cell on the worksheet and selecting it, rather than updating the link?
Can anybody think of any way to achieve what I'm trying to do? :banghead:
Much appreciate any help.
Thanks.