forcing refresh links

[Guest]

Hi All,

I have a link in WB A to WB B. When I change the source cell in WB B (it
contains a Boolean value) from FALSE to TRUE, link is refreshed in WB A. But
when I close WB B without saving (keeping the unchanged source value FALSE),
link in WB A is refreshed only when I reopen WB A. My question is that can I
somehow force refreshing link immediately when I close WB B without saving?

Thanks,
Stefi

 

[Dave Peterson]

You could always use:
Edit|links|Update values

But I bet you're looking for something more automatic.

Maybe you could have some application event that looks for a workbook that's
closing and then cycles through all the other workbooks and does that same
function.

I don't see anything that's built into excel that would do this automatically.

 

[Guest]

„Dave Peterson†ezt írta:

You could always use:
Edit|links|Update values

But I bet you're looking for something more automatic.

Maybe you could have some application event that looks for a workbook that's
closing and then cycles through all the other workbooks and does that same
function.

I don't see anything that's built into excel that would do this automatically.

 

[Guest]

Hi Dave,

You won! Creating an event that keeps looking for closing workbooks seems a
bit complicated. But anyway, many thanks, because you put me in the right
direction:

Private Sub Workbook_Activate()
ActiveWorkbook.UpdateLink Name:= _
ActiveWorkbook.LinkSources, _
Type:=xlExcelLinks
End Sub

of WB A does the trick, because WB B must be the active workbook when I
close it, and having it closed WB A becomes the active workbook, either
automatically or clicking on it. It's satisfactory to have WB A refreshed
when it becomes active next time.

Regards,
Stefi


„Stefi†ezt írta:

 

[Guest]

My solution is not complete! It works only if linked workbooks are ALL
closed! Any idea how to handle the case when some of them are just open?

Stefi


„Stefi†ezt írta:

 

[Guest]

The final solution:

Private Sub Workbook_Activate()
aLinks = ActiveWorkbook.LinkSources(xlExcelLinks)
If Not IsEmpty(aLinks) Then
For i = 1 To UBound(aLinks)
' MsgBox "Link " & i & ":" & Chr(13) & aLinks(i)
backslpoz = InStrRev(aLinks(i), "\")
linkwbnev = Mid(aLinks(i), backslpoz + 1)
If Not IsOpenWB(linkwbnev) Then
ThisWorkbook.UpdateLink Name:=linkwbnev, Type:=xlExcelLinks
End If
Next i
End If
End Sub


Public Function IsOpenWB(ByVal munkafuzet As String) As Boolean
'returns true if workbook is open
Dim objWorkbook As Object
On Error Resume Next
IsOpenWB = False
Set objWorkbook = Workbooks(munkafuzet)
If Err = 0 Then IsOpenWB = True
End Function

Stefi


„Stefi†ezt írta:

 

[Dave Peterson]

How about something like this:

Option Explicit
Private Sub Workbook_Activate()
Dim myLinks As Variant
Dim iCtr As Long

myLinks = Me.LinkSources(xlExcelLinks)
If IsEmpty(myLinks) Then
'do nothing
Else
For iCtr = 1 To UBound(myLinks)
If IsWorkbookOpen(CStr(myLinks(iCtr))) = True Then
'skip it
Else
Me.UpdateLink Name:=myLinks(iCtr)
End If
Next iCtr
End If

End Sub
Function IsWorkbookOpen(myFileName As String) As Boolean

Dim JustFileName As String
Dim BackSlashPos As Long
Dim TestWkbk As Workbook

BackSlashPos = InStrRev(myFileName, "\")
JustFileName = Mid(myFileName, BackSlashPos + 1)

On Error Resume Next
IsWorkbookOpen = CBool(Len(Workbooks(JustFileName).Name) > 0)
On Error GoTo 0

End Function

 

[Guest]

Thanks Dave for dealing with my problem that much! At first look this is
almost the same as I found myself in my previous post!

Regards,
Stefi


„Dave Peterson†ezt írta:

 

[Dave Peterson]

If I had waited a few minutes, your post would have shown up and I wouldn't have
essentially duplicated your solution.

Thanks Dave for dealing with my problem that much! At first look this is
almost the same as I found myself in my previous post!

Regards,
Stefi

„Dave Peterson†ezt írta:

 

[Guest]

Sorry for wasting your time but I couldn't be quicker. I wanted to thoroughly
test my solution before posting it. Thanks anyway!

Stefi


„Dave Peterson†ezt írta:

 

[Dave Peterson]

Not a problem.

Sorry for wasting your time but I couldn't be quicker. I wanted to thoroughly
test my solution before posting it. Thanks anyway!

Stefi

„Dave Peterson†ezt írta:

 

[Dave Peterson]

In fact, if you look at a lot of posts, you'll see many of the answers
duplicated.

I'm just glad we matched!